Oracle CASE Expression with Subquery

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 :slight_smile:

[edited, used bbc for better readability - Andreas]


BO-Newbie :us: (BOB member since 2003-07-01)

The only issue I see is that you should have single quote around your aliases, not double quotes. What problems are you experiencing with your code?


MichaelWelter :vatican_city: (BOB member since 2002-08-08)

J,

check out the thread ‘How to implement subquery in designer’ (posted 15-06-2004). The bottom line is that you put your subquery in a predefined condition.

Istvan


Istvan :netherlands: (BOB member since 2004-05-13)

You mean this one? :wink:

Note: If you want to link to a topic, you can right-click on the topic link (from your search results, or from the top page of the topic as it is displayed) and select Copy Shortcut. Then you can paste that information into your post, providing a direct link to the topic. Those instructions work for IE, other browsers may vary.


Dave Rathbun :us: (BOB member since 2002-06-06)