Oracle: Checking NOT NULL in Tuples
source link: https://www.codesd.com/item/oracle-checking-not-null-in-tuples.html
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
Oracle: Checking NOT NULL in Tuples
Objective : To select records from Order table where (delivery_date, type) is NOT IN (NULL, 'A').
select * from Orders;
Table : Orders
No Type Delivery_Date
1 A null
2 B 20150120
3 A 20150115
4 A 20150115
5 A 20150111
6 A 20150112
7 B null
8 B null
Expected Result :
No Type Delivery_Date
2 B 20150120
3 A 20150115
4 A 20150115
5 A 20150111
6 A 20150112
7 B null
8 B null
Tried the below constraints in where clause but to no luck.
1. WHERE (DELIVERY_DATE, TYPE) IS NOT IN (NULL, 'A')
2. WHERE (NVL(DELIVERY_DATE, 0), TYPE) IS NOT IN (0, 'A')
To make it work, have added a column by name required_row which is set to Y if this condition is (deliver_date is null and type = 'A') and selecting only the records where required_row is Y.
with orders
as
(select 1 as no, 'A' as type, null as delivery_date from dual union
select 2 as no, 'B' as type, 20150120 as delivery_date from dual union
select 3 as no, 'A' as type, 20150115 as delivery_date from dual union
select 4 as no, 'A' as type, 20150115 as delivery_date from dual union
select 5 as no, 'A' as type, 20150111 as delivery_date from dual union
select 6 as no, 'A' as type, 20150112 as delivery_date from dual union
select 7 as no, 'B' as type, null as delivery_date from dual union
select 8 as no, 'B' as type, null as delivery_date from dual
)
select * from ( select orders.*,
case when orders.delivery_date is null and type = 'A'
then 'N' else 'Y'
end as required_row from orders) where required_row='Y';
Any inputs/ thoughts on achieving the same in any other approach, keeping performance in view, would be appreciated.
Try this
select orders.* from orders where Delivery_Date is not null or type !='A'
/*Assuming type as a char field and this query will output all records
excluding deliverydate_null with type ='A' */
Modified the above query to include the sql snippet shared in fiddle.
Updated:
Here is Sample SQLFIDDLE
Recommend
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK