The forum will be down for maintenance over the weekend of August 18-20, 2017. The forum will be shut down on the evening (EDT) of Friday, August 18. Downtime is unknown but may be up to two days. The forum will be restarted as soon as maintenance is complete.

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

asked 12 Aug '14, 12:30

jhk6z's gravatar image

jhk6z
1111
accept rate: 0%

edited 12 Aug '14, 12:38

Justin%20Willey's gravatar image

Justin Willey
6.8k110144212

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

select @@version

(12 Aug '14, 12:42) Justin Willey
Be the first one to answer this question!
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×6

question asked: 12 Aug '14, 12:30

question was seen: 403 times

last updated: 12 Aug '14, 12:42