This discussion is archived
7 Replies Latest reply: Nov 30, 2012 1:26 AM by BluShadow RSS

Can we put Select statement in When Clause

947775 Newbie
Currently Being Moderated
hi ,

Can we write query something like this

declare
COUNT NUMBER;
BEGIN
SELECT
CASE
WHEN
(SELECT COUNT(STATUS) FROM EXECUTION_LOG
WHERE PACKAGE_NAME = 'India Student Load'
AND STATUS IS NULL
AND TO_DATE(START_DATE, 'DD-MON-YYYY') = TO_DATE(SYSDATE, 'DD-MON-YYYY')) = 0 THEN COUNT :=1;

WHEN
(SELECT COUNT(STATUS) FROM EXECUTION_LOG
WHERE PACKAGE_NAME = 'India Student Load'
AND STATUS = 'S'
AND TO_DATE(START_DATE, 'DD-MON-YYYY') = TO_DATE(SYSDATE, 'DD-MON-YYYY')) = 1 THEN COUNT :=0;

WHEN
(SELECT COUNT(STATUS) FROM EXECUTION_LOG
WHERE PACKAGE_NAME = 'India Student Load'
AND STATUS IN ('I','E')
AND TO_DATE(START_DATE, 'DD-MON-YYYY') = TO_DATE(SYSDATE, 'DD-MON-YYYY')) = 1 THEN COUNT :=1;
ELSE NULL
END;
END;
  • 1. Re: Can we put Select statement in When Clause
    KeithJamieson Expert
    Currently Being Moderated
    Yes you can
    select case when dummy='Y'
    then
    (select 'X' from dual)
    else
    (select 'Y' from dual)
    end whentest
    from dual;
    
    SQL> /
    
    W                                                                               
    -                                                                               
    Y                                                                               
  • 2. Re: Can we put Select statement in When Clause
    BluShadow Guru Moderator
    Currently Being Moderated
    944772 wrote:
    hi ,

    Can we write query something like this

    declare
    COUNT NUMBER;
    BEGIN
    SELECT
    CASE
    WHEN
    (SELECT COUNT(STATUS) FROM EXECUTION_LOG
    WHERE PACKAGE_NAME = 'India Student Load'
    AND STATUS IS NULL
    AND TO_DATE(START_DATE, 'DD-MON-YYYY') = TO_DATE(SYSDATE, 'DD-MON-YYYY')) = 0 THEN COUNT :=1;

    WHEN
    (SELECT COUNT(STATUS) FROM EXECUTION_LOG
    WHERE PACKAGE_NAME = 'India Student Load'
    AND STATUS = 'S'
    AND TO_DATE(START_DATE, 'DD-MON-YYYY') = TO_DATE(SYSDATE, 'DD-MON-YYYY')) = 1 THEN COUNT :=0;

    WHEN
    (SELECT COUNT(STATUS) FROM EXECUTION_LOG
    WHERE PACKAGE_NAME = 'India Student Load'
    AND STATUS IN ('I','E')
    AND TO_DATE(START_DATE, 'DD-MON-YYYY') = TO_DATE(SYSDATE, 'DD-MON-YYYY')) = 1 THEN COUNT :=1;
    ELSE NULL
    END;
    END;
    You could include selects in a WHEN clause of a CASE statement inside an SQL query...
    SQL> select empno, case when (select dname from dept where dept.deptno = emp.deptno) = 'SALES' then 'Yeeehaaa!' else null end
      2  from emp;
    
         EMPNO CASEWHEN(
    ---------- ---------
          7369
          7499 Yeeehaaa!
          7521 Yeeehaaa!
          7566
          7654 Yeeehaaa!
          7698 Yeeehaaa!
          7782
          7788
          7839
          7844 Yeeehaaa!
          7876
          7900 Yeeehaaa!
          7902
          7934
    
    14 rows selected.
    But not inside a PL/SQL case statement....
    SQL> ed
    Wrote file afiedt.buf
    
      1  declare
      2    cursor c_emp is
      3    select * from emp;
      4  begin
      5    for e in c_emp
      6    loop
      7      case when (select dname from dept where deptno = e.deptno) = 'SALES' then
      8        dbms_output.put_line('Emp:'||e.empno||' - Yeehaa!');
      9      else
     10        dbms_output.put_line('Emp:'||e.empno);
     11      end case;
     12    end loop;
     13* end;
    SQL> /
        case when (select dname from dept where deptno = e.deptno) = 'SALES' then
                   *
    ERROR at line 7:
    ORA-06550: line 7, column 16:
    PLS-00103: Encountered the symbol "SELECT" when expecting one of the following:
    ( - + case mod new not null <an identifier>
    <a double-quoted delimited-identifier> <a bind variable>
    continue avg count current exists max min prior sql stddev
    sum variance execute forall merge time timestamp interval
    date <a string literal with character set specification>
    <a number> <a single-quoted SQL string> pipe
    <an alternatively-quoted string literal with character set specification>
    <an alternat
    ORA-06550: line 7, column 62:
    PLS-00103: Encountered the symbol ")" when expecting one of the following:
    . ( * @ % & - + ; / at for mod remainder rem
    <an exponent (**)> and or group having intersect minus order
    start union where connect || multiset
    Was easy enough to test yourself.
  • 3. Re: Can we put Select statement in When Clause
    BluShadow Guru Moderator
    Currently Being Moderated
    Keith Jamieson wrote:
    Yes you can
    No. The OP was asking if it could be in the WHEN clause. ;)
  • 4. Re: Can we put Select statement in When Clause
    Most Wanted!!!! Journeyer
    Currently Being Moderated
    use this
    DECLARE
       COUNT1   NUMBER;
    BEGIN
       SELECT CASE
                 WHEN COUNT (status) = 0 AND status IS NULL
                    THEN 1
                 WHEN COUNT (status) = 1 AND status = 'S'
                    THEN 0
                 WHEN COUNT (status) = 1 AND status IN ('I', 'E')
                    THEN 1
              END
         INTO COUNT1
         FROM execution_log
        WHERE package_name = 'India Student Load'
    --      AND status IN ('I', 'E')
          AND TO_DATE (start_date, 'DD-MON-YYYY') =
                                                  TO_DATE (SYSDATE, 'DD-MON-YYYY');
    END;
    Edited by: most wanted!!!! on Nov 30, 2012 1:03 AM
  • 5. Re: Can we put Select statement in When Clause
    Purvesh K Guru
    Currently Being Moderated
    Yes, you can.

    see this:-
    select case
            when (select trunc(sysdate - 1) from dual) = to_date('29-Nov-2012', 'DD-Mon-YYYY')
              then 'Yesterday'
            else
              'Today'
           end dy
      from dual;
    
    DY        
    --------- 
    Yesterday 
  • 6. Re: Can we put Select statement in When Clause
    Purvesh K Guru
    Currently Being Moderated
    BluShadow wrote:
    SQL> select empno, case when (select dname from dept where dept.deptno = emp.deptno) = 'SALES' then 'Yeeehaaa!' else null end
    I like the Yeeehaaa, Blu !!!)
  • 7. Re: Can we put Select statement in When Clause
    BluShadow Guru Moderator
    Currently Being Moderated
    944772 wrote:
    hi ,

    Can we write query something like this

    declare
    COUNT NUMBER;
    BEGIN
    SELECT
    CASE
    WHEN
    (SELECT COUNT(STATUS) FROM EXECUTION_LOG
    WHERE PACKAGE_NAME = 'India Student Load'
    AND STATUS IS NULL
    AND TO_DATE(START_DATE, 'DD-MON-YYYY') = TO_DATE(SYSDATE, 'DD-MON-YYYY')) = 0 THEN COUNT :=1;

    WHEN
    (SELECT COUNT(STATUS) FROM EXECUTION_LOG
    WHERE PACKAGE_NAME = 'India Student Load'
    AND STATUS = 'S'
    AND TO_DATE(START_DATE, 'DD-MON-YYYY') = TO_DATE(SYSDATE, 'DD-MON-YYYY')) = 1 THEN COUNT :=0;

    WHEN
    (SELECT COUNT(STATUS) FROM EXECUTION_LOG
    WHERE PACKAGE_NAME = 'India Student Load'
    AND STATUS IN ('I','E')
    AND TO_DATE(START_DATE, 'DD-MON-YYYY') = TO_DATE(SYSDATE, 'DD-MON-YYYY')) = 1 THEN COUNT :=1;
    ELSE NULL
    END;
    END;
    The problem with your query is that you are mixing syntax for an SQL CASE statement and a PL/SQL case statement. SQL cannot perform PL/SQL variable assignments like that.

    In terms of your code, it would be more like...
    declare 
      V_COUNT NUMBER;
    BEGIN 
      SELECT CASE
               WHEN (SELECT COUNT(STATUS)
                     FROM   EXECUTION_LOG
                     WHERE  PACKAGE_NAME = 'India Student Load'
                     AND    STATUS IS NULL
                     AND    TO_DATE(START_DATE, 'DD-MON-YYYY') = TO_DATE(SYSDATE, 'DD-MON-YYYY')) = 0 THEN 1
               WHEN (SELECT COUNT(STATUS) 
                     FROM   EXECUTION_LOG
                     WHERE  PACKAGE_NAME = 'India Student Load'
                     AND    STATUS = 'S'
                     AND    TO_DATE(START_DATE, 'DD-MON-YYYY') = TO_DATE(SYSDATE, 'DD-MON-YYYY')) = 1 THEN 0
               WHEN (SELECT COUNT(STATUS)
                     FROM   EXECUTION_LOG
                     WHERE  PACKAGE_NAME = 'India Student Load'
                     AND    STATUS IN ('I','E')
                     AND    TO_DATE(START_DATE, 'DD-MON-YYYY') = TO_DATE(SYSDATE, 'DD-MON-YYYY')) = 1 THEN 1
             ELSE
               NULL
             END;
      INTO V_COUNT
      FROM DUAL;
    END;
    Of course, that could probably be optimised as well.

    ETA: You should not use reserved words for your variable names, so "COUNT" should be something like "V_COUNT"


    Edited by: BluShadow on 30-Nov-2012 09:25
    forum playing up

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points