SQL Pass Through

[rib742]

Good evening.

I've been tinkering with SQL Pass Throughs to make my ODBC reports run faster but I can't figure something out.

There are several fields from the records I'm retrieving that share a key. What I'm trying to do is keep these records on 1 line of the output but can't seem to get it.

Here is a sample of the SQL I have.

SELECT (CASE WHEN AF.A = 'G' THEN 'Y' END) AS G, (CASE WHEN AF.A = 'N' THEN 'Y' END) AS N, (CASE WHEN AF.A = 'M' THEN 'Y' END) AS M, AF.QUAL

FROM XYZ.ADD_FAC AS AF

What I want to have returned is something like:

G N M QUAL
Y Y 123456
Y Y 5678901

Instead I get:

G N M QUAL
Y 123456
Y 123456
Y 5678901
Y 5678901

Any help or direction would be greatly appreciated. Again, I've just gotten myself into SQL pass throughs recently and trying to teach myself/learn as I go.

Thanks!

 

[r937]

select max(case when AF.A = 'G' 
                then 'Y' end) as G
     , max(case when AF.A = 'N' 
                then 'Y' end) as N
     , max(case when AF.A = 'M' 
                then 'Y' end) as M
     , AF.QUAL
  from XYZ.ADD_FAC as AF
group
    by AF.QUAL

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)

 

[rib742]

Thanks r937. This is putting me on the right track but it seems that I'm having to put all my select fields in the group by clause in order for it to work. If I do the above on the excerpt sample from the whole query then it works. Is there something different for when you have a Where clause as well?

I've been looking in a SQL handbook I have but then again it's not specific to access pass throughs.

 

[JamesLean]

Can you give an example of your data and what result you want out of it? I'm not sure I understand at the moment?

--James

 

[rib742]

Here's the query with the one field I want to group by. But when I go to run it, I get an error message stating that last field in the select is not part of the group by. When I take the last field out of the select, the error message indicates the next last field and so on.

SELECT MAX(CASE WHEN AF.PROD_GRP = 'GATE' THEN 'Y' END) AS GATED, MAX(CASE WHEN AF.PROD_GRP = 'NGATE' THEN 'Y' END) AS NONGATED, MAX(CASE WHEN AF.PROD_GRP = 'MCARE' THEN 'Y' END) AS MCARE, MAX(CASE WHEN AF.PRODUCT = 'MHMO' THEN 'Y' END) AS MHMO, MAX(CASE WHEN AF.PRODUCT = 'MC' THEN 'Y' END) AS MC, MAX(CASE WHEN AF.PRODUCT = 'MEPO' THEN 'Y' END) AS MEPO, MAX(CASE WHEN AF.PRODUCT = 'MPPO' THEN 'Y' END) AS MPPO, AF.CATEGORY, DQ.QUALFR_ID, DQ.PBG_NO, DQ.PBG_TYPE_CD, D.CNT_ID, D.CNT_VERS_NO, D.DOC_ID, D.DOC_VERS_NO, D.TEMPL_ID, D.TEMPL_VERS_NO, RTRIM(D.TEMPL_TYPE), RTRIM(D.DOC_NAME), D.DOC_HMO_EFFTV_DT, D.DOC_TERMN_DT, D.ADM_PAYMT_IND, D.NEXT_COLA_DT, D.COLA_INTERVAL, DP.CNT_PRVDR_ID, DP.PRV_EFFTV_DT, DP.PRV_TERMN_DT, CI.CNT_EFFTV_DT, CI.CNT_TERMN_DT, RTRIM(CI.CNT_TYPE_CD) || ' - ' || RTRIM(CT.LONG_DEFN) AS "CONTRACT TYPE", RTRIM(CI.CATGRY_CD) || ' - ' || RTRIM(PI.LONG_DEFN) AS "CONTRACT CATEGORY", CI.RECV_SIGN_DT, CI.CNT_RENW_DT, CB.STATUS_TISTAMP, RTRIM(CB.CNT_NAME_ALL_CAPS)

FROM AF, AG, DQ, D, DP, CB, CI, PI,CT

WHERE AF.ADD_FACTRS_ID = AG.ADD_FACTRS_ID
AND AG.ADD_FCTR_GRP_ID = DQ.ADD_FCTR_GRP_ID
AND (DQ.DOC_ID = D.DOC_ID
AND DQ.DOC_VERS_NO = D.DOC_VERS_NO)
AND (D.DOC_ID = DP.DOC_ID
AND D.DOC_VERS_NO = DP.DOC_VERS_NO
AND D.CNT_ID = DP.CNT_ID
AND D.CNT_VERS_NO = DP.CNT_VERS_NO
AND DP.PRV_TERMN_DT >= CURRENT DATE
AND D.DOC_TERMN_DT >= DP.PRV_TERMN_DT)
AND (DQ.CNT_ID = CB.CNT_ID
AND DQ.CNT_VERS_NO = CB.CNT_VERS_NO)
AND (CB.CNT_ID = CI.CNT_ID
AND CB.CNT_VERS_NO = CI.CNT_VERS_NO)
AND CI.CNT_TYPE_CD = CT.CNT_TYPE_CD
AND (CI.CATGRY_CD = PI.SHORT_DEFN
AND PI.PICK_LIST_TYPE = 'CATGRY_CD')
AND DQ.DOC_ID > 1
AND DQ.PBG_NO > 0
AND D.RELEASED = 'R'
AND D.TEMPL_TYPE LIKE '%FFS'
AND CI.CNT_TYPE_CD NOT LIKE 'D%'
AND CB.STATUS_CD = 'RLSD'

GROUP BY DQ.QUALFR_ID