DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: CASE syntax in an Oracle package - Oracle8i

  1. #1
    Join Date
    May 2002
    Posts
    29

  2. #2
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  3. #3
    Join Date
    May 2002
    Posts
    29
    Thanks for your reply. One last question, how would you use it in a dynamic SQL?

  4. #4
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    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
    Christopher R. Long
    ChrisRLong@HotMail.Com
    But that's just my opinion. I could be wrong

  5. #5
    Join Date
    May 2002
    Posts
    29
    That's very helpful. Mucha gracias...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width