0

I am trying to come up with a Oracle view , I am ok with the first part as you see below. (Only pasting part of the syntax here for ease). the issue is that , in order to get the sales manger info, I have to extract it from CUST_KNVP table for which there is another sql syntax i am using. My problem is to combine both these syntax below into one. Can anyone please guide me on this. As you will see tables deliver and Orders appear in both syntax. Thanks

**
/*Main View Sql syntax*/
     select 
    d.gate_entry_date 
    k.account_exec_name,
    o.load_start_date,
    o.local_end_date,
    d.local_arrival_date,
    p.freight,
    d.actual_gate_entry_date,
    m.shipm_start,
    m.shipment_end
    from deliver d, ship_pickup_date o,  key_customer k,
    reel_date m, orders p, 
    where d.item = m.order_item
    and d.sold_to = k.sold_to
    and d.shipment_number = o.ship_id
    and d.sales_doc = p.sales_doc
    and   d.item = p.item

/* SQL syntax to get the sales manager below, need to combine both as final product.*/
    select sales_manager 
    from (
    select distinct d.sold_to, p.sales_district, d.sales_office, d.sales_rep
    from deliver d, orders p
    where d.sales_doc = p.sales_doc
    and   d.item = p.item) driver, cust_knvp mgr
    where driver.sold_to = mgr.customer
    and   driver.sales_district = mgr.sales_district
    and   driver.sales_office = mgr.sales_office
    and   driver.sales_rep = mgr.sales_rep

Looking for help, thanks.

Improve this question
4
  • I don't understand - why don't you just join to the extra table? Commented Aug 21, 2017 at 15:05
  • As you see in second sql, I am making a table called driver and joining to the cust_knvp table. Commented Aug 21, 2017 at 15:14
  • 2
    But why? You already have those underlying tables in the first part; why you you just add a join to cust_knvp in the first part, and forget about the second part? Commented Aug 21, 2017 at 15:16
  • I need the joins in second part to get the sales manager info. those4 joins in second part as you see , I am making a driver table. Can you please write it out for me what you are trying to suggest, as to how I will get sales manager without a subquery. Commented Aug 21, 2017 at 15:28

1 Answer 1

Reset to default
1

You can just join to the cust_knvp table directly, referring to the underlying table columns instead of using the driver inline view, which is accessing the same data anyway:

select 
    d.gate_entry_date,
    k.account_exec_name,
    o.load_start_date,
    o.local_end_date,
    d.local_arrival_date,
    p.freight,
    d.actual_gate_entry_date,
    m.shipm_start,
    m.shipment_end,
    mgr.sales_manager
    from deliver d, ship_pickup_date o,  key_customer k,
    reel_date m, orders p, cust_knvp mgr
    where d.item = m.order_item
    and d.sold_to = k.sold_to
    and d.shipment_number = o.ship_id
    and d.sales_doc = p.sales_doc
    and d.item = p.item
    and d.sold_to = mgr.customer
    and p.sales_district = mgr.sales_district
    and d.sales_office = mgr.sales_office
    and d.sales_rep = mgr.sales_rep

or more clearly with modern join syntax:

select 
    d.gate_entry_date,
    k.account_exec_name,
    o.load_start_date,
    o.local_end_date,
    d.local_arrival_date,
    p.freight,
    d.actual_gate_entry_date,
    m.shipm_start,
    m.shipment_end,
    mgr.sales_manager
    from deliver d
    join ship_pickup_date o on o.ship_id = d.shipment_number
    join key_customer k on k.sold_to = d.sold_to
    join reel_date m on m.order_item = d.item
    join orders p on p.sales_doc = d.sales_doc
      and p.item = d.item
    join cust_knvp mgr on mgr.customer = d.sold_to
      and mgr.sales_district = p.sales_district
      and mgr.sales_office = d.sales_office
      and mgr.sales_rep = d.sales_rep
Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Thanks Alex. Great help. Just one question, I'm not seeing sales manager in the syntax you provided. Once you do that, I will run it in sql developer.
Greatly appreciate, it's coming through but results still not favorable. In the second subquery, I posted you will notice the DISTINCT for sold_to, sales_office, sales_district and sales_rep. without distinct on these results repeating. thanks
@user3557193 - I don't know your data model or your data so I have no idea why you have duplicates. An order with multiple deliveries? Multiple orders in one delivery? Something lower down? If you get complete duplicate rows in the result set then you can still use distinct immediate after select but that can be a symptom of a data model problem; if some of the column values are repeated that's probably expected. Either might still suggest a missing join condition or filter. We have no way to tell though.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.