Union and Right Join!!! Help

[giggles7840]

I have used union and i have used right joins, but i have not used both together before.

in my code, the union works fine, but as soon as the right join is included, the records come back all screwy. There are no errors, i just wind up with the correct number of records, but with a bunnch of NULLS.

Please help!! This was someone else code and i dont know how to fix it!

SELECT style AS PART,
whse AS TWHSE,
skid_id AS SKID_ID,
sku_desc AS SKU_DESC,
batch_nbr AS BATCH_NBR,
qty AS QTY,
cntry_of_orgn AS COUNTRY,
L.dsp_locn AS LOCATION,
units_case AS UNITS_CASE,
case_pallet AS CASE_PALLET
FROM
PkMS_nc.dbo.LOCN_HDR L

RIGHT JOIN

(/**** Start *** Union of all PkMS data ****/


-- CASE
SELECT IMA.style AS style,
CH.whse AS TWHSE,
CH.plt_id AS skid_id,
IMA.sku_desc AS sku_desc,
CD.batch_nbr AS batch_nbr,
SUM(CD.actl_qty) AS qty,
cntry_of_orgn AS cntry_of_orgn,
CH.locn_id AS location_id,
IMA.STD_CASE_QTY AS units_case,
COUNT(CD.case_nbr) AS case_pallet
FROM PkMS_nc.dbo.case_hdr CH
JOIN PkMS_nc.dbo.case_dtl CD
ON CH.case_nbr = CD.case_nbr
JOIN PkMS_nc.dbo.item_master IMA
ON IMA.sku_id = CD.sku_id
WHERE CH.whse = 'nc'
AND CD.batch_nbr IS NOT NULL
AND CH.stat_code < 90 -- UnAllocable case locks
GROUP BY CH.whse, IMA.style, CH.plt_id, IMA.sku_desc,
CD.batch_nbr, CD.cntry_of_orgn,
CH.locn_id, IMA.STD_CASE_QTY

UNION

-- CARTON_HDR
SELECT IMB.style AS style,
RH.whse AS TWHSE,
RH.plt_id AS skid_id,
IMB.sku_desc AS sku_desc,
RD.batch_nbr AS batch_nbr,
SUM(RD.units_pakd) AS qty,
cntry_of_orgn AS cntry_of_orgn,
RH.curr_locn_id AS location_id,
IMB.STD_CASE_QTY AS units_case,
COUNT(RD.carton_nbr) AS case_pallet
FROM PkMS_nc.dbo.carton_hdr RH
JOIN PkMS_nc.dbo.carton_dtl RD
ON RH.carton_nbr = RD.carton_nbr
JOIN PkMS_nc.dbo.item_master IMB
ON RD.sku_id = IMB.sku_id
WHERE RD.batch_nbr IS NOT NULL
AND RH.whse = 'nc'
AND RH.stat_code < 90
GROUP BY RH.whse, IMB.style, RH.plt_id, IMB.sku_desc,
RD.batch_nbr, RD.cntry_of_orgn,
RH.curr_locn_id, IMB.STD_CASE_QTY

UNION

-- TRANS_INVN
SELECT IMC.style AS style,
TINV.whse AS Twhse,
Null AS skid_id,
IMC.sku_desc AS sku_desc,
TINV.batch_nbr AS batch_nbr,
SUM(tinv.actl_invn_units) AS qty,
TINV.cntry_of_orgn AS cntry_of_orgn,
TINV.locn_id AS location_id,
0 AS units_case,
0 AS case_pallet
FROM PkMS_nc.dbo.trans_invn TINV
JOIN PkMS_nc.dbo.item_master IMC
ON IMC.sku_id = tinv.sku_id
WHERE TINV.batch_nbr IS NOT NULL
AND TINV.whse = 'nc'
GROUP BY TINV.whse, IMC.style, IMC.sku_id, IMC.sku_desc,
TINV.batch_nbr, TINV.cntry_of_orgn, TINV.locn_id) AS Q
/**** End *** Union of all PkMS data ****/

ON Q.location_id = L.locn_id ORDER BY style, location

 

[tlbroadbent]

When you use OUTER JOINs such as RIGHT SQL returns all records from the RIGHT side of the query and only matching records from the LEFT side. Thus, if the query finds rows in the RIGHT side set that don't exist in the LEFT side set, the columns selected from the LEFT side set will not have a value and will be NULL. YOu will always get NULLs returned unless all rows match. If you only want the rows that match remove the RIGHT from the JOIN to make the JOIN an INNER JOIN. Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.

 

[topher0303]

Are you getting nulls in all columns or just the LOCATION column?

 

[giggles7840]

its every other column. whse for instance. if i run all the queries separatly, there are values in that column, no nulls but when i put them together, it puts nulls in.

If i change the right join to a inner join, then it drops the records that i need.

 

[topher0303]

Are the nulls only in the dropped records?

Are there nulls in the whse column of any of the base tables: case_hdr, carton_hdr, or trans_invn?

If you run everything between the start and end comments of your sql (just the union selects) do you get nulls?

 

[giggles7840]

no nulls when i run just the unions. it doesnt break til the right join is included.

 

[tlbroadbent]

I don't see any reason you should see nulls the columns, particularly whse when each query in th UNION selects a specific whse value ('nc'). Convert the RIGHT JOIN to a LEFT JOIN and test it to see if this makes any difference. Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.

 

[topher0303]

Does the PkMS_nc.dbo.LOCN_HDR table have a whse column in it?

Try placing Q. in front of all the columns in the first select statement prior to the right join except for the L.dsp_locn. Typically, in SQL Server, I have always received an error when sources joined had the same column name. See if this change makes any difference. Other than possibly this, I cannot see anything wrong with the sql.

Chris.