1 2 Previous Next 18 Replies Latest reply: Mar 21, 2013 2:22 AM by indra budiantho RSS

    not exists, but exists

    indra budiantho
      Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
      PL/SQL Release 10.2.0.4.0 - Production
      CORE     10.2.0.4.0     Production
      TNS for Linux: Version 10.2.0.4.0 - Production
      NLSRTL Version 10.2.0.4.0 - Production

      I found this case, why the answer is 'Y' ? tx
      /* Formatted on 3/21/2013 10:48:59 AM (QP5 v5.139.911.3011) */
      SELECT CASE
                WHEN EXISTS
                        (SELECT MAX (SYSDATE)
                           FROM DUAL
                          WHERE SYSDATE = SYSDATE - 1)
                THEN
                   'Y'
                ELSE
                   'N'
             END
                RES
        FROM DUAL
        • 1. Re: not exists, but exists
          Manik
          Hi Indra,
                              
          SELECT CASE
                    WHEN EXISTS
                            (SELECT MAX (SYSDATE)
                               FROM DUAL
                              WHERE SYSDATE = SYSDATE - 1)
                    THEN
                       'Y'
                    ELSE
                       'N'
                 END
                    RES
            FROM DUAL;
          The sub query inside the exists returns null:
          The below query confirms that , it returns some date (NVL returns only when you have a null)
           SELECT nvl(MAX (SYSDATE),sysdate - 10000)
                               FROM DUAL
                              WHERE SYSDATE = SYSDATE - 1 
          So your query works similar to this:

          Which is always TRUE.
           SELECT *
            FROM DUAL
           WHERE EXISTS (SELECT NULL FROM DUAL);
          Note: I dont have oracle 10g to test with, but I guess this is the case.

          Cheers,
          Manik.
          • 2. Re: not exists, but exists
            BCV
            Hi,
            Yeah Its Working Correctly,
             
              Whatever a value it has After 'EXISTS' Condition In CASE Statement , That Will Return. i.e 'Y', Here There Is No Chance For 'N' To Retrive even though 'Exists' has value or not.
            Regards
            BCV.
            • 3. Re: not exists, but exists
              523861
              Aggregate functions always return a row, so your
              SELECT MAX (SYSDATE)
                                   FROM DUAL
                                  WHERE SYSDATE = SYSDATE - 1
              returns a row and hence exists.


              change it to return a non-aggregate function:
              SQL> SELECT CASE
                2            when exists
                3                    (SELECT 'x'
                4                       FROM DUAL
                5                      WHERE SYSDATE = SYSDATE - 1)
                6            THEN
                7               'Y'
                8            ELSE
                9               'N'
               10         END
               11            res
               12    FROM DUAL;
              
              R
              -
              N
              
              1 row selected.
              • 4. Re: not exists, but exists
                avish16
                Please chk the below link, its clearly explained here in -

                http://www.techrepublic.com/article/oracle-tip-understand-how-nulls-affect-in-and-exists/5319615
                • 5. Re: not exists, but exists
                  523861
                  avish16 wrote:
                  Please chk the below link, its clearly explained here in -

                  http://www.techrepublic.com/article/oracle-tip-understand-how-nulls-affect-in-and-exists/5319615
                  I don't believe it has anything to do with the null, it's the fact that the inner query returns a row.

                  as I said above, aggregate queries always return a row even if the query brings back no data. a count() would have the same effect:
                  SQL> SELECT CASE
                    2            when exists
                    3                    (SELECT count(*)
                    4                       FROM DUAL
                    5                      WHERE SYSDATE = SYSDATE - 1)
                    6            THEN
                    7               'Y'
                    8            ELSE
                    9               'N'
                   10         END
                   11            res
                   12    FROM DUAL;
                  
                  R
                  -
                  Y
                  
                  1 row selected.
                  • 6. Re: not exists, but exists
                    indra budiantho
                    tx, all, you are all the man!
                    • 7. Re: not exists, but exists
                      dilipkumar10285
                      hi,

                      Short and sweet explanation from WhiteHat.
                      When there is row than EXISTS condition evaluates to true else false.
                      This is small doubt but confusing one to many people like me :-).

                      Thanks
                      Dilipkumar
                      • 8. Re: not exists, but exists
                        Manik
                        >
                        I don't believe it has anything to do with the null, it's the fact that the inner query returns a row.
                        When you say some row is returned, its NULL which is returned there when max() function was used in OP query.

                        Cheers,
                        Manik.
                        • 9. Re: not exists, but exists
                          523861
                          Manik wrote:
                          I don't believe it has anything to do with the null, it's the fact that the inner query returns a row.
                          When you say some row is returned, its NULL which is returned there when max() function was used in OP query.

                          Cheers,
                          Manik.
                          doesn't matter what the value is, so long as a row is returned: see the below
                          SQL> SELECT CASE
                            2            when exists
                            3                    (SELECT null
                            4                       FROM DUAL
                            5                      WHERE SYSDATE = SYSDATE - 1)
                            6            THEN
                            7               'Y'
                            8            ELSE
                            9               'N'
                           10         END
                           11            res
                           12    FROM DUAL;
                          
                          R
                          -
                          N
                          
                          1 row selected.
                          
                          SQL> SELECT CASE
                            2            when exists
                            3                    (SELECT null
                            4                       FROM DUAL
                            5                      WHERE SYSDATE = SYSDATE)
                            6            THEN
                            7               'Y'
                            8            ELSE
                            9               'N'
                           10         END
                           11            res
                           12    FROM DUAL;
                          
                          R
                          -
                          Y
                          
                          1 row selected.
                          • 10. Re: not exists, but exists
                            Manik
                            Again :)

                            OP was using MAX .. that is what I am trying to explain there.. you said aggregate function max returns a row and I am saying that row is NULL :)

                            Saying so, I agree with what you wrote in your post.

                            Cheers,
                            Manik.
                            • 11. Re: not exists, but exists
                              523861
                              Manik wrote:
                              Again :)

                              OP was using MAX .. that is what I am trying to explain there.. you said aggregate function max returns a row and I am saying that row is NULL :)

                              Saying so, I agree with what you wrote in your post.

                              Cheers,
                              Manik.
                              ahh- my fault, sorry - I miss-read your post :-)
                              • 12. Re: not exists, but exists
                                Manik
                                :) thats ok congos for promoted as Expert with this post ;)

                                Cheers,
                                Manik.
                                • 13. Re: not exists, but exists
                                  523861
                                  hehe thanks :-) it's a long slog to Guru from here......
                                  • 14. Re: not exists, but exists
                                    ranit B
                                    Congrats... Whitehat... The new 'Expert'

                                    I've a feeling that I don't know anything... but still a PRO... Got to learn a lotttt before going to EXPERT.

                                    I've very limited knowledge in areas like -
                                    1] MODEL - http://docs.oracle.com/cd/B19306_01/server.102/b14223/sqlmodel.htm
                                    2] XML DB
                                    3] SCHEDULERS, JOBS & CHAINS

                                    and lotsss more...

                                    Guys please cooperate. ;)
                                    But 've to agree this forum is 'THE BEST'
                                    1 2 Previous Next