Oracle : Checking for NOT NULL in Tuples

Question

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.

score 2 · Accepted Answer · edited Feb 21 '15 at 07:03

2

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

edited Feb 21 '15 at 07:03

Murali Rao

2,287
11
18

answered Feb 21 '15 at 04:23

Khurram Ali

1,659
4
20
37

Objective is to AVOID fetching record where delivery_date IS NULL and type = 'A' . I believe you are suggesting to use : delivery_date is NOT NULL and TYPE <> 'A', even then its not achieving the objective. – Murali Rao Feb 21 '15 at 04:48
@MuraliRao : let me understand clearly you want to avoid only those records in which delivery_date is not null plus type ='A' and want all other records include delivery_date null with type ='B' ?? – Khurram Ali Feb 21 '15 at 04:59
@MuraliRao See my edits and sqlfiddle you will surely get my point – Khurram Ali Feb 21 '15 at 05:05

score 1 · Answer 2 · answered Feb 21 '15 at 04:37

1

It`s possible to resolve this with not exists subquery:

SELECT * FROM order t 
WHERE not exists (
  SELECT 1 
  FROM order 
  WHERE 
    type = 'A' 
    and delivery_date is null 
    and id = t.id
)

answered Feb 21 '15 at 04:37

Marcelo Keiti

1,200
9
10

Thanks, this achieves our objective, Question - Is this performance efficient ? We are querying the table Orders twice. Compared to this, Is it not better to have a column which will help in identifying if its a required record or not (snippet added in description) ? – Murali Rao Feb 21 '15 at 05:00
You can try resolve this problem with `not in` or `minus` and compare the execution plan – Marcelo Keiti Feb 21 '15 at 10:59

Oracle : Checking for NOT NULL in Tuples

2 Answers2