Results 1 to 13 of 13
  1. #1
    Join Date
    Nov 2003
    Posts
    23

    Unanswered: CASE cannot be used in PL/SQL in Ora8i, need options

    Hi,

    I have a stored procedure that uses the CASE function in an sql query (snippet below). Oracle8i doesn't allow using CASE in PL/SQL, can anyone suggest another way I can write the sql?

    Thanks.

    Code:
    SELECT
    CASE
    When CAST(BlFundsTransfer.amount as int)  <= 2500 Then '1/ $0 -> $25.00'
    When CAST(BlFundsTransfer.amount as int)  <= 5000 Then '2/ $25.01 -> $50.00'
    When CAST(BlFundsTransfer.amount as int)  <= 10000 Then '3/ $50.01 -> $100.00'
    when CAST(BlFundsTransfer.amount as int)  <= 50000 Then '4/ $100.01 -> $500.00'
    When CAST(BlFundsTransfer.amount as int)  >= 50001 Then '5/ > $500.00'
    Else 'Gap in range'
    End "Payment Range"
    FROM  BlFundsTransfer 
    /

  2. #2
    Join Date
    Jul 2003
    Posts
    50
    Long time since I wrote any PL/SQL... but isn't there IF-THEN-ELSE available there?

  3. #3
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    You can use DECODE and SIGN, but it isn't pretty:

    Code:
    SELECT
    DECODE (SIGN(CAST(BlFundsTransfer.amount as int)-2501, -1, '1/ $0 -> $25.00',
      DECODE (SIGN(CAST(BlFundsTransfer.amount as int)-5001, -1, '1/ $0 -> $50.00',
        DECODE...
    ...
            )
          )
        )
      )
    )
    Alternatively, create a view based on the CASE expressin and then select from the view in PL/SQL.

  4. #4
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    Not to sure if it will work but how about creating a view with the case statement and then in the plsql selecting from the view?

    Alan

  5. #5
    Join Date
    Dec 2002
    Location
    Sunny Florida
    Posts
    121
    When working with 8i I uses ansi SQL and found your same problem. The work around it to make the statement dynamic and execute it with 'execute immediate'

  6. #6
    Join Date
    Dec 2002
    Location
    Sunny Florida
    Posts
    121
    Here is a small sample:
    execute immediate 'update ME_INITPAY SET DAYSLATE=case when procdate > TARGETDATE then to_number(procdate - TARGETDATE) else 0 end';

    EXECUTE IMMEDIATE 'INSERT INTO PARAMS
    SELECT AGENCY, CAUSE, SITE,NATURE,'
    ||CASE P_LOB WHEN 'WC' THEN 'DIVISION,' ELSE 'LEVEL1,' END
    ||CASE P_LOB WHEN 'WC' THEN 'DEPT,' ELSE 'LEVEL2,' END
    ||CASE P_LOB WHEN 'WC' THEN 'SUB,' ELSE 'LEVEL3,' END||
    'NVL((SELECT DESCRIPTION FROM ' ||P_LOB|| 'RSVTYP WHERE RESERVESCATEGORY='||''''||'1'||''''||'),'||''''||' NONE'||''''||'),
    NVL((SELECT DESCRIPTION FROM ' ||P_LOB|| 'RSVTYP WHERE RESERVESCATEGORY='||''''||'2'||''''||'),'||''''||' NONE'||''''||'),
    NVL((SELECT DESCRIPTION FROM ' ||P_LOB|| 'RSVTYP WHERE RESERVESCATEGORY='||''''||'3'||''''||'),'||''''||' NONE'||''''||'),
    NVL((SELECT DESCRIPTION FROM ' ||P_LOB|| 'RSVTYP WHERE RESERVESCATEGORY='||''''||'4'||''''||'),'||''''||' NONE'||''''||'),
    NVL((SELECT DESCRIPTION FROM ' ||P_LOB|| 'RSVTYP WHERE RESERVESCATEGORY='||''''||'5'||''''||'),'||''''||' NONE'||''''||'),
    NVL((SELECT DESCRIPTION FROM ' ||P_LOB|| 'RSVTYP WHERE RESERVESCATEGORY='||''''||'6'||''''||'),'||''''||' NONE'||''''||')
    FROM TABLE_'||CASE P_LOB WHEN 'WC' THEN 'WCCOMP' ELSE 'LIAB' END||'_PARAM';

  7. #7
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    Following on from Tony's DECODE suggestion, you can also switch the expressions around to avoid nested DECODEs. The following is untested, and you may need to round the amount values etc to get the same results, and I didn't bother with the CAST (is that really needed?)

    Code:
    SELECT DECODE(-1,
                  SIGN(ft.amount - 2501),  '1/ $0 -> $25.00',
                  SIGN(ft.amount - 5001),  '2/ $25.01 -> $50.00',
                  SIGN(ft.amount - 10001), '3/ $50.01 -> $100.00',
                  SIGN(ft.amount - 50001), '4/ $100.01 -> $500.00',
                  '5/ > $500.00') AS payment_range
    FROM  blfundstransfer ft

  8. #8
    Join Date
    Dec 2002
    Location
    Sunny Florida
    Posts
    121
    I argue against decode and sign because it isn't portable. I do Oracle and SQL Server. I like to avoid rewriting things as much as possible use generic commands whenever possible. Besides, upgrading to 9 or higher whipes all this out and the dynamic example works all the same.

  9. #9
    Join Date
    Nov 2003
    Posts
    23
    Thanks you all for your responses. I'm following up using decode as it's the easiest one for me to understand.

    WilliamR, your suggestion doesn't cater to the following conditions:

    When CAST(BlFundsTransfer.amount as int) >= 50001 Then '5/ > $500.00'
    Else 'Gap in range'

    How can I use SIGN to cater to amounts greater than or equal to 50001?

    Thanks.

  10. #10
    Join Date
    Jan 2004
    Posts
    492
    Not to sound like a complete idiot, but I don't see where a gap would come in?

    The first 4 clauses check for values less than or equal to 50000. The 5th clause checks for any values greater than or equal to 50001. So if you pick any value, one of those clauses will pick it up. What am I missing here?
    Oracle OCPI (Certified Practicing Idiot)

  11. #11
    Join Date
    Nov 2003
    Posts
    23
    The condition

    Else 'Gap in range'

    is there to cater for non-numeric values such as null.

    I'm not sure how to add this condition to the statement.

  12. #12
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Quote Originally Posted by AndrewG1976
    The condition

    Else 'Gap in range'

    is there to cater for non-numeric values such as null.

    I'm not sure how to add this condition to the statement.
    You can enhance William's elegant solution like this:
    Code:
    SELECT DECODE(-1,
                  SIGN(ft.amount - 2501),  '1/ $0 -> $25.00',
                  SIGN(ft.amount - 5001),  '2/ $25.01 -> $50.00',
                  SIGN(ft.amount - 10001), '3/ $50.01 -> $100.00',
                  SIGN(ft.amount - 50001), '4/ $100.01 -> $500.00',
                  SIGN(ft.amount - 999999999999999), '5/ > $500.00',
                  'Gap in range') AS payment_range
    FROM  blfundstransfer ft
    Like your CASE expression, this will handles nulls. However, also like your CASE expression, it will fall over with an error if ft.amount is a non-numeric value like 'xxx'.

    Question: why would a column called "amount" be defined as a string rather than a number in the first place?

  13. #13
    Join Date
    Nov 2003
    Posts
    23
    Thanks for this. Much appreciated.

    Question: why would a column called "amount" be defined as a string rather than a number in the first place?
    No idea, this is the database design and must serve some purpose. At least one would hope so!

Posting Permissions

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