SQL question

Jim Sniatecki

SQL question
I have a SQL problem I want to return three columns as one result set.



AGE_NR ALL REF



The SQL right now I can do as two pieces but I want to combine them
into one sql stmt.





SQL is:



SQL 1:



SELECT C.AGE_NR, COUNT(*) AS ALL

FROM ARQ.POL_ROL A,

ARQ.POL_QT B,

ARQ.ROL C

WHERE A.POL_ID = B.POL_ID

AND A.ROL_ID = C.ROL_ID

AND B.POL_QT_DRVD_FRM_QT <> ''

GROUP BY C.AGE_NR ;



This will give me a result set , for example:



AGE_NR ALL

15 3

16 10

17 4





SQL2:



SELECT C.AGE_NR, COUNT(*) AS REF

FROM ARQ.POL_ROL A,

ARQ.POL_QT B,

ARQ.ROL C

WHERE A.POL_ID = B.POL_ID

AND A.ROL_ID = C.ROL_ID

AND B.POL_QT_DRVD_FRM_QT <> ''

AND B.AGNCY_ID NOT IN ('AA1000',

'BB1000','BC1000','DD1001','EE1000','FF2000',

'GG1000','HH1000','JJ1001','MM1000','NN1010',

'WW1000')

GROUP BY C.AGE_NR



The result set would be for example:



AGE_NR REF

15 10

16 20

17 5





I would like to combine the SQLs to get for example a result set such
as:



AGE_NR ALL REF

15 3 10

16 10 20

17 4 5





Thanks

Jim Sniatecki

Erie Insurance DBA






-----------------------------------------
Disclaimer: This message (and any attachments) is confidential and
is intended only for the addressee(s). This message may contain
information that is protected by one or more legally recognized
privileges. If the reader of this message is not the intended
recipient, I did not intend to waive, and I do not waive, any legal
privilege or the confidentiality of the message. If you receive
this message in error, please notify me immediately by return
e-mail and delete this message from your computer and network
without saving it in any manner. The unauthorized use,
dissemination, distribution, or reproduction of this message,
including attachments, is prohibited and may be unlawful.


---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

John Miller

Re: SQL question
(in response to Jim Sniatecki)
Use two table expressions and join them together. The following
untested SQL should work for you



SELECT A.AGE_NR, A.ALL, B.REF

FROM (SELECT C.AGE_NR AS AGE_NR, COUNT(*) AS ALL

FROM ARQ.POL_ROL A,

ARQ.POL_QT B,

ARQ.ROL C

WHERE A.POL_ID = B.POL_ID

AND A.ROL_ID = C.ROL_ID

AND B.POL_QT_DRVD_FRM_QT <> ''

GROUP BY C.AGE_NR) AS A,

(SELECT C.AGE_NR AS AGE_NR, COUNT(*) AS REF

FROM ARQ.POL_ROL A,

ARQ.POL_QT B,

ARQ.ROL C

WHERE A.POL_ID = B.POL_ID

AND A.ROL_ID = C.ROL_ID

AND B.POL_QT_DRVD_FRM_QT <> ''

AND B.AGNCY_ID NOT IN ('AA1000',

'BB1000','BC1000','DD1001','EE1000','FF2000',

'GG1000','HH1000','JJ1001','MM1000','NN1010',

'WW1000')

GROUP BY C.AGE_NR) AS B

WHERE A.AGE_NR = B.AGE_NR

ORDER BY A.AGE_NR



John



________________________________



Disclaimer: This message (and any attachments) is confidential and
is intended only for the addressee(s). This message may contain
information that is protected by one or more legally recognized
privileges. If the reader of this message is not the intended
recipient, I did not intend to waive, and I do not waive, any legal
privilege or the confidentiality of the message. If you receive
this message in error, please notify me immediately by return
e-mail and delete this message from your computer and network
without saving it in any manner. The unauthorized use,
dissemination, distribution, or reproduction of this message,
including attachments, is prohibited and may be unlawful.


------------------------------------------------------------------------
--------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the
archives and home page at http://www.idugdb2-l.org/archives/db2-l.html.
From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences
at http://conferences.idug.org/index.cfm


The information transmitted is intended only for the addressee and may contain confidential, proprietary and/or privileged material. Any unauthorized review, distribution or other use of or the taking of any action in reliance upon this information is prohibited. If you receive this in error, please contact the sender and delete or destroy this message and any copies.

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Doyle Mark

Re: SQL question
(in response to John Miller)
(Heroic music . . .) This looks like a job for a nested table
expression!


Try

select first. AGE_NR, first.ALL, second.REF

from ( SELECT C.AGE_NR, COUNT(*) AS ALL
FROM ARQ.POL_ROL A,
ARQ.POL_QT B,
ARQ.ROL C
WHERE A.POL_ID = B.POL_ID
AND A.ROL_ID = C.ROL_ID
AND B.POL_QT_DRVD_FRM_QT <> ''
GROUP BY C.AGE_NR ) as first
Inner join
(SELECT C.AGE_NR, COUNT(*) AS REF
FROM ARQ.POL_ROL A,
ARQ.POL_QT B,
ARQ.ROL C
WHERE A.POL_ID = B.POL_ID
AND A.ROL_ID = C.ROL_ID
AND B.POL_QT_DRVD_FRM_QT <> ''
AND B.AGNCY_ID NOT IN ('AA1000',
'BB1000','BC1000','DD1001','EE1000','FF2000',
'GG1000','HH1000','JJ1001','MM1000','NN1010',
'WW1000')
GROUP BY C.AGE_NR) As second
on first.AGE_NR = second.AGE_NR
order by . . .

Captain NTE

(who, disguised as a mild-mannered DBA, remains in obscurity
until confronted with complex, real-world data isssues, and then
transforms into that caped supercoder CAPTAIN NTE!)


-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]
On Behalf Of Sniatecki, Jim
Sent: Friday, January 26, 2007 2:22 PM
To: [login to unmask email]
Subject: SQL question



I have a SQL problem I want to return three columns as
one result set.



AGE_NR ALL REF



The SQL right now I can do as two pieces but I want to
combine them into one sql stmt.




---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm