Results 1 to 4 of 4

Thread: DECODE problem

  1. #1
    Join Date
    Jul 2002
    Posts
    42

    Unhappy Unanswered: DECODE problem

    Hello,

    I need to create a fiscal year out of a date stored in a string formated like 'YYYYMMDD'. The easyest way is to use the month portion of the date. First thing that comes to my mind is using the DECODE function as I used it quite often. But for some reason, I always get an error from the SQL compiler. Here is whet I want to do:

    SQL> SELECT
    2 DECODE(TO_NUMBER(SUBSTR(date_commande, 5, 2)),
    3 TO_NUMBER(SUBSTR(date_commande, 5, 2)) < 4,
    4 TO_NUMBER(SUBSTR(date_commande, 1, 4))-1,
    5 TO_NUMBER(SUBSTR(date_commande, 1, 4))) FISCAL_YEAR
    6 FROM ap_bon_commande_entete;
    TO_NUMBER(SUBSTR(date_commande, 5, 2)) < 4,
    *
    ERROR at line 3:
    ORA-00907: missing right parenthesis

    The fiscal year starts on April first and ends on Marsh 31st the next year but is numbered with the year of the beginning year.

    What am I doing wrong? I'm on a 9i database, so mayby I should try the SELECT CASE statement?

    Please help!

    Thanks a million.

  2. #2
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    You cant use expressions like x<4 in decode, use case.

    Alan

  3. #3
    Join Date
    Jul 2003
    Posts
    2,296
    you want CASE, not DECODE.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  4. #4
    Join Date
    Apr 2004
    Location
    Toronto, Canada
    Posts
    249
    Hi,


    It looks like you are attempting to get a fiscal year from a date.

    Another way to do the same is to use the following query:

    PHP Code:
     select TO_NUMBER(TO_CHARADD_MONTHS(<date>, -3), 'YYYY')) from dual 
    Hope that helps.

    Ravi

Posting Permissions

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