Hi, Gang!
I’m building some pretty complex algorithm objects using Designer. They work EXCEPT for the ones that include subqueries. Is there a way to write this SELECT statement as a CASE expression? I can’t write any code in the WHERE window, as it mistakenly gets applied to the entire SELECT statement. I tried to simplify the script as much as possible…I have to leave now, but I’ll replace the Z.IIF statement with GREATEST/LEAST statements Thursday (Z.IIF is a home-grown function that converts code to GREATEST/LEAST).
SUM(DECODE(FT, '01',
Z.IIF(UB,'IN','1,3',
DECODE(RECI, 'Y',Z.IIF(CGPB_Y,'GT',0,CGPB + CGPB_Y,CGPB),CGPB),0),0)) AR60_CGP_BAL
FROM
(SELECT
UB_IND "UB" ,
FIN_TYPE_CD "FT" ,
REVENUE_EXCESS_COLLECT_IND "RECI" ,
SUM(Z.IIF(BALANCE_TYPE,'IN','01,02,04,06,70,71',AMOUNT,0)
- Z.IIF(BALANCE_TYPE,'IN','03,15,17,18,21,36,46,50,60,63,64,65',AMOUNT,0)) "CGPB" ,
SUM(Z.IIF(BALANCE_TYPE,'IN','12,14,20,45,55,58,59',AMOUNT,0)
- Z.IIF(BALANCE_TYPE,'IN','11,48',AMOUNT,0)) "CGPB_Y"
FROM
MIDBDBA.VTS_RS_BO_APPROPRIATION
WHERE
APPN_STATUS_CD = 'A'
GROUP BY
UB_IND ,
FIN_TYPE_CD ,
REVENUE_EXCESS_COLLECT_IND)
Thanks for your consideration!
J
[edited, used bbc for better readability - Andreas]
BO-Newbie (BOB member since 2003-07-01)