on 2014 Aug 12 1:30 PM
When I join to a derived table, the join does not return any records. If I run the query for the derived table, I can manually verify that the join should return some records. I used ANSI syntax but I get the same results when using SQL 92 syntax.
SELECT DISTINCT A.order_nbr, D.mfg_item_nbr [MIN], D.min_length, D.max_length, ISNULL(F.fiber_spec_name, FS.fiber_spec_name) fiber_spec_name, A.notes, rank = isnull(A.rank,0), ShipInfo.schd_ship_date, ShipInfo.CountDistinctShipToDates , H.current_ship_schedule , CASE WHEN H.order_type = 'EX' THEN 'Y' ELSE 'N' END [Export] , OrderInfo.cust_ordered_quantity, OrderInfo.mfg_quantity , ISNULL(AttachedInfo.attached_qty, 0) attached_qty, ISNULL(AttachedInfo.nbr_boxes, 0) nbr_boxes , ISNULL(BoxedInfo.BoxedQty, 0) BoxedQty , ISNULL(H.cut_stock, 'N') ready_for_dc FROM devpmsdb..ord_order H INNER JOIN devpmsdb..ord_item D ON H.order_nbr = D.order_nbr INNER JOIN devpmsdb..fs_attach A ON H.order_nbr = A.order_nbr INNER JOIN (SELECT A1.order_nbr, SUM(ISNULL(cust_ordered_quantity, 0)) cust_ordered_quantity, SUM(ISNULL(mfg_quantity, 0)) mfg_quantity FROM devpmsdb..ord_item A1 WHERE A1.order_nbr IN (SELECT order_nbr FROM devpmsdb..fs_attach WHERE order_nbr <> 'BULLETIN' ) GROUP BY A1.order_nbr ) OrderInfo ON H.order_nbr = OrderInfo.order_nbr LEFT JOIN devpmsdb..ps_fiber F ON D.ps_matl_id = F.ps_matl_id LEFT JOIN devpmsdb..ps_fs_set FS ON D.ps_matl_id = FS.ps_matl_id LEFT JOIN (SELECT A1.order_nbr, COUNT(DISTINCT schd_ship_date) CountDistinctShipToDates, MIN(schd_ship_date) schd_ship_date FROM devpmsdb..ord_item A1 WHERE A1.order_nbr IN (SELECT order_nbr FROM devpmsdb..fs_attach WHERE order_nbr <> 'BULLETIN' ) AND schd_ship_date >= CONVERT(SMALLDATETIME, CONVERT(VARCHAR(10), getdate(), 101)) GROUP BY A1.order_nbr ) ShipInfo ON H.order_nbr = ShipInfo.order_nbr LEFT JOIN (SELECT A1.mfg_item_nbr, attached_qty = isnull(sum(A1.plnd_length),0), nbr_boxes = count(1) FROM devpmsdb..pms_item_product A1 WHERE A1.mfg_item_nbr IN (SELECT D2.mfg_item_nbr FROM devpmsdb..ord_item D2, devpmsdb..fs_attach A2 WHERE D2.order_nbr = A2.order_nbr AND A2.order_nbr <> 'BULLETIN' ) AND A1.product_id LIKE '[GB]%' AND A1.plnd_length > 0 GROUP BY mfg_item_nbr ) AttachedInfo ON D.mfg_item_nbr = AttachedInfo.mfg_item_nbr LEFT JOIN (SELECT A1.mfg_item_nbr, BoxedQty = isnull(sum(A1.plnd_length),0), CountBoxes = count(1) FROM devpmsdb..pms_item_product A1 WHERE A1.mfg_item_nbr IN (SELECT D2.mfg_item_nbr FROM devpmsdb..ord_item D2, devpmsdb..fs_attach A2 WHERE D2.order_nbr = A2.order_nbr AND A2.order_nbr <> 'BULLETIN' ) AND A1.schd_item_nbr >= 1 AND A1.product_id LIKE '[GB]%' AND A1.plnd_length > 0 AND NOT EXISTS (SELECT 1 FROM testcabledb..fs_master A2 WHERE A2.lot_no = A1.product_id AND label_status <> 'P' ) GROUP BY mfg_item_nbr ) BoxedInfo ON D.mfg_item_nbr = BoxedInfo.mfg_item_nbr WHERE D.status IN ('VIRG', 'SCHD', 'WIP', 'DELV', 'COMP') AND A.order_nbr <> 'BULLETIN' AND ISNULL(LEN(LTRIM(RTRIM(H.spec_req))), 0) = 0 ORDER BY rank, ProjectName, A.order_nbr, D.mfg_item_nbr, D.schd_ship_date
Request clarification before answering.
User | Count |
---|---|
75 | |
30 | |
9 | |
8 | |
7 | |
6 | |
6 | |
5 | |
5 | |
5 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.