0

With the code below I was originally attempting to not use sub queries to eliminate "duplicate data" (its technically not duplicate data since each order has several lines of data where prices and weights are different but I wanted to consolidate my results to where I only get distinct order #'s) But with having no luck of only using join conditions and SUM/GROUP BY. I adopted in my first join a sub query to have distinct orders returned with the fields "price" and "shipped". And that worked accordingly and I received the orders I needed. But with utilizing the whole script I realized "carton_no" was causing "duplicate" results being returned I so attempted to add another sub query but I seem to be receiving the error:

Msg 107, Level 16, State 2, Line 17 The column prefix 'TCT' does not match with a table name or alias name used in the query.

Block of code providing errors:


LEFT OUTER JOIN 
  (
       SELECT TRCB.order_no, TRCB.Skid_Height_Inches, TCT.carton_no FROM tdc_revshelf_carton_box TRCB WITH (NOLOCK) 
       INNER JOIN tdc_carton_tx TCT 
       ON TRCB.order_no = TCT.order_no
       AND TRCB.order_ext = TCT.order_ext
       AND TRCB.carton_no = TCT.carton_no   
       GROUP BY TRCB.order_no, TRCB.Skid_Height_Inches, TCT.carton_no
   ) TRCB ON  TRCB.order_no = TCT.tdc_carton_tx 

DECLARE @location VARCHAR(10)
DECLARE @dateshipped DATETIME 
SET @location = 'RAS 2'
SET @DateShipped = DATEADD(day, 0, DATEDIFF(day, 0, GETDATE())-3)


SELECT  

      SUM (L.Shipped ) AS Shipped
    , SUM (L.Price ) AS Price
    , O.Order_no
    , O.Ext
    , O.Cust_Code 
    , O.Ship_To_Name 
    , O.Freight_Allow_Type 
    , TCT.Carton_no 
    , TPM.dim_ext_x  
    , TPM.dim_ext_y 
    , TRCB.Number_of_Boxes 
    , MAX(TRCB.Skid_Height_Inches )
    , MAX(O.Routing) AS 'Routing'  
    , MAX(O.User_def_fld9) AS 'bol_no'  
    , MAX(Ship_to_name) AS Ship_to_name_1 
    , MAX(Ship_to_add_3) AS Ship_to_add_3 
    , MAX(L.[location]) AS 'Location' 
    , MAX(O.cust_code ) AS 'Cust_code_1' 
    , MAX(O.user_def_fld10) AS 'Skids' 
    , MAX(O.user_def_fld12) AS 'Weight' 
    , ISNULL(MAX(BH.est_freight_cost),0) AS 'BOL_est_Freight'
    , TCT.[weight] + TPM.[weight] AS 'Total_Pallet_Weight'




FROM orders O WITH (NOLOCK)
INNER JOIN  
    (
       SELECT  o.order_no, L.shipped, L.price FROM orders O WITH (NOLOCK) INNER JOIN Ord_list L ON O.order_no = L.order_no
        WHERE O.routing NOT LIKE 'FEDX%' 
        AND O.routing NOT IN ('UPS', 'UPS 1', 'UPS 2', 'UPS 3') 
        AND ISNULL(O.void,'') <> 'V'
        AND L.location = @location
        GROUP BY O.order_no, L.shipped, L.price
   )
   L ON O.order_no = L.order_no
JOIN xfers X
     ON O.order_no = X.xfer_no
JOIN xfer_list XL
    ON  X.xfer_no = XL.xfer_no
--INNER JOIN ord_list L WITH (NOLOCK)
--  ON  O.order_no = L.order_no
--     AND O.ext = L.order_ext
INNER JOIN arcust C WITH (NOLOCK)
    ON O.cust_code = C.customer_code 
LEFT OUTER JOIN armaster A WITH (NOLOCK)
    ON A.customer_code = O.cust_code  
       AND A.ship_to_code = O.ship_to
       AND A.location_code = O.location  
LEFT OUTER JOIN tdc_pkg_master TPM WITH (NOLOCK)
     ON TCT.carton_type=TPM.pkg_code
INNER JOIN tdc_carton_tx TCT WITH (NOLOCK)
     ON TPM.pkg_code = TCT.carton_type
LEFT OUTER JOIN 
  (
       SELECT TRCB.order_no, TRCB.Skid_Height_Inches, TCT.carton_no FROM tdc_revshelf_carton_box TRCB WITH (NOLOCK) 
       INNER JOIN tdc_carton_tx TCT 
       ON TRCB.order_no = TCT.order_no
       AND TRCB.order_ext = TCT.order_ext
       AND TRCB.carton_no = TCT.carton_no   
       GROUP BY TRCB.order_no, TRCB.Skid_Height_Inches, TCT.carton_no
   ) TRCB
        ON  TRCB.order_no = TCT.tdc_carton_tx 
LEFT OUTER JOIN ras_bol_details BD WITH (NOLOCK)
    ON X.xfer_no = BD.bl_src_no
       AND X.xfer_no = BD.bl_src_no AND BD.order_type IN ('T','S')
LEFT OUTER JOIN ras_bol_header BH WITH (NOLOCK)
    ON BD.bl_no = BH.bl_no
       AND O.order_no = BD.bl_src_no 
       AND O.ext = BD.bl_src_ext 

WHERE  DATEADD(day, 0, DATEDIFF(day, 0, O.date_shipped)) >= @dateshipped




GROUP BY

      O.order_no
    , O.ext
    , O.cust_code
    , O.ship_to_name 
    , O.freight_allow_type 
    , TCT.carton_no 
    , TCT.[weight]
    , TPM.[weight]
    , TPM.dim_ext_x  
    , TPM.dim_ext_y 
    , TRCB.number_of_boxes 
    , TRCB.skid_height_inches 
  • DISTINCT and GROUP BY? Why? Also, please don't use shorthand like dd. Your life won't change by spelling it out (day is just one extra character), and you can avoid a lot of problems (see here and here). – Aaron Bertrand May 15 '17 at 15:27
  • Right I never got around to changing the dd, and I know the GROUP BY is supposed to replace DISTINCT. But with including DISTINCT I was receiving the exact orders I needed vs just having the GROUP BY only. – tfenwick11 May 15 '17 at 15:43
  • @AaronBertrand my mistake, I receive the correct amount of orders with or without distinct. My apology must be going crazy this morning.. – tfenwick11 May 15 '17 at 15:48
  • I suspect (but am not sure) that in some way the problem lies in your using the TCT alias in both the main query and in a sub-query. I'd trying changing one or the other, just to make sure that the multiple references aren't confusing either the engine, or you! – RDFozz May 15 '17 at 18:35
0

This has been resolved, with restructuring the procedure and learning a few columns could be removed that were not needed in the report. I was able to move away from the second sub query. Thanks,

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.