Running several SELECT commands in one query

[Vodnik]

I was using the code:

Code: Pascal  [Select][+][-]

1.     DBForm.DBquery2.Close;
2.     DBForm.DBquery2.SQL.text:='select min(callstart + sitetzoffset units minute), max(callstart + sitetzoffset units minute), count(*), count(distinct callid) from callrecord;';
3.     DBForm.DBquery2.Open;
4.     CallRecordStartDateTime:=DBForm.DBquery2.Fields[0].AsDateTime;
5.     CallRecordEndDateTime:=DBForm.DBquery2.Fields[1].AsDateTime;
6.     Total:=DBForm.DBquery2.Fields[2].AsLongInt;
7.     Unique:=DBForm.DBquery2.Fields[3].AsLongInt;
8.     DBForm.DBquery2.Close;
9.  

to obtain several values from a table. But soon I have discovered, that combined SELECT

Code: Pascal  [Select][+][-]

1. 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][+][-]

1.       DBForm.DBquery2.Close;
2.      DBForm.DBquery2.Clear;
3.     DBForm.DBquery2.SQL.Add('select min(callstart + sitetzoffset units minute) from callrecord;');
4.     DBForm.DBquery2.SQL.Add('select max(callstart + sitetzoffset units minute) from callrecord;');
5.     DBForm.DBquery2.SQL.Add('select count(*) from callrecord;');
6.     DBForm.DBquery2.SQL.Add('select count(distinct callid) from callrecord;');
7.     DBForm.DBquery2.Open;
8.     CallRecordStartDateTime:=DBForm.DBquery2.Fields[0].AsDateTime;
9.     CallRecordEndDateTime:=DBForm.DBquery2.Fields[1].AsDateTime;
10.     Total:=DBForm.DBquery2.Fields[2].AsLongInt;
11.     Unique:=DBForm.DBquery2.Fields[3].AsLongInt;
12.     DBForm.DBquery2.Close;
13.  

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]

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][+][-]

1. SELECT
2.   MIN(callstart + sitetzoffset units MINUTE),
3.   MAX(callstart + sitetzoffset units MINUTE),
4.   COUNT(*),
5.   COUNT(DISTINCT callid)
6. FROM callrecord

[Vodnik]

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][+][-]

1. SELECT
2.   MIN(callstart + sitetzoffset units MINUTE),
3.   MAX(callstart + sitetzoffset units MINUTE),
4.   COUNT(*),
5.   COUNT(DISTINCT callid)
6. FROM callrecord

 
RVK, you suggested just my first example

[rvk]

RVK, 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][+][-]

1. SELECT MIN(callstart + sitetzoffset units MINUTE)
2. FROM callrecord
3. UNION ALL
4. SELECT MAX(callstart + sitetzoffset units MINUTE)
5. FROM callrecord
6. UNION ALL
7. SELECT COUNT(*)
8. FROM callrecord
9. UNION ALL
10. SELECT COUNT(DISTINCT callid)
11. FROM callrecord

Or (1 record, 4 fields):

Code: SQL  [Select][+][-]

1. SELECT MIN(callstart + sitetzoffset units MINUTE),NULL,NULL,NULL
2. FROM callrecord
3. UNION ALL
4. SELECT NULL,MAX(callstart + sitetzoffset units MINUTE),NULL,NULL
5. FROM callrecord
6. UNION ALL
7. SELECT NULL,NULL,COUNT(*),NULL
8. FROM callrecord
9. UNION ALL
10. SELECT NULL,NULL,NULL,COUNT(DISTINCT callid)
11. FROM callrecord

But I would find out, by checking the indexes and plan, why your other query is slow.

[Vodnik]

Here is another example of single SQL query (from SQL forum):

Code: SQL  [Select][+][-]

1. SELECT  
2.    (SELECT COUNT(DISTINCT callid) FROM callrecord) distinct_callid_cnt,
3.    (SELECT COUNT(*) FROM callrecord) count_all,
4.    (SELECT MIN(callstart) FROM callrecord) min_callstart,
5.    (SELECT MAX(callstart) FROM callrecord) max_callstart
6. FROM TABLE(SET{1})
7.  

It runs fast, as separate SELECTs do.
Thank you for idea about plan, I didn't know about it.