CASE syntax in an Oracle package - Oracle8i

[rkeokitv]

My first question, is the CASE syntax supported in Oracle8i, v. 8.1.7? I believe it is supported in Oracle9i.

If it's supported in Oracle8i, can someone shed a light and show some examples?

I have this package and it's causing this error:

The following error has occurred:

PACKAGE BODY CANDS.CS_SUMMIT
On line: 73
PLS-00103: Encountered the symbol "CASE" when expecting one of the following:

( - + all mod null
avg
count current di

Here is the actual code:

DELETE FROM TEMP_TABLE_1_ACTUALS;
INSERT INTO TEMP_TABLE_1_ACTUALS
SELECT A.ACTIVITY_ID,
A.ACCOUNTING_DT,
SUM(DECODE(A.ANALYSIS_TYPE, 'COM', A.RESOURCE_AMOUNT, 0)) ENC_AMOUNT,
SUM(DECODE(A.ANALYSIS_TYPE, 'EST', A.RESOURCE_AMOUNT, 0)) EST_AMOUNT,
SUM(CASE
WHEN
A.ANALYSIS_TYPE IN('ACT','BIL','MBL')
THEN
A.RESOURCE_AMOUNT
ELSE
0
END
) EXP_AMOUNT,
SUM(DECODE(A.ANALYSIS_TYPE, 'BUD', A.RESOURCE_AMOUNT, 'TRF',
A.RESOURCE_AMOUNT, 'RVN', A.RESOURCE_AMOUNT, 'CFS',
A.RESOURCE_AMOUNT, 'CFE', A.RESOURCE_AMOUNT,0)) REV_AMOUNT
FROM TS_PROJ_RESOURCE@TEMP_LINK A
WHERE A.BUSINESS_UNIT = 'KOC'
AND (A.PROJECT_ID LIKE 'C%'
OR A.ACTIVITY_ID LIKE 'NS%' OR A.ACTIVITY_ID LIKE 'NN%')
AND (A.ACCOUNT > '699999' or A.ACCOUNT = '141200' or A.ACCOUNT = '141300' )
AND A.ACCOUNT NOT IN ('710511','975900','710599','849100','988990',' ')
AND A.ANALYSIS_TYPE IN ('ACT','COM','BIL','MBL','EST','BUD','TRF','RVN','CFS','CFE')
GROUP BY A.ACTIVITY_ID, A.ACCOUNTING_DT;

[jmodic]

In 8i, CASE expressions are only available in SQL, not in PL/SQL. If you want to use it in SQL statement inside PL/SQL block, you will have to use dynamic SQL.

In 9i, where there is one common SQL parser for both SQL and PL/SQL, CASE expressions are transparently supported in PL/SQL too.

[rkeokitv]

Thanks for your reply. One last question, how would you use it in a dynamic SQL?

[chrisrlong]

EXECUTE IMMEDIATE
'INSERT INTO TEMP_TABLE_1_ACTUALS
SELECT A.ACTIVITY_ID,
A.ACCOUNTING_DT,
SUM(DECODE(A.ANALYSIS_TYPE, ''COM'', A.RESOURCE_AMOUNT, 0)) ENC_AMOUNT,
SUM(DECODE(A.ANALYSIS_TYPE, ''EST'', A.RESOURCE_AMOUNT, 0)) EST_AMOUNT,
SUM(CASE
WHEN
A.ANALYSIS_TYPE IN(''ACT'',''BIL'',''MBL'')
THEN
A.RESOURCE_AMOUNT
ELSE
0
END
) EXP_AMOUNT,
SUM(DECODE(A.ANALYSIS_TYPE, ''BUD'', A.RESOURCE_AMOUNT, ''TRF'',
A.RESOURCE_AMOUNT, ''RVN'', A.RESOURCE_AMOUNT, ''CFS'',
A.RESOURCE_AMOUNT, ''CFE'', A.RESOURCE_AMOUNT,0)) REV_AMOUNT
FROM TS_PROJ_RESOURCE@TEMP_LINK A
WHERE A.BUSINESS_UNIT = ''KOC''
AND (A.PROJECT_ID LIKE ''C%''
OR A.ACTIVITY_ID LIKE ''NS%'' OR A.ACTIVITY_ID LIKE ''NN%'')
AND (A.ACCOUNT > ''699999'' or A.ACCOUNT = ''141200'' or A.ACCOUNT = ''141300'' )
AND A.ACCOUNT NOT IN (''710511'',''975900'',''710599'',''849100'',''988990'','' '')
AND A.ANALYSIS_TYPE IN (''ACT'',''COM'',''BIL'',''MBL'',''EST'',''BUD'',''TRF'',''RVN'',''CFS'',''CFE'')
GROUP BY A.ACTIVITY_ID, A.ACCOUNTING_DT';

- Chris

[rkeokitv]

That's very helpful. Mucha gracias...