Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2004
    Posts
    50

    Arrow Unanswered: if statement in Select clause

    Hi,
    How can I use an 'if' statement inside a select clause.

    e.g.:

    select if(a>5) then 3 else 2 endif
    from xyz

    Thanks
    Ishan

  2. #2
    Join Date
    Jan 2004
    Location
    Singapore
    Posts
    89

    Thumbs up

    Make use of case:

    SQL> SELECT ename, empno,
    2 (CASE
    3 WHEN sal < 1000 THEN 'Low'
    4 WHEN sal BETWEEN 1000 AND 3000 THEN 'Medium'
    5 WHEN sal > 3000 THEN 'High'
    6 ELSE 'N/A'
    7 END) salary
    8 FROM emp
    9 ORDER BY ename;

    ENAME EMPNO SALARY
    ---------- ---------- ------
    ADAMS 7876 Medium
    ALLEN 7499 Medium
    BLAKE 7698 Medium
    CLARK 7782 Medium
    FORD 7902 Medium
    JAMES 7900 Low
    JONES 7566 Medium
    KING 7839 High
    MARTIN 7654 Medium
    MILLER 7934 Medium
    PPR 7369 Low
    RAD 888 Medium
    SCOTT 7788 Medium
    TURNER 7844 Medium
    WARD 7521 Medium
    Last edited by praveenpr; 05-07-04 at 08:27.
    Thanks and Regards,

    Praveen Pulikunnu

  3. #3
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Or, if you Oracle version doesn't support use of a CASE clause, perhaps DECODE could help. For example:

    Code:
    SELECT DECODE (SIGN (5 - a), -1, 3, 2)
      FROM xyz;

Posting Permissions

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