CODEWITHSUNDEEP
sqloracle-databasedate-arithmetic
Abdulla
Abdulla

Reputation: 39

Oracle find whether a corresponding record exists within a number of days

I am trying to subtract to date from each other

The question says that I have to create a query to display the orders that were not shipped within 30 days of ordering.

Here is my trying:

select orderno 
from orders 
where 30> (select datediff(dd,s.ship_date,o.odate ) 
           from o.orders,s.shipment);

The error I get is

ERROR at line 1:
ORA-00942: table or view does not exist

These are the two tables :

SQL> desc orders

Name                                      Null?    Type
----------------------------------------- -------- ----------------------------

ORDERNO                                   NOT NULL NUMBER(3)
ODATE                                     NOT NULL DATE
CUSTNO                                             NUMBER(3)
ORD_AMT                                            NUMBER(5)

SQL> desc shipment
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------

ORDERNO                                   NOT NULL NUMBER(3)
WAREHOUSENO                               NOT NULL VARCHAR2(3)
SHIP_DATE                                          DATE

Upvotes: 1

Views: 498

Answers (3)

David Aldridge
David Aldridge

Reputation: 52356

You'd be wanting something along the lines of:

select ...
from   orders o
where  not exists (
         select null
         from   shipments s
         where  s.orderno   = o.orderno
         and    s.ship_date <= (o.odate + 30))

Date arithmetic is pretty easy if you just want a difference in days, as you can add or subtract days as integers. If it were months, quarters or years you'd want to use Add_Months().

Also, it's better in the query above to say "shipment_date <= (order_date + 30)" rather than "(shipment_date - order_date) <= 30)" as it lets indexes be used on the join key and shipment date combined. In practice you'd probably want an index on (s.orderno, s.ship_date) so that the shipment table does not have to be accessed for this query.

I used NOT EXISTS here because in the case that there might be multiple shipments per order you would want the query stop finding additional shipments if it has found a single one.

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1269873

Here is one method, using Oracle syntax:

select o.orderno
from orders o
where 30 > (select o.date - s.ship_date
            from shipment s
            where s.orderno = o.orderno
           );

Note the correlation clause in the subquery, but each table is only mentioned once.

The problem that you have is that an order could ship on more than on occasion -- and this would generate an error in the query, because the subquery would return more than one row. One solution is aggregation. You need to decide if the question is "the entire order does not ship within 30 days" or "no part of the order ships within 30 days". The latter would use MIN():

select o.orderno
from orders o
where (select MIN(o.date - s.ship_date)
       from shipment s
       where s.orderno = o.orderno
      ) > 30;

Upvotes: 1

SMA
SMA

Reputation: 37023

Your Syntax is wrong and you are trying to do a cross join implicitely. I think what you need is an INNER JOIN which i assume is going to return one row (if it returns multiple rows then use >ALL) like:

select orderno 
from orders 
where 30> (select s.ship_date - o.odate 
           from orders o INNER JOIN shipment s
           ON o.orderNo = s.orderNo);

Upvotes: 1

Related Questions