Running several SELECT commands in one query
lazarus / 16-Databases

----------------------------------------------------------------------
Vodnik · 2018-05-04 11:40

I was using the code:
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i    
DBForm.DBquery2.Close;    DBForm.DBquery2.SQL.text:='select
min(callstart + sitetzoffset units minute), max(callstart +
sitetzoffset units minute), count(*), count(distinct callid) from
callrecord;';    DBForm.DBquery2.Open;   
CallRecordStartDateTime:=DBForm.DBquery2.Fields[0].AsDateTime;   
CallRecordEndDateTime:=DBForm.DBquery2.Fields[1].AsDateTime;   
Total:=DBForm.DBquery2.Fields[2].AsLongInt;   
Unique:=DBForm.DBquery2.Fields[3].AsLongInt;   
DBForm.DBquery2.Close; to obtain several values from a table. But soon
I have discovered, that combined SELECT
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i select
min(callstart + sitetzoffset units minute), max(callstart +
sitetzoffset units minute), count(all callid), count(distinct callid)
from callrecord;runs approx. 3 times longer than 4 separate ones. Do
not know why this is so (Informix via ODBC), but I changed the code:
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i      
DBForm.DBquery2.Close;     DBForm.DBquery2.Clear;   
DBForm.DBquery2.SQL.Add('select min(callstart + sitetzoffset units
minute) from callrecord;');    DBForm.DBquery2.SQL.Add('select
max(callstart + sitetzoffset units minute) from callrecord;');   
DBForm.DBquery2.SQL.Add('select count(*) from callrecord;');   
DBForm.DBquery2.SQL.Add('select count(distinct callid) from
callrecord;');    DBForm.DBquery2.Open;   
CallRecordStartDateTime:=DBForm.DBquery2.Fields[0].AsDateTime;   
CallRecordEndDateTime:=DBForm.DBquery2.Fields[1].AsDateTime;   
Total:=DBForm.DBquery2.Fields[2].AsLongInt;   
Unique:=DBForm.DBquery2.Fields[3].AsLongInt;   
DBForm.DBquery2.Close; but this produce SQL error:
Could not prepare statement. ODBC error details: LastReturnCode:
SQL_ERROR;Could not get error message: An invalid parameter was passed
to SQLGetDiagRec/Field
Then how is it possible to run several SELECT commands in one query?

----------------------------------------------------------------------
rvk · 2018-05-04 11:43

You can't do multiple selects with TSQLQuery.

But you can adjust your query to return all the fields.

This might work (depending on database):
Code: SQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i SELECT 
MIN(callstart + sitetzoffset units MINUTE),  MAX(callstart +
sitetzoffset units MINUTE),  COUNT(*),  COUNT(DISTINCT callid)FROM
callrecord

----------------------------------------------------------------------
Vodnik · 2018-05-04 11:51

Quote from: rvk on May 04, 2018, 01:43:47 pmYou can't do multiple
selects with TSQLQuery.

But you can adjust your query to return all the fields.

This might work (depending on database):
Code: SQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i SELECT 
MIN(callstart + sitetzoffset units MINUTE),  MAX(callstart +
sitetzoffset units MINUTE),  COUNT(*),  COUNT(DISTINCT callid)FROM
callrecord

RVK, you suggested just my first example

----------------------------------------------------------------------
rvk · 2018-05-04 12:06

Quote from: Vodnik on May 04, 2018, 01:51:56 pmRVK, you suggested just
my first example
Then you need to run the queries separately directly after each other.
It's not possible to do multiple selects in one TSQLQuery, period.

Or find out why your original query is slow.

Another alternative would be to use UNION to patch these queries
together.
You could do that with one record of with 4.

Either (4 record, 1 field):
Code: SQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i SELECT
MIN(callstart + sitetzoffset units MINUTE)FROM callrecordUNION
ALLSELECT MAX(callstart + sitetzoffset units MINUTE)FROM
callrecordUNION ALLSELECT COUNT(*)FROM callrecordUNION ALLSELECT
COUNT(DISTINCT callid)FROM callrecord
Or (1 record, 4 fields):
Code: SQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i SELECT
MIN(callstart + sitetzoffset units MINUTE),NULL,NULL,NULLFROM
callrecordUNION ALLSELECT NULL,MAX(callstart + sitetzoffset units
MINUTE),NULL,NULLFROM callrecordUNION ALLSELECT
NULL,NULL,COUNT(*),NULLFROM callrecordUNION ALLSELECT
NULL,NULL,NULL,COUNT(DISTINCT callid)FROM callrecord
But I would find out, by checking the indexes and plan, why your other
query is slow.

----------------------------------------------------------------------
Vodnik · 2018-05-04 20:20

Here is another example of single SQL query (from SQL forum):
Code: SQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i SELECT   
 (SELECT COUNT(DISTINCT callid) FROM callrecord) distinct_callid_cnt, 
 (SELECT COUNT(*) FROM callrecord) count_all,   (SELECT MIN(callstart)
FROM callrecord) min_callstart,   (SELECT MAX(callstart) FROM
callrecord) max_callstartFROM TABLE(SET{1}) It runs fast, as separate
SELECTs do.
Thank you for idea about plan, I didn't know about it.