7 Replies Latest reply: Jun 2, 2010 8:23 AM by John Spencer RSS

    SubQuery Comparison in If Statement

    703775
      Is there a possibility that i could use a subquery as follows:

      IF SYS_CONTEXT('USERENV','SESSION_USER') not in (select USERNAME from dba_users a where A.INITIAL_RSRC_CONSUMER_GROUP='SYS_GROUP') THEN
      ....

      ELSE

      ....
      END IF.

      If this is not possible, does any one know any other workarounds.

      thanks in advance.
        • 1. Re: SubQuery Comparison in If Statement
          189821
          Use CASE ... WHEN ... ELSE ... END
          instead of
          IF ... ELSE ... END IF.

          Read about CASE in the SQL Reference.

          Urs
          • 2. Re: SubQuery Comparison in If Statement
            odie_63
            You can use this query (in an implicit or explicit cursor) :
            select 1
            from dba_users a 
            where A.INITIAL_RSRC_CONSUMER_GROUP='SYS_GROUP'
            and a.username = SYS_CONTEXT('USERENV','SESSION_USER');
            and act accordingly upon the result :
            no row selected = original condition fulfilled
            • 3. Re: SubQuery Comparison in If Statement
              Nimish Garg
              IN SQL U CAN USE AS FOLLOWS

              SELECT
                   CASE WHEN
                        (SELECT 1 FROM DUAL WHERE SYS_CONTEXT('USERENV','SESSION_USER') NOT IN (SELECT USERNAME FROM DBA_USERS A WHERE A.INITIAL_RSRC_CONSUMER_GROUP='SYS_GROUP')) = 1
                   THEN
                        'YES'
                   ELSE
                        'NO'
                   END X
              FROM
                   DUAL;
              • 4. Re: SubQuery Comparison in If Statement
                Nimish Garg
                AND IN PL/SQL TRY


                DECLARE
                     X NUMBER(1) :=0;
                BEGIN
                     SELECT 1 INTO X FROM DUAL WHERE SYS_CONTEXT('USERENV','SESSION_USER') NOT IN (SELECT USERNAME FROM DBA_USERS A WHERE A.INITIAL_RSRC_CONSUMER_GROUP='SYS_GROUP');
                     IF X = 1 THEN
                          DBMS_OUTPUT.PUT_LINE('YES');
                     ELSE
                          DBMS_OUTPUT.PUT_LINE('NO');
                     END IF;
                END;
                • 6. Re: SubQuery Comparison in If Statement
                  Sven W.
                  Nimish Garg wrote:
                  AND IN PL/SQL TRY


                  DECLARE
                       X NUMBER(1) :=0;
                  BEGIN
                       SELECT 1 INTO X FROM DUAL WHERE SYS_CONTEXT('USERENV','SESSION_USER') NOT IN (SELECT USERNAME FROM DBA_USERS A WHERE A.INITIAL_RSRC_CONSUMER_GROUP='SYS_GROUP');
                       IF X = 1 THEN
                            DBMS_OUTPUT.PUT_LINE('YES');
                       ELSE
                            DBMS_OUTPUT.PUT_LINE('NO');
                       END IF;
                  END;
                  Of cause much better would be Odies solution. That sql statement can be easiliy used in pl/sql.

                  example, not syntax checked
                  DECLARE
                        X NUMBER(1) :=0;
                  BEGIN
                     begin
                       select 1
                       into x
                       from dba_users a 
                       where A.INITIAL_RSRC_CONSUMER_GROUP='SYS_GROUP'
                       and a.username = SYS_CONTEXT('USERENV','SESSION_USER')
                       and rownum <= 1;
                     
                       DBMS_OUTPUT.PUT_LINE('YES');
                     exception
                       when no_data_found then
                          DBMS_OUTPUT.PUT_LINE('NO');
                     end;
                  END;
                  /
                  Edited by: Sven W. on Jun 2, 2010 1:57 PM: added rownum to avoid too_many_rows exception.
                  • 7. Re: SubQuery Comparison in If Statement
                    John Spencer
                    Sven:

                    "added rownum to avoid too_many_rows exception"

                    No harm in being careful, but if I got a too_many_rows exception on that query, I would definitely want to know about it :-)

                    User_name is effectively a unique key, so you should never get too_many_rows.

                    John