Connecting Two simple queries to one

[vpekulas]

How can I connect the following two queries into one ?

SQL = "SELECT COUNT(*) AS UNACT_SUPP FROM TABLE_SUPPLIER WHERE ACTIVE = 0"

SQL = "SELECT COUNT(*) AS ACT_SUPP FROM TABLE_SUPPLIER WHERE ACTIVE = 1"

 

[Guest_imported]

Hi,

just use the UNION keyword.

SQL = SELECT count(*) FROM file1
UNION
SELECTcount(*) FROM file2

This will give you two lines of output.

Hope this helps

Bernd

 

[OraCool]

Try this:
SQL =
"SELECT ACTIVE, COUNT(*) AS ACT_SUPP
FROM TABLE_SUPPLIER
WHERE ACTIVE < 2
GROUP BY ACTIVE
ORDER BY ACTIVE&quot;

 

[vpekulas]

Thank You
for the help, I probably gave a wrong example, so let's try again:

SQL = &quot;SELECT COUNT(*) AS UNACT_SUPP FROM TABLE_SUPPLIER WHERE C_TYPE = 'FAX'&quot;

SQL = &quot;SELECT COUNT(*) AS ACT_SUPP FROM TABLE_SUPPLIER WHERE ACTIVE = 1&quot;

As you can see I want to different values not in any way united togather.
What I need is to get UNACT_SUPP and ACT_SUPP values.
Any ideas ?

 

[rac2]

SELECT 
(SELECT COUNT(*) FROM TABLE_SUPPLIER WHERE C_TYPE = 'FAX'") AS UNACT_SUPP,
(SELECT COUNT(*) FROM TABLE_SUPPLIER WHERE ACTIVE = 1") AS ACT_SUPP

 

[vpekulas]

That gives me an error as well:

[TCX][MyODBC]You have an error in your SQL syntax near 'SELECT COUNT(*) FROM TABLE_SUPPLIER WHERE C_TYPE = 'FAX') AS UNACT_SUPP, (SELECT' at line 1
/test.asp, line 13

The code I use is:

SQL = SQL & &quot;SELECT &quot;
SQL = SQL & &quot;(SELECT COUNT(*) FROM TABLE_SUPPLIER WHERE C_TYPE = 'FAX') AS UNACT_SUPP,&quot;
SQL = SQL & &quot; (SELECT COUNT(*) FROM TABLE_SUPPLIER WHERE ACTIVE = 1) AS ACT_SUPP&quot;

 

[vpekulas]

Once agin:
If I execute the SQL_1 separetly it returns 35
and SQL_2 returns 1, but when I use UNION it returns nothing. It doesn't even find anything.

Example:

SQL_1 = &quot;SELECT COUNT(*) AS ACTIVE_LINKS FROM TABLE_LINK WHERE ACTIVE = 1&quot;
SQL_2 = &quot;SELECT COUNT(*) AS UN_LINKS FROM TABLE_LINK WHERE ACTIVE = 0&quot;
SQL = SQL_1 & &quot; UNION &quot; & SQL_2


What am I doing wrong ?

 

[vbjohn]

Try using.. UNION ALL that worked for my problem.


John-