SQL Subquery assistance
I have the following query that pulls a single row of data based on a set of parameters. See attached example 1
I need to alter the above query so that it selects the details of the above order using something similar as below.
The link is as follows CI.style = SV.rng.style AND CI.lbl_code =SV.rng_lbl AND CI.color_code = SV.rng_color
Output of query 2
rng_style Size_Desc rng_total_qty size_qty
1160000R 18MO 40 3
1160000R 2 40 4
1160000R 3 40 5
1160000R 4 40 5
1160000R 5 40 7
1160000R 6 40 5
1160000R 7 40 5
1160000R 8 40 6
Example-1.xlsx
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
DECLARE @Searchby VARCHAR(1);
DECLARE @Number INT;
SET @Searchby = 'I';
SET @Number = '603200';
SELECT CI.pick_num ,
CI.style ,
CI.color_code ,
CI.lbl_code ,
CI.dimension ,
CI.price ,
CI.total_qty ,
CI.ship_name ,
CI.uom ,
CI.style_name ,
CI.cont_desc ,
CI.group_name ,
CI.cnty_name ,
CI.customer ,
CI.store ,
CI.ship_dc ,
CI.ord_num ,
CI.center_code ,
CI.po_num ,
ST.ShipTo_Name ,
ST.ShipTo_Address1 ,
ST.ShipTo_Address2 ,
ST.ShipTo_City ,
ST.ShipTo_State ,
ST.ShipTo_ZipCode ,
ST.ShipTo_Country_Name ,
CI.inv_num ,
CASE WHEN ( @Searchby = 'O' ) THEN ( CI.ord_num )
WHEN ( @Searchby = 'P' ) THEN ( CI.pick_num )
WHEN ( @Searchby = 'I' ) THEN ( CI.inv_num )
WHEN ( @Searchby = 'C' ) THEN ( CI.po_num )
END AS SearchNumber ,
SC.HS_NUM ,
SC.HS_NUM2 ,
SC.HS_NUM3 ,
CI.curr_code
FROM [KLL Commercial Invoice] AS CI
INNER JOIN [KLL Ship To Address Resolution] AS ST ON CI.pick_num = ST.pick_num
AND @Searchby = 'P'
OR CI.ord_num = ST.ord_num
AND @Searchby IN (
'O', 'C' )
OR CI.inv_num = ST.inv_num
AND @Searchby = 'I'
INNER JOIN zzxscolr AS SC ON CI.style = SC.style
AND CI.color_code = SC.color_code
AND CI.lbl_code = SC.lbl_code
AND CI.dimension = SC.dimension
AND CI.division = SC.division
WHERE ( CI.pick_num IN ( @Number ) )
AND ( @Searchby = 'P' )
OR ( @Searchby = 'O' )
AND ( CI.ord_num IN ( @Number ) )
OR ( @Searchby = 'I' )
AND ( CI.inv_num IN ( @Number ) );I need to alter the above query so that it selects the details of the above order using something similar as below.
The link is as follows CI.style = SV.rng.style AND CI.lbl_code =SV.rng_lbl AND CI.color_code = SV.rng_color
SELECT SV.rng_style ,
SV.Size_Desc ,
MAX(SV.rng_total_qty) AS rng_total_qty ,
SUM(SV.Size_Qty) AS size_qty
FROM [KLL Range Style Breakdown by Size Vertical] AS SV
WHERE SV.rng_style = '1160000r'
AND SV.rng_lbl = '66can'
AND SV.rng_color = 'asst'
AND SV.Size_Qty <> 0
GROUP BY SV.rng_style ,
SV.Size_Desc; Output of query 2
rng_style Size_Desc rng_total_qty size_qty
1160000R 18MO 40 3
1160000R 2 40 4
1160000R 3 40 5
1160000R 4 40 5
1160000R 5 40 7
1160000R 6 40 5
1160000R 7 40 5
1160000R 8 40 6
Example-1.xlsx
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Okay I figured out it was just a typo for the above however I am now seeing this at the end
Msg 4104, Level 16, State 1, Line 82
The multi-part identifier "SV.rng_style" could not be bound.
FROM CTE_Header_Detail CHD
INNER JOIN CTE_Aggregaed_Data CAD ON CHD.style = SV.rng_style;
Msg 4104, Level 16, State 1, Line 82
The multi-part identifier "SV.rng_style" could not be bound.
FROM CTE_Header_Detail CHD
INNER JOIN CTE_Aggregaed_Data CAD ON CHD.style = SV.rng_style;
ASKER
Thank you
ASKER
Invalid object name 'CTE_Detail_Aggregaed_Data
It's the last part of the SQL it looks like.
SELECT CHD.* ,
CAD.*
FROM CTE_Header_Detail CHD
INNER JOIN CTE_Detail_Aggregaed_Data CAD ON CHD.style = SV.rng_style;
Open in new window