CODEWITHSUNDEEP
oracle-databasesubquery
Sandeep-J
Sandeep-J

Reputation: 48

Need to select column from subquery into main query

I have a query like below - table names etc. changed for keeping the actual data private

SELECT inv.*,TRUNC(sysdate)
FROM Invoice inv
WHERE (inv.carrier,inv.pro,inv.ndate) IN
(
    SELECT carrier,pro,n_dt FROM Order where TRUNC(Order.cr_dt) = TRUNC(sysdate)
)

I am selecting records from Invoice based on Order. i.e. all records from Invoice which are common with order records for today, based on those 3 columns...

Now I want to select Order_Num from Order in my select query as well.. so that I can use the whole thing to insert it into totally seperate table, let's say orderedInvoices.

insert into orderedInvoices(seq_no,..same columns as Inv...,Cr_dt)
(
    SELECT **Order.Order_Num**, inv.*,TRUNC(sysdate)
    FROM Invoice inv
    WHERE (inv.carrier,inv.pro,inv.ndate) IN
    (
        SELECT carrier,pro,n_dt FROM Order where TRUNC(Order.cr_dt) = TRUNC(sysdate)
    )
)

?? - how to do I select that Order_Num in main query for each records of that sub query?

p.s. I understand that trunc(cr_dt) will not use index on cr_dt (if a index is there..) but I couldn't select records unless I omit the time part of it..:(

Upvotes: 1

Views: 732

Answers (1)

Ben
Ben

Reputation: 52923

If the table ORDER1 is unique on CARRIER, PRO and N_DT you can use a JOIN instead of IN to restrict your records, it'll also enable you to select whatever data you want from either table:

select order.order_num, inv.*, trunc(sysdate)
  from Invoice inv
  join order ord
    on inv.carrier = ord.carrier
   and inv.pro = ord.pro
   and inv.ndate = ord.n_dt
 where trunc(order.cr_dt) = trunc(sysdate)

If it's not unique then you have to use DISTINCT to deduplicate your record set.

Though using TRUNC() on CR_DT will not use an index on that column you can use a functional index on this if you do need an index.

create index i_order_trunc_cr_dt on order (trunc(cr_dt));

1. This is a really bad name for a table as it's a keyword, consider using ORDERS instead.

Upvotes: 1

Related Questions