Join to derived table fails

[Former Member]

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

[justin_willey]

Which version of SQL Anywhere are you using? (You can get the full build number with

select @@version