This discussion is archived
4 Replies Latest reply: Oct 7, 2012 7:47 AM by APC RSS

Need a help in SQL query

VenkatRakesh Newbie
Currently Being Moderated
Hi,
I need a help in writing an SQL query . I am actually confused how to write a query. Below is the scenario.
CREATE TABLE demand_tmp
( item_id  NUMBER,
  org_id   NUMBER,
  order_line_id NUMBER,
  quantity NUMBER,
  order_type NUMBER
 );
 
 
 CREATE TABLE order_tmp
 ( item_id  NUMBER,
   org_id   NUMBER,
   order_line_id NUMBER,
   open_flag  VARCHAR2(10)
 );
 
 
INSERT INTO demand_tmp
SELECT 12438,82,821,100,30 FROM dual;
 
INSERT INTO demand_tmp
SELECT 12438,82,849,350,30 FROM dual;

INSERT INTO demand_tmp
SELECT 12438,82,NULL,150,29 FROM dual;

INSERT INTO demand_tmp
SELECT 12438,82,0,50,-1 FROM dual;



INSERT INTO order_tmp
SELECT 12438,82,821,'Y' FROM dual;

INSERT INTO order_tmp
SELECT 12438,82,849,'N' FROM dual;

Demand_tmp:
Item_id        org_id   order_line_id       quantity       order_type     
12438     82                 821                 100       30     
12438     82                 849                 350       30     
12438     82              NULL                 150       29     
12438     82                    0                  50       -1     
                         
Order_tmp :
Item_id        org_id        order_line_id      open_flag     
12438     82                  821                Y     
12438     82                  849                N     
I need to fetch the records from demand_tmp table whose order_line_id is present in order_tmp and having open_flag as 'Y' or if order_type in demand_tmp table is 29.
The below query will give the records whose order line id is present in order_tmp. But, If i need records which are having order_type=29 the below query wont return any records as order_line_id is NULL. If I place outer join I will get other records also (In this example order_type -1 records) . Please help me how can we write a query for this. Expected o/p is below.
Query :

Select item_id,org_id,order_line_id,quantity,order_type,open_flag
from demand_tmp dt , order_tmp ot
where dt.order_line_id = ot.order_line_id
AND dt.item_id=ot.item_id
AND dt.org_id = ot.org_id
AND ot.open_flag = 'Y';

Expected Output :                         
item_id     org_id     order_line_id     quantity     order_type   open_flag
12438     82                 821               100                    30             Y
12438     82              NULL               150                29         NULL 
Thanks in advance,
Rakesh

Edited by: Venkat Rakesh on Oct 7, 2012 6:32 PM

Edited by: Venkat Rakesh on Oct 7, 2012 8:39 PM
  • 1. Re: Need a help in SQL query
    Bas de Klerk Pro
    Currently Being Moderated
    Hi Rakesh,

    the query is not working as you would like ( but IS working as expected ) since your trying to compare null to another value.
    Comparing null always results in FALSE, also if you compare null to null. This is because null means undefined.
    select 1 from dual where null=null results in no data found.

    I would suggest using a non natural key to join the tables.
    For example include a column ID in the master table which is filled with a sequence and include that field as a foreign key in the detail table.
    This way you can easily join master and detail on ID = ID, and you don't have to worry about null values in this column since it's always filled with data.

    Regards,
    Bas

    btw, using the INNER JOIN and OUTER JOIN syntax in your SQL makes it better readable, since you're separating join conditions from the where clause, just a tip ;)
  • 2. Re: Need a help in SQL query
    VenkatRakesh Newbie
    Currently Being Moderated
    Hi Bas,
    Actually, I can't modify table structure . For few records which have order type as 29 will always have order_line_id NULL. I just created an example here to explain my scenario.
    I need to fetch the orders percent in demand_tmp table whose order_line_id is present in order_tmp and also need to fetch order_type 29 records irrespective of previous condition.
    Outer join will bring records other than order type 29 and not present in order_tmp . That is my main concern.

    Thanks in advance,
    Rakesh
  • 3. Re: Need a help in SQL query
    APC Oracle ACE
    Currently Being Moderated
    Please edit your post so that the text between the set-up and your query is readable without a scrollbar.

    Cheers, APC
  • 4. Re: Need a help in SQL query
    APC Oracle ACE
    Currently Being Moderated
    Venkat Rakesh wrote:
    Outer join will bring records other than order type 29 and not present in order_tmp . That is my main concern.
    Could you add some data which illustrates your concern. Because given your current data set, this query will deliver your expected outcome:
    Select dt.item_id
           , dt.org_id
           , dt.order_line_id
           , dt.quantity
           , dt.order_type
           , ot.open_flag
    from demand_tmp dt left join order_tmp ot
          on ( dt.order_line_id = ot.order_line_id
               AND dt.item_id = ot.item_id
               AND dt.org_id = ot.org_id )
    WHERE ot.open_flag = 'Y'
    OR dt.order_type = 29
    Cheers, APC

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points