Need Help Combining Queries

dvdaddict32 · #1 (**permalink**) 05-06-10, 09:48

Hey guys,

I am stuck trying to figure out how to combine several queries into one master query. First here is my main query:

Code:

SELECT   A.AGTY_SYS_CD, A.BENEF_STAT_CD, 
         sum(case when A.RECIP_RETIR_DT <= '2010-04-01' then 1 end)   AS "TOTAL RETIREES", 
         sum(case when recip_retir_dt2 = '2010-05-01' and A.BENEF_SEQ_NBR = 1 then 1 end)  AS "NEW DROPS", 
         sum(case when A.BENEF_STAT_CD = 'AC' and RECIP_TYPE_CD in ('10') and  RECIP_RETIR_DT BETWEEN '2010-03-01' and '2010-03-30' then 1 end)   AS "A&S RETIREES", 
         sum(case When RIGHT (VOUCHER_ID_CD,1) = 'D' and A.RECIP_RETIR_DT BETWEEN '2010-03-01' and '2010-03-30' then 1 end)   AS "DISABILITIES" 


FROM     DSNP.PR01_T_RECIP_SYS A, 
         DSNP.PR01_T_ANTY_PYMT B 
WHERE    A.RECIP_SSN_NBR=B.RECIP_SSN_NBR 
AND      A.BENEF_STAT_CD IN ('AC', 'DP') 
AND      A.RECIP_TYPE_CD = '10' AND      A.`BENEF_SEQ_NBR = 1 
GROUP BY A.AGTY_SYS_CD, A.BENEF_STAT_CD 
ORDER BY 3 DESC

I discovered this week that I have to base my search criteria on a different field being min(anty_pymt_dt) = '2010-05-01 instead of retire date for these categories. This causes a problem because I have to use a derived table with the min function.

So for disabilities, the code is

Code:

SELECT count(A.recip_ssn_nbr) as "DISABILITIES"  FROM 



(SELECT A.recip_ssn_nbr FROM 
 dsnp.pr01_t_anty_pymt A,
     dsnp.pr01_t_recip_sys B
where A.recip_ssn_nbr = B.recip_ssn_nbr
and right(B.voucher_id_cd,1) = 'D'
group by A.recip_ssn_nbr
having min(A.anty_pymt_dt) = '2010-05-01') as A

Is there any way I can plug this code into my CASE statement?

The other way I was thinking was to make each category a derived table, but I don't know how to perform multiple inner joins. I tried a test UNION with this query:

Code:


SELECT count(A.recip_ssn_nbr) as "DISABILITIES"  FROM 



(SELECT A.recip_ssn_nbr FROM 
 dsnp.pr01_t_anty_pymt A,
     dsnp.pr01_t_recip_sys B
where A.recip_ssn_nbr = B.recip_ssn_nbr
and right(B.voucher_id_cd,1) = 'D'
group by A.recip_ssn_nbr
having min(A.anty_pymt_dt) = '2010-05-01') as A


UNION ALL

SELECT count(A.recip_ssn_nbr) as "TOTAL RETIREES"

FROM
 (SELECT *
   FROM     DSNP.PR01_T_RECIP_SYS
   WHERE    RECIP_RETIR_DT <= '2010-04-01'
   AND      BENEF_STAT_CD IN ('AC', 'DP')
   AND      RECIP_TYPE_CD = '10'
   AND      BENEF_SEQ_NBR = 1
    ) as A

However, the output doesn't give me different column names. It only shows Disabilities as the column name.

Can anyone help? Thanks!

dvdaddict32 · #2 (**permalink**) 05-06-10, 12:25

Hey guys,

I made some progress and figured a way to combine the queries by referencing my alias names inside derived tables.

It would still be easier for me if I could use that case statement so if anyone knows a way to use min function inside case statement, that would be great.

Lenny77 · #3 (**permalink**) 05-06-10, 14:06

If you want to have name on the column after UNION ALL you have to use the same name on all parts:

Code:

SELECT count(A.recip_ssn_nbr) as "DISABILITIES"  
FROM 
(SELECT 1
FROM 
 dsnp.pr01_t_anty_pymt A,
     dsnp.pr01_t_recip_sys B
where A.recip_ssn_nbr = B.recip_ssn_nbr
and right(B.voucher_id_cd,1) = 'D'
group by A.recip_ssn_nbr
having min(A.anty_pymt_dt) = '2010-05-01') as A


UNION ALL

SELECT count(A.recip_ssn_nbr) as "DISABILITIES"

FROM
 (SELECT 1
   FROM     DSNP.PR01_T_RECIP_SYS
   WHERE    RECIP_RETIR_DT <= '2010-04-01'
   AND      BENEF_STAT_CD IN ('AC', 'DP')
   AND      RECIP_TYPE_CD = '10'
   AND      BENEF_SEQ_NBR = 1
    ) as A

Lenny

tonkuma · #4 (**permalink**) 05-06-10, 16:16

Quote:

If you want to have name on the column after UNION ALL you have to use the same name on all parts:

One way to use the same names on all parts would be like this:

Code:

SELECT
       'DISABILITIES'         AS item_name
     , count(A.recip_ssn_nbr) as value_of_item
  FROM 
...
 
UNION ALL
 
SELECT
       'TOTAL RETIREES'       AS item_name
     , count(A.recip_ssn_nbr) AS value_of_item
  FROM
...

tonkuma · #5 (**permalink**) 05-06-10, 16:27

Is it not necessary to specify codes in comments in the following example?:

Code:

(
SELECT
/*
       AGTY_SYS_CD
     , BENEF_STAT_CD
     ,
*/
       'DISABILITIES'         AS item_name
     , count(A.recip_ssn_nbr) as value_of_item
  FROM 
  (SELECT A.recip_ssn_nbr
/*
        , B.AGTY_SYS_CD
        , B.BENEF_STAT_CD 
*/
     FROM dsnp.pr01_t_anty_pymt A
        , dsnp.pr01_t_recip_sys B
    WHERE A.recip_ssn_nbr = B.recip_ssn_nbr
      AND right(B.voucher_id_cd,1) = 'D'
/*
      AND B.BENEF_STAT_CD IN ('AC', 'DP')
      AND B.RECIP_TYPE_CD = '10'
      AND B.BENEF_SEQ_NBR = 1
*/
    GROUP BY
          A.recip_ssn_nbr
/*
        , B.AGTY_SYS_CD
        , B.BENEF_STAT_CD 
*/
   HAVING min(A.anty_pymt_dt) = '2010-05-01'
  ) as A
/*
 GROUP BY
       AGTY_SYS_CD
     , BENEF_STAT_CD 
*/

UNION ALL

SELECT
/*
       AGTY_SYS_CD
     , BENEF_STAT_CD
     ,
*/
       'TOTAL RETIREES'       AS item_name
     , count(A.recip_ssn_nbr) AS value_of_item
  FROM
  (SELECT *
     FROM DSNP.PR01_T_RECIP_SYS
    WHERE RECIP_RETIR_DT <= '2010-04-01'
      AND BENEF_STAT_CD IN ('AC', 'DP')
      AND RECIP_TYPE_CD = '10'
      AND BENEF_SEQ_NBR = 1
  ) as A
/*
 GROUP BY
       AGTY_SYS_CD
     , BENEF_STAT_CD 
*/
)
ORDER BY 1
    /* , 2 , 3 */
;

Internet Services

Web Development

Digital Marketing

Consumer Tech