CODEWITHSUNDEEP
sqloracle-database
Craig
Craig

Reputation: 1985

IF statement in WHERE clause - SQL - Oracle

My table has the following structure:

ID     STATUS_1   STATUS_2   VERSION
1      Success    Disabled      5
2      Disabled   In_Progress   3
3      Disabled   Disabled      4

I need to return all rows where either

In the current table, the returned rows would be:

1      Success    Disabled      5
2      Disabled   In_Progress   3

So if my table looked like this:

ID     STATUS_1   STATUS_2   VERSION
1       --        Disabled     5
2       Disabled  --           3
3       Disabled  Disabled     4

The only row returned should be 

2       Disabled  --           3

as it has the lowest version (3).

Can this be accomplished with an IF statement within the WHERE clause?

Upvotes: 1

Views: 4009

Answers (4)

Benjamin
Benjamin

Reputation: 139

For if else you can use "case when...", "coalesce()" or "decode()". Following example uses "case when...".

create table states (
   ID       number, 
   STATUS_1 varchar2(50),   
   STATUS_2 varchar2(50),   
   VERSION  number
);

insert into states values (1,      'Success',    'Disabled',      5);
insert into states values (2,      'Disabled',   'In_Progress',   3);
insert into states values (3,      'Disabled',   'Disabled',      4);

select t.* 
  from ( select s.*, 
                rank() over (order by version asc) as rnk,
                count( case 
                          when s.status_1 = 'Success' then 1 
                          when s.status_1 = 'In_Progress' then 1
                          when s.status_2 = 'Success' then 1 
                          when s.status_2 = 'In_Progress' then 1 
                          else null
                        end
                     ) over () as cnt
           from states s ) t
  where (t.rnk = 1 and t.cnt = 0)
     or (t.status_1 in ('Success',    'In_Progress'))
     or (t.status_2 in ('Success',    'In_Progress'))

Have fun

Upvotes: 0

Aparna
Aparna

Reputation: 286

Please try the below query and let me know if it works 

    select id,STATUS_1,status_2,version from status 
        where status_1 in ('In progress','Success') 
        and Status_2 in ('In progress','Success')
    union all
    select id,status_1,status_2,version from 
      (
             select id,status_1,status_2,version,
             row_number() over(order by version) as rn 
             from status 
             where status_1 not in ('In progress','Success') or 
                   status_2 not in ('In progress','Success')
      )a
    where a.rn=1

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520908

The following query appears to be what you want. First, I use a CTE which computes the number of records having either Success or In_progress. Later, we can use this count to figure out whether we need to return a single row with the lowest version, or else all matching rows.

I take a UNION between a query which finds the record with the lowest version and a second query which returns all matching rows. The trick is that the single row from the first half of the UNION is only retained if the matching count is zero. Otherwise, it is dropped and this part of the UNION will contribute nothing to the result set. Appreciate that if we do retain the single lowest version record, then the second half of the query, by definition, would itself return nothing, still leaving us with that single record in the result set.

WITH cte AS (
    SELECT COUNT(*) cnt
    FROM yourTable
    WHERE
        STATUS_1 IN ('Success', 'In_Progress') OR
        STATUS_2 IN ('Success', 'In_Progress')
)

SELECT t.ID, t.STATUS_1, t.STATUS_2, t.VERSION
FROM
(
    SELECT ID, STATUS_1, STATUS_2, VERSION,
        ROW_NUMBER() OVER (ORDER BY VERSION) rn
    FROM yourTable
) t
WHERE
    t.rn = 1 AND
    (SELECT cnt FROM cte) = 0

UNION ALL

SELECT ID, STATUS_1, STATUS_2, VERSION
FROM yourTable
WHERE
    STATUS_1 IN ('Success', 'In_Progress') OR
    STATUS_2 IN ('Success', 'In_Progress')

Here is a demo where you can explore this query:

Rextester

Note that I used SQL Server in the demo because setting up Oracle demos is a pain, but the query should run on Oracle with little or no modification.

Upvotes: 1

Shahzaib
Shahzaib

Reputation: 218

You can use CASE statement, it works like IF-THEN-ELSE

Upvotes: 0

Related Questions