4 Replies Latest reply: Nov 21, 2012 12:36 AM by user2015671 RSS

    merge 2 queries into 1 query

    user2015671
      Hello,

      My below query sometimes return null value,

      select trim(max(SEQUENCE#)) from v$archived_log where applied='YES';

      If above query return nulls value then i need to use result of below querey.

      select trim(max(sequence#)) from v\$managed_standby;


      in one query i need to retrun value. How do i write such query?

      Many thanks in advance.
        • 1. Re: merge 2 queries into 1 query
          Manik
          May be this.. try ...

          SELECT TRIM (MAX (sequence#))
            FROM v$managed_standby
           WHERE 1 = (SELECT NVL (TRIM (MAX (SEQUENCE#)), 1)
                        FROM v$archived_log
                       WHERE applied = 'YES');
          Cheers,
          Manik.
          • 2. Re: merge 2 queries into 1 query
            Purvesh K
            user2015671 wrote:
            Hello,

            My below query sometimes return null value,

            select trim(max(SEQUENCE#)) from v$archived_log where applied='YES';

            If above query return nulls value then i need to use result of below querey.

            select trim(max(sequence#)) from v\$managed_standby;


            in one query i need to retrun value. How do i write such query?
            Maybe this way... (Untested)
            select NVL( (select trim(max(SEQUENCE#)) from v$archived_log where applied='YES'), (select trim(max(sequence#)) from v\$managed_standby)) seq
              from dual;
            • 3. Re: merge 2 queries into 1 query
              user2015671
              Thanks for your response but above query returns null value.

              SQL> SELECT TRIM (MAX (sequence#))
              FROM v$managed_standby
              WHERE 1 = (SELECT NVL (TRIM (MAX (SEQUENCE#)), 1)
              FROM v$archived_log
              WHERE applied = 'YES');

              TRIM(MAX(SEQUENCE#))
              ----------------------------------------

              SQL> SELECT NVL (TRIM (MAX (SEQUENCE#)), 1)
              FROM v$archived_log
              WHERE applied = 'YES' 2 3 ;

              NVL(TRIM(MAX(SEQUENCE#)),1)
              ----------------------------------------
              169

              SQL> SELECT TRIM (MAX (sequence#))
              FROM v$managed_standby 2 ;

              TRIM(MAX(SEQUENCE#))
              ----------------------------------------
              170


              Is there any way to do that?
              • 4. Re: merge 2 queries into 1 query
                user2015671
                Thanks Purvesh for your help.