Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2003
    Location
    VA
    Posts
    1

    Unanswered: select case is allowed in select statement

    I am using Oracle 8.1.5
    does this version supports

    Getting this error
    ERROR at line 3:
    ORA-00923: FROM keyword not found where expected

    SELECT
    CASE
    WHEN (Admission.ADMISSION_DATE < TO_DATE('10/01/2002 00:00:00','MM/DD/YYYY HH24:MIS') AND Zone_Status.ABBREVIATION IN( 'DOM', 'FDP' ))
    THEN
    NVL( Admission.CURRENT_QUANTITY, 0 )
    ELSE
    0
    END AS "BEG_DOM"
    FROM ADMISSION admission, ZONE_STATUS Zone_Status
    WHERE
    (Admission.ZONE_STATUS_ID=Zone_Status.ZONE_STATUS_ ID(+) AND Admission.RECORD_STATE IN( 18, 19)

  2. #2
    Join Date
    Jul 2003
    Location
    US
    Posts
    314

    Arrow Correct Syntax

    Kindly use the following format for using CASE in SQLs.

    select CASE column_name

    WHEN '1' expr1
    WHEN '2' expr2
    es column_name_wanted
    from table_name;

  3. #3
    Join Date
    Jul 2003
    Location
    US
    Posts
    314

    Arrow Correction

    Kindly use the following format for using CASE in SQLs.

    select CASE column_name

    WHEN '1' THEN expr1
    WHEN '2' THEN expr2
    ELSE exp3 as column_name_wanted

    from table_name;

  4. #4
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi,

    I don't think your original syntax is out, I haven't checked it carefull for brackets etc, but this sort of thing is quite usual...

    select
    case when h.somecol > h.othercol and h.othercol <> 0
    then 'something'
    else null
    end as columnname
    from...

    Hth
    bill

  5. #5
    Join Date
    Oct 2002
    Location
    Plymouth UK
    Posts
    116
    You need to use DECODE because CASE is not supported in 8.1.5. The syntax is:

    DECODE(value, if1, then1 [, if2, then2]... , else)

  6. #6
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    Aruneesh, the syntax for case has changed between 8i (8.1.6 I think) and 9i, for 8i you have to do case when <expr> but with 9i you can also do case <column> when <expr>.

    In the original query it might also be worth checking there are spaces before the keyword like 'FROM'. There is also a closing bracket missing at the end of the WHERE clause.

    Alan

Posting Permissions

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