This discussion is archived
4 Replies Latest reply: Nov 20, 2012 10:19 PM by Manik RSS

merge 2 queries into 1 query

user2015671 Newbie
Currently Being Moderated
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 Expert
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Thanks Purvesh for your help.

Legend

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