CODEWITHSUNDEEP
sqloracle-database
Deb
Deb

Reputation: 35

Conditional selection from a table Oracle SQL

I have a table with columns value, id, h (hierarchy) and date columns. A snapshot of it looks like this: 

value | id  | h  | date 
---   |---  |--- |------------
 1.0  | 32  | 0  | Jul 3, 2017
 5.6  | 54  | 1  | Jul 2, 2017
 3.5  | 178 | 2  | Jul 3, 2017
 3.4  | 178 | 2  | Jul 1, 2017

This table has no primary key (aka there are multiple records for each value, id, h, or date). id and h have one to one relationship.

I want to write a query so that assuming today is Jul 3, it will return a value for each id for today, ordered by h, and showing null value if there is no record for today for that id. aka like this

value | id  | h  
---   |---  |--- 
 1.0  | 32  | 0  
 null | 54  | 1  
 3.5  | 178 | 2

Upvotes: 0

Views: 496

Answers (3)

San
San

Reputation: 4538

Using analytical function, and case statements, you can achieve the result. Assuming that column h is never going to have a negative value, here is the query:

SQL> WITH cte_sample(value,id,h,D) AS (
  2   SELECT 1.0, 32, 0, to_date('Jul 18, 2017', 'Mon DD, YYYY') FROM dual UNION ALL
  3   SELECT  5.6, 54, 1, to_date('Jul 17, 2017', 'Mon DD, YYYY')  FROM dual UNION ALL
  4   SELECT 3.5, 178, 2, to_date('Jul 18, 2017', 'Mon DD, YYYY')  FROM dual UNION ALL
  5   SELECT 3.4, 178, 2, to_date('Jul 16, 2017', 'Mon DD, YYYY')  FROM dual UNION ALL
  6   SELECT 3.6, 178, 3, to_date('Jul 18, 2017', 'Mon DD, YYYY')  FROM dual ),
  7  ----------------------
  8  -- End of data
  9  ----------------------
 10  extracted_table AS (
 11  SELECT CASE WHEN d = trunc(sysdate) THEN VALUE ELSE NULL END AS VALUE,
 12         ID,
 13         h,
 14         d,
 15         row_number() OVER(PARTITION BY ID ORDER BY CASE WHEN d = trunc(SYSDATE) THEN -9 ELSE h END) rn
 16    FROM cte_sample)
 17  SELECT VALUE, ID, h FROM extracted_table WHERE rn = 1;

Output:

     VALUE         ID          H
---------- ---------- ----------
         1         32          0
                   54          1
       3.5        178          2

Upvotes: 0

fg78nc
fg78nc

Reputation: 5232

You can implement conditional IF-ELSE logic using CASE statement for the first column:

  select 
        // if current date is the same as date in date_col output value
     case when sysdate - date_col < 1 then value
        // else output null
     else null end "value", 
     id, h
  from my_table
  order by 3; // sort by h column

Upvotes: 0

Max08
Max08

Reputation: 1025

Doing a left join might help :- 

Select b.value,a.id,a.h
from 
(Select distinct id,h 
from <table>) a 
left join
(Select id,h,value 
from <table> where date > trunc(SYSDATE)) b
on a.id = b.id
and a.h = b.h;

Upvotes: 1

Related Questions