Home » SQL & PL/SQL » SQL & PL/SQL » CASE shorthand?
CASE shorthand? [message #397253] Thu, 09 April 2009 10:34 Go to next message
TurboRogue
Messages: 1
Registered: April 2009
Junior Member
Hi..I'm very new to Oracle/SQL/and the like. However the question I have is more general syntax/logic based. Is it possible to use shorthand to write the following type of case statement? Something to the effect of:

case
when 1 xxx;
when 2 xxx;
when 3 xxx;
case 4 yyy;
end case;


What I'm trying for is:

case
when 1,2,3 xxx;
case 4 yyy;
end case;

Any help is appreciated. Thanks.
Re: CASE shorthand? [message #397254 is a reply to message #397253] Thu, 09 April 2009 10:35 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
No. But you can verify this by reading the documentation at http://tahiti.oracle.com
Re: CASE shorthand? [message #397257 is a reply to message #397253] Thu, 09 April 2009 10:44 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Depends on what you mean exactly:
SQL> select rownum
  2  ,      case when rownum = 1 or rownum = 2 or rownum = 3 then 'YES' else 'NO' end
  3  from   emp;

    ROWNUM CAS
---------- ---
         1 YES
         2 YES
         3 YES
         4 NO
         5 NO
         6 NO
         7 NO
         8 NO
         9 NO
        10 NO
        11 NO
        12 NO
        13 NO
        14 NO

14 rows selected.

SQL> select rownum
  2  ,      case rownum when 1 or 2 or 3 then 'YES' else 'NO' end
  3  from   emp;
,      case rownum when 1 or 2 or 3 then 'YES' else 'NO' end
                          *
ERROR at line 2:
ORA-00905: missing keyword


In short: kinda depends on how complicated your statement-to-be-tested is.
Re: CASE shorthand? [message #397259 is a reply to message #397257] Thu, 09 April 2009 10:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Maybe IN is closer to OP's wish:
SQL> select rownum,
  2         case
  3           when rownum in (1,2,3) then 'YES'
  4           when rownum=4 then 'BIS'
  5           else 'NO'
  6         end val
  7  from emp;
    ROWNUM VAL
---------- ---
         1 YES
         2 YES
         3 YES
         4 BIS
         5 NO
         6 NO
         7 NO
         8 NO
         9 NO
        10 NO
        11 NO
        12 NO
        13 NO
        14 NO

Regards
Michel
Re: CASE shorthand? [message #397318 is a reply to message #397253] Fri, 10 April 2009 01:14 Go to previous message
Littlefoot
Messages: 20888
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
If the example reflects real world problem, then this CASE's shortcut is DECODE:
SQL> select decode(rownum, 4, 'yyy', 'xxx')
  2  from emp;

DEC
---
xxx
xxx
xxx
yyy
xxx
xxx
xxx
xxx
xxx
xxx
xxx
xxx
xxx
xxx

14 rows selected.

SQL>
Previous Topic: Insert from select not same result as select
Next Topic: Help with cursor and trigger
Goto Forum:
  


Current Time: Fri Dec 02 20:49:07 CST 2016

Total time taken to generate the page: 0.07518 seconds