Need help writing a IF THEN ELSE or Case in select statement.

I have the following query, which I need to run only if database_role = 'PRIMARY':

SELECT (p.log_seq - s.log_seq) diff
  FROM v$database d,
       -- Logs archived on primary
       (SELECT MAX (sequence#) log_seq
          FROM v$archived_log
         WHERE archived = 'YES' AND standby_dest = 'NO') p,
       -- archive logs applied to standby
       (SELECT MAX (sequence#) log_seq
          FROM v$archived_log
         WHERE standby_dest = 'YES' AND applied = 'YES') s

How to incorporate a IF or CASE statement such that if null then it returns a zero.?
Rao_SAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

YZlatCommented:
Use function NVL:

select nvl(field ,0)

Open in new window

Rao_SAuthor Commented:
I tried this, it works fine on primary, but on standby i get 'no rows'..
On standby i need to get back a zero...

SELECT nvl((p.log_seq - s.log_seq),0) diff
  FROM v$database d,
       -- Logs archived on primary
       (SELECT MAX (sequence#) log_seq
          FROM v$archived_log
         WHERE archived = 'YES' AND standby_dest = 'NO') p,
       -- archive logs applied to standby
       (SELECT MAX (sequence#) log_seq
          FROM v$archived_log
         WHERE standby_dest = 'YES' AND applied = 'YES') s
  where d.database_role = 'PRIMARY';
YZlatCommented:
try this

SELECT (nvl(p.log_seq,0) - nvl(s.log_seq,0)) diff
   FROM v$database d,
        -- Logs archived on primary
        (SELECT MAX (sequence#) log_seq
           FROM v$archived_log
          WHERE archived = 'YES' AND standby_dest = 'NO') p,
        -- archive logs applied to standby
        (SELECT MAX (sequence#) log_seq
           FROM v$archived_log
          WHERE standby_dest = 'YES' AND applied = 'YES') s
   where d.database_role = 'PRIMARY'; 

Open in new window

Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

johnsoneSenior Oracle DBACommented:
YZlat - Using NVL won't work.  You are not getting a NULL returned by the query you are getting no rows.

One way is to manufacture a second row and ensure that it sorts second, then choose the first row.  This way if the first query returns no rows, you get the 0 that you wanted.
SELECT diff 
FROM   (SELECT * 
        FROM   (SELECT 1                         sort_order, 
                       ( p.log_seq - s.log_seq ) diff 
                FROM   v$database d, 
                       -- Logs archived on primary 
                       (SELECT Max (sequence#) log_seq 
                        FROM   v$archived_log 
                        WHERE  archived = 'YES' 
                               AND standby_dest = 'NO') p, 
                       -- archive logs applied to standby 
                       (SELECT Max (sequence#) log_seq 
                        FROM   v$archived_log 
                        WHERE  standby_dest = 'YES' 
                               AND applied = 'YES') s 
                UNION ALL 
                SELECT 2 sort_order, 
                       0 diff 
                FROM   dual) 
        ORDER  BY sort_order) 
WHERE  ROWNUM = 1; 

Open in new window

There are certainly many other ways to do it.
Rao_SAuthor Commented:
hi johnsone, with your query i get a diff = 783, i need to put the check - database_role='PRIMARY' so that i get diff = 0.

hi YZlat, from your query, i get 'No rows returned' .. i need to get 0 back...
johnsoneSenior Oracle DBACommented:
Sorry, I modified your original query, which I thought was working.  This is the modified "newer" query.  All I did was add the extra parts for getting a 0 if there are no rows.
SELECT diff 
FROM   (SELECT * 
        FROM   (SELECT 1                         sort_order, 
                       ( p.log_seq - s.log_seq ) diff 
                FROM   v$database d, 
                       -- Logs archived on primary 
                       (SELECT Max (sequence#) log_seq 
                        FROM   v$archived_log 
                        WHERE  archived = 'YES' 
                               AND standby_dest = 'NO') p, 
                       -- archive logs applied to standby 
                       (SELECT Max (sequence#) log_seq 
                        FROM   v$archived_log 
                        WHERE  standby_dest = 'YES' 
                               AND applied = 'YES') s 
                WHERE  d.database_role = 'PRIMARY' 
                UNION ALL 
                SELECT 2 sort_order, 
                       0 diff 
                FROM   dual) 
        ORDER  BY sort_order) 
WHERE  ROWNUM = 1; 

Open in new window

FYI - I removed the NVL function as it is not necessary.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rao_SAuthor Commented:
hi johnsone, that worked! thank you!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.