By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,364 Members | 1,147 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,364 IT Pros & Developers. It's quick & easy.

Query not getting all data

P: 1
I have a query built to extract data. No errors when it runs however it gets some of the data but not all of the data. Below is an example of the query and the results. Here is an example... Each job has a PO and multiple lines it might get line 2,3,5,7 but miss 1,4,6,8


Here is the example. This is in sql 2005

--select * from p21_order_view
--
use p21
select p21_order_view.order_no, ship2_name, order_date, po_no, line_no, disposition, item_id, qty_ordered, unit_price, extended_price, sales_cost,
pick_ticket_no, print_date,tracking_no, ship_quantity, invoice_no, invoice_date
from p21_order_view
left outer join
(select p21_view_oe_pick_ticket.pick_ticket_no, p21_view_oe_pick_ticket.order_no, oe_line_no, p21_view_oe_pick_ticket.print_date,
tracking_no, p21_view_oe_pick_ticket.invoice_no, invoice_date, ship_quantity
from p21_view_oe_pick_ticket
inner join p21_view_oe_pick_ticket_detail on p21_view_oe_pick_ticket.pick_ticket_no = p21_view_oe_pick_ticket_detail.pick_ticket_no
left outer join p21_view_invoice_hdr on p21_view_oe_pick_ticket.invoice_no = p21_view_invoice_hdr.invoice_no
where tracking_no not like '%CANCELLED%') as pick_ticket_info
on p21_order_view.order_no = pick_ticket_info.order_no and p21_order_view.line_no = pick_ticket_info.oe_line_no
where hdr_delete_flag = 'N' and hdr_cancel_flag = 'N' and line_cancel_flag = 'N' and disposition <> 'C'
and order_date >= '01/01/08' order by order_date desc, p21_order_view.order_no, line_no
--
--sp_help oe_pick_ticket_detail
--
--select p21_view_oe_pick_ticket.pick_ticket_no, p21_view_oe_pick_ticket.order_no, oe_line_no, p21_view_oe_pick_ticket.print_date,
--tracking_no, p21_view_oe_pick_ticket.invoice_no, invoice_date
--from p21_view_oe_pick_ticket
--inner join p21_view_oe_pick_ticket_detail on p21_view_oe_pick_ticket.pick_ticket_no = p21_view_oe_pick_ticket_detail.pick_ticket_no
--left outer join p21_view_invoice_hdr on p21_view_oe_pick_ticket.invoice_no = p21_view_invoice_hdr.invoice_no
--
--select * from p21_view_oe_pick_ticket_detail
--select * from p21_view_oe_pick_ticket
--sp_help oe_pick_ticket






This is an example of what is happening


It's hard to see but this example is missing line 2,3,6. I hightlighted the line numbers in red. The lines are in the system though


1001199 WRIGLEY YORKVILLE 2008-04-08 11:35:11.000 4500666698 1 B 2108423 2.000000000 0.000000000 0.0000 622.600000000 NULL NULL NULL NULL
1001199 WRIGLEY YORKVILLE 2008-04-08 11:35:11.000 4500666698 4 B 2032416 3.000000000 386.200000000 1158.6000 309.000000000 NULL NULL NULL NULL
1001199 WRIGLEY YORKVILLE 2008-04-08 11:35:11.000 4500666698 5 B 2031692 8.000000000 48.130000000 385.0400 36.800664300 NULL NULL NULL NULL
1001199 WRIGLEY YORKVILLE 2008-04-08 11:35:11.000 4500666698 7 B 2029842 1.000000000 0.000000000 0.0000 175.000000000 NULL NULL NULL NULL
1001199 WRIGLEY YORKVILLE 2008-04-08 11:35:11.000 4500666698 8 B 2008349 4.000000000 0.000000000 0.0000 95.000000000 NULL NULL NULL NULL
Apr 9 '08 #1
Share this Question
Share on Google+
1 Reply


P: 26
The post is very poorly formatted to make it readable. Also, do you really need to post the commented lines? The highlighted text has not come through thus making the post incomplete. Please reformat it and make easy for others to have a look and make a suggestion.
Thanks.
Apr 9 '08 #2

Post your reply

Sign in to post your reply or Sign up for a free account.