Combine 2 queries into 1 output

• turkey-500673

  Mr or Mrs. 500

  Points: 516

  More actions

  September 14, 2012 at 11:00 am

  #262004

  I have 2 queries and I need to add them together to get 1 row of data for each invoice_no, order_no combination.

  First query is

  select invoice_line.order_no, invoice_no,

  sum(invoice_line.extended_price) as 'Extended Price',

  sum(invoice_line.commission_cost) as comm_cost_invline,

  sum(oe_line.commission_cost/ oe_line.unit_size) as comm_cost_oeLine

  from invoice_line

  join oe_line on oe_line.order_no = invoice_line.order_no

  and oe_line.line_no = invoice_line.oe_line_number

  where invoice_no = '3101279' --used to check 1 invoice no

  group by invoice_no, invoice_line.order_no

  order by invoice_line.order_no

  It returns

  order_noinvoice_noExtended Pricecomm_cost_invlinecomm_cost_oeLine

  1100056310127963.490048.29582138938.8900000

  Second query is:

  select invoice_hdr.invoice_no, invoice_hdr.order_no, freight_out,invoice_hdr_salesrep.salesrep_id from invoice_hdr --and others

  join invoice_hdr_salesrep on invoice_hdr_salesrep.invoice_number = invoice_hdr.invoice_no

  join freight_code on freight_code.freight_code_uid = invoice_hdr.freight_code_uid

  join oe_pick_ticket on oe_pick_ticket.order_no = invoice_hdr.order_no

  and oe_pick_ticket.invoice_no = invoice_hdr.invoice_no

  and freight_out > 0.00

  where freight_cd = 'PREPAY-CEP'

  and invoice_hdr.date_paid between '2012-01-01' and '2012-09-30'

  and invoice_hdr_salesrep.salesrep_id = 1023

  and invoice_hdr.invoice_no = '3101279'

  order by invoice_hdr.invoice_no

  It returns

  invoice_noorder_nofreight_outsalesrep_id

  3101279110005610.84001023

  What I need is:

  invoice_no order_no freight_out salesrep_id Extended Price comm_cost_invline comm_cost_oeLine

  3101279 1100056 10.8400 1023 63.4900 48.295821389 38.8900000

  For the life of me I can't figure this out. Can anybody help?

  Thanks a lot, I really appreciate it.

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  September 14, 2012 at 11:13 am

  #1538044

  Have you tried joining the INVOICE_LINE into your second query and add the sum(..) tot he select?

• turkey-500673

  Mr or Mrs. 500

  Points: 516

  More actions

  September 14, 2012 at 2:51 pm

  #1538170

  mmm, yes that seemed to work. letting user test it now. thank you.