Hey guys,
I have the following query which gives me total number of SSNS per county along with other info:
Code:
SELECT CTY_ID_NM AS COUNTY_NAME
,COUNT(*) AS COUNT
,SUM(ANTY_PYMT_TOT_AMT) AS TOTAL_AMOUNT
FROM (SELECT D.CTY_ID_NM AS CTY_ID_NM
,A.RECIP_SSN_NBR AS RECIP_SSN_NBR
,SUM(ANTY_PYMT_TOT_AMT) AS ANTY_PYMT_TOT_AMT
FROM DSNP.PR01_T_RECIP_SYS A
,DSNP.PR01_T_ANTY_PYMT B
,DSNP.PR01_T_MBR_CITY C
,DSNP.PR01_T_CTY D
WHERE A.RECIP_SSN_NBR = B.RECIP_SSN_NBR
AND A.BENEF_SEQ_NBR = B.BENEF_SEQ_NBR
AND A.RECIP_SSN_NBR = C.MBR_SSN_NBR
AND C.CTY_ID_CD = D.CTY_ID_CD
AND A.RECIP_TYPE_CD = '10'
AND B.ANTY_PYMT_DT BETWEEN '2007-07-01'
AND '2008-06-30'
AND B.ANTY_PYMT_STAT_CD = 'A'
AND C.MBR_ADDR_SEQ_NBR = 1
GROUP BY
D.CTY_ID_NM
,A.RECIP_SSN_NBR
UNION ALL
SELECT D.CTY_ID_NM AS CTY_ID_NM
,A.RECIP_SSN_NBR AS RECIP_SSN_NBR
,SUM(ANTY_PYMT_TOT_AMT) AS ANTY_PYMT_TOT_AMT
FROM DSNP.PR01_T_RECIP_SYS A
,DSNP.PR01_T_ANTY_PYMT B
,DSNP.PR01_T_RECIP_CITY C
,DSNP.PR01_T_CTY D
WHERE A.RECIP_SSN_NBR = B.RECIP_SSN_NBR
AND A.BENEF_SEQ_NBR = B.BENEF_SEQ_NBR
AND A.RECIP_SSN_NBR = C.RECIP_SSN_NBR
AND C.CTY_ID_CD = D.CTY_ID_CD
AND A.RECIP_TYPE_CD IN ('20', '30', '40')
AND B.ANTY_PYMT_DT BETWEEN '2007-07-01'
AND '2008-06-30'
AND B.ANTY_PYMT_STAT_CD = 'A'
AND C.RECIP_ADDR_SEQ_NBR = 1
GROUP BY
D.CTY_ID_NM
,A.RECIP_SSN_NBR
) AS A
GROUP BY
A.CTY_ID_NM
The output looks like this:
COUNTY COUNT TOTAL AMOUNT
BENTON 234 $23,999.
I need to modify the query to show me each SSN number, their city name (CITY_ID_NM), and state (STATE_ID_CD) which makes up these count totals.
I modified the query like this which does give me the SSN's:
Code:
SELECT CTY_ID_NM AS COUNTY_NAME
,RECIP_SSN_NBR as SSN
FROM (SELECT D.CTY_ID_NM AS CTY_ID_NM
,A.RECIP_SSN_NBR AS RECIP_SSN_NBR
,SUM(ANTY_PYMT_TOT_AMT) AS ANTY_PYMT_TOT_AMT
FROM DSNP.PR01_T_RECIP_SYS A
,DSNP.PR01_T_ANTY_PYMT B
,DSNP.PR01_T_MBR_CITY C
,DSNP.PR01_T_CTY D
WHERE A.RECIP_SSN_NBR = B.RECIP_SSN_NBR
AND A.BENEF_SEQ_NBR = B.BENEF_SEQ_NBR
AND A.RECIP_SSN_NBR = C.MBR_SSN_NBR
AND C.CTY_ID_CD = D.CTY_ID_CD
AND A.RECIP_TYPE_CD = '10'
AND B.ANTY_PYMT_DT BETWEEN '2007-07-01'
AND '2008-06-30'
AND B.ANTY_PYMT_STAT_CD = 'A'
AND C.MBR_ADDR_SEQ_NBR = 1
GROUP BY
D.CTY_ID_NM
,A.RECIP_SSN_NBR
UNION ALL
SELECT D.CTY_ID_NM AS CTY_ID_NM
,A.RECIP_SSN_NBR AS RECIP_SSN_NBR
,SUM(ANTY_PYMT_TOT_AMT) AS ANTY_PYMT_TOT_AMT
FROM DSNP.PR01_T_RECIP_SYS A
,DSNP.PR01_T_ANTY_PYMT B
,DSNP.PR01_T_RECIP_CITY C
,DSNP.PR01_T_CTY D
WHERE A.RECIP_SSN_NBR = B.RECIP_SSN_NBR
AND A.BENEF_SEQ_NBR = B.BENEF_SEQ_NBR
AND A.RECIP_SSN_NBR = C.RECIP_SSN_NBR
AND C.CTY_ID_CD = D.CTY_ID_CD
AND A.RECIP_TYPE_CD IN ('20', '30', '40')
AND B.ANTY_PYMT_DT BETWEEN '2007-07-01'
AND '2008-06-30'
AND B.ANTY_PYMT_STAT_CD = 'A'
AND C.RECIP_ADDR_SEQ_NBR = 1
GROUP BY
D.CTY_ID_NM
,A.RECIP_SSN_NBR
) AS A
However, when I try to add in the city and state fields, SQL tells me it is not valid in the context. I don't know how to reference these fields outside the sub-query because the UNION references this data in two different tables called PR01_T_RECIP_CITY and PR01_T_MBR_CITY.
Anyone have any idea how I can add the SSN's city and state to my query result?
dBforums
>
How Can I Get Detail on this Query?
06-12-09, 16:59
Linear Mode
