7 Replies Latest reply: Nov 30, 2012 3:26 AM by BluShadow RSS

    Can we put Select statement in When Clause

    947775
      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
          Keith Jamieson
          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
            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
              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!!!!
                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
                  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
                    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
                      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