Combine queries for single output

PlayHard

I want my output in two columns like this:

C_INCIDENT_TYPE \ TOTAL COUNT


How do I combine my two queries to get result.

First Query

SELECT C_INCIDENT_TYPE, COUNT (*)
FROM TABLE_AUDIT
WHERE SUBSTR(I_USER,7,2) IN ('AD','WF','F5')
AND SUBSTR(I_IMAGE_COPY,12,8) > '00000000'
AND SUBSTR(I_IMAGE_COPY,12,8) < '99999999'
AND D_H_STAMP BETWEEN '2007-06-01-00.37.30.124670' AND
'2007-06-30-00.37.30.124670'
GROUP BY C_INCIDENT_TYPE
ORDER BY 2
WITH UR


Second Query

SELECT C_INCIDENT_TYPE, COUNT (*)
FROM TABLE_AUDIT
WHERE SUBSTR(I_USER,7,2) NOT IN 'AD','WF','F5')
AND SUBSTR(I_IMAGE_COPY,12,8) > '00000000'
AND SUBSTR(I_IMAGE_COPY,12,8) < '99999999'
AND D_H_STAMP BETWEEN '2007-06-01-00.37.30.124670' AND
'2007-06-30-00.37.30.124670'
GROUP BY C_INCIDENT_TYPE
ORDER BY 2
WITH UR

The only statement difference between the two queries is: WHERE SUBSTR(I_USER,7,2) NOT IN 'AD','WF','F5') (The other query says 'IN')

That is my question... Thank You!

Aug 21 '07 #1

r035198x

I want my output in two columns like this:

C_INCIDENT_TYPE \ TOTAL COUNT


How do I combine my two queries to get result.

First Query

SELECT C_INCIDENT_TYPE, COUNT (*)
FROM TABLE_AUDIT
WHERE SUBSTR(I_USER,7,2) IN ('AD','WF','F5')
AND SUBSTR(I_IMAGE_COPY,12,8) > '00000000'
AND SUBSTR(I_IMAGE_COPY,12,8) < '99999999'
AND D_H_STAMP BETWEEN '2007-06-01-00.37.30.124670' AND
'2007-06-30-00.37.30.124670'
GROUP BY C_INCIDENT_TYPE
ORDER BY 2
WITH UR


Second Query

SELECT C_INCIDENT_TYPE, COUNT (*)
FROM TABLE_AUDIT
WHERE SUBSTR(I_USER,7,2) NOT IN 'AD','WF','F5')
AND SUBSTR(I_IMAGE_COPY,12,8) > '00000000'
AND SUBSTR(I_IMAGE_COPY,12,8) < '99999999'
AND D_H_STAMP BETWEEN '2007-06-01-00.37.30.124670' AND
'2007-06-30-00.37.30.124670'
GROUP BY C_INCIDENT_TYPE
ORDER BY 2
WITH UR

The only statement difference between the two queries is: WHERE SUBSTR(I_USER,7,2) NOT IN 'AD','WF','F5') (The other query says 'IN')

That is my question... Thank You!

How do you want to combine the queries? The two conditions contradict so you can't get data that satisfies both conitions.

Aug 22 '07 #2

PlayHard

How do you want to combine the queries? The two conditions contradict so you can't get data that satisfies both conitions.

I understand that the two conditions contradict each other. My goal is to get the output so it looks like this.

Therefore output will have three columns

C_INCIDENT_TYPE | COUNT (From first Query | COUNT (from Second Query |
| | |

Would I use a join to run the query? Just asking?

Thank You!

Aug 22 '07 #3

r035198x

I understand that the two conditions contradict each other. My goal is to get the output so it looks like this.

Therefore output will have three columns

C_INCIDENT_TYPE | COUNT (From first Query | COUNT (from Second Query |
| | |

Would I use a join to run the query? Just asking?

Thank You!

I get what you're talking about now. I don't think joining the queries is the proper way of doing it. I'd suggest doing a little bit of arithmetic with it. If you have n records and your first query returns a records, then shouldn't your second query return n - a records?

Aug 22 '07 #4

PlayHard

Thanks, I will look into that more...

Aug 22 '07 #5