You are viewing this site as a guest. You can still ask questions and help others! Join our Dev2Dev IT Community to receive your own blog, share your knowledge, and much more.

My group by is not working when I use the decode function??

3 replies [Last post]
Guest's picture
Guest

Here is my query.. HELP!!

SELECT DECODE(PMRTAGGR_AGG.PMR_TRD_EFF_MO_ID,
1,1,2,1,3,1,4,2,5,2,6,2,7,3,8,3,9,3,10,4,11,4,12,4) AS
PMR_TRD_EFF_MO_ID
,SUM(PMRTAGGR_AGG.PMR_GRSS_SLS_AMT)
,SUM(PMRTAGGR_AGG.PMR_GRSS_REDS_AMT)
FROM PMRTAGGR_AGG,
PFUND_BSE,
PFIRM_BSE
WHERE (PMR_RETAIL_FL = 'Y' OR
PMR_RTL_401K_FL = 'Y')
AND PFU_MFC_NM IN ('PUTNAM','CAYMAN','ALTERNATIVE
PRODUCTS','ALLSTATE','HARTFORD')
AND PFU_PROD_NM <> 'PRIME MONEY MARKET'
-- AND PFI_DST_DSTRB_NO NOT IN ('92367', '93440', '69508', '51237' ,
-- '99978','52912', '52918', '98315', '98713')
AND PMR_EXEC_DSTRB_WID = PFI_DSTRB_WID
AND PMR_FUND_WID = PFU_FUND_WID
AND PMR_TRD_EFF_YR_ID = '2007'
and pfi_dst_dstrb_no = '25856'
GROUP BY PMRTAGGR_AGG.PMR_TRD_EFF_MO_ID

Thanks,
Matt

Larry's picture
User offline. Last seen 4 years 13 weeks ago. Offline
Joined: 03/18/2008
Posts: 19
Try this...

I've never had any luck trying to do a GROUP BY using the AS name. Try instead specifying the entire DECODE(...) in the GROUP BY. I'm pretty certain that will work.

CompShack's picture
User offline. Last seen 7 years 17 weeks ago. Offline
Joined: 12/09/2007
Posts: 167
Group By Decode

Larry is right - So Instead of your

GROUP BY PMRTAGGR_AGG.PMR_TRD_EFF_MO_ID
--use:
GROUP BY DECODE(PMRTAGGR_AGG.PMR_TRD_EFF_MO_ID,
1,1,2,1,3,1,4,2,5,2,6,2,7,3,8,3,9,3,10,4,11,4,12,4)

n/a
Matt's picture
Matt (not verified)
Thanks..It Works

Thanks..It Works