[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 308
  • Last Modified:

Stored procedure help...

I am working on a stored procedure to pull data out of our WMS system to create BOLs.  So far I have two queries that will pull all the data I need and when I run each one separately I get the correct data.  The trouble I am having is putting the two together into one procedure.  Since I am fairly new to writing stored procedures I need help.  I have tried different things like Union, Union All, also creating a temp table and doing an insert into, things that I have used in other procedures and nothing has worked.  I am at a loss and need help.  I have listed each query in the code section.  Any help you can offer would be greatly appreciated.
--Code for first query:
 
With BOLUnHdr(PktCtrlNbr, Shipto, CustName, Address1, Address2, Address3, City, ShipToSt, ZipCode, Country, Routing, Terms, TrailerNbr, ProNbr, FrtTrms, SealNbr, CrtnNbr)
As
(
SELECT
		PH.PKT_CTRL_NBR As PktCtrlNbr,
		PH.SHIPTO AS ShipTo,
		PH.SHIPTO_NAME AS CustName,
		PH.SHIPTO_ADDR_1 AS Address1,
		PH.SHIPTO_ADDR_2 AS Address2,
		PH.SHIPTO_ADDR_3 AS Address3,
		PH.SHIPTO_CITY AS City,
		PH.SHIPTO_STATE AS ShipToSt,
		PH.SHIPTO_ZIP AS ZipCode,
		PH.SHIPTO_CNTRY AS Country,
		PH.RTE_ATTR AS Routing,
		PH.TERMS_DESC AS Terms,
		CH.TRLR_NBR As TrailerNbr,
		OS.PRO_NBR As ProNbr,
		OS.FREIGHT_TERMS As FrtTrms,
		OL.SEAL_NBR As SealNbr,
		CH.CARTON_NBR As CrtnHdr
		
FROM
		PKT_HDR As PH INNER JOIN
		CARTON_HDR As CH ON PH.PKT_CTRL_NBR = CH.PKT_CTRL_NBR INNER JOIN
		OUTBD_LOAD As OL On CH.LOAD_NBR = OL.LOAD_NBR Inner Join
		OUTBD_STOP As OS On OL.LOAD_NBR = OS.LOAD_NBR And
							CH.SHPMT_NBR = OS.SHPMT_NBR
 
Where
		CH.LOAD_NBR = '000000024'
), BOLUnHdrData(PktCtrlNbr, Shipto, CustName, Address1, Address2, Address3, City, ShipToSt, ZipCode, Country, Routing, Terms, TrailerNbr, ProNbr, FrtTrms, SealNbr, PlltCnt)
As
(
Select
		PktCtrlNbr,
		ShipTo,
		CustName,
		Address1,
		Address2,
		Address3,
		City,
		ShipToSt,
		ZipCode,
		Country,
		Routing,
		Terms,
		TrailerNbr,
		ProNbr,
		FrtTrms,
		SealNbr,
		Count(Distinct(CrtnNbr)) As PlltCnt
 
From
		BOLUnHdr
 
Group By
		PktCtrlNbr,
		ShipTo,
		CustName,
		Address1,
		Address2,
		Address3,
		City,
		ShipToSt,
		ZipCode,
		Country,
		Routing,
		Terms,
		TrailerNbr,
		ProNbr,
		FrtTrms,
		SealNbr
), BOLUnData(PktCtrlNbr, Shipto, CustName, Address1, Address2, Address3, City, ShipToSt, ZipCode, Country, Routing, Terms, TrailerNbr, ProNbr, SealNbr, PlltCnt, SkuId, PackQty, Weight, FrtClass, SortOrder, CollectFlag, LocnClass, CrtnNbr)
As
(
SELECT
		PktCtrlNbr,
		Shipto,
		CustName,
		Address1,
		Address2,
		Address3,
		City,
		ShipToSt,
		ZipCode,
		Country,
		Routing,
		Terms,
		TrailerNbr,
		ProNbr,
		SealNbr,
		PlltCnt,
		'Unit' As SkuId,
		1 As PackQty,
		Case
			When LH.LOCN_CLASS = 'A' And IM.STD_PACK_QTY > 1 Then Round((((PD.UNITS_PAKD % IM.STD_PACK_QTY) / IM.STD_PACK_QTY) * IM.STD_PACK_WT),4)
			When LH.LOCN_CLASS = 'A' And IM.STD_PACK_QTY = 1 Then PD.UNITS_PAKD * IM.STD_PACK_WT
			End As Weight,
		Case
			When FrtTrms = 0 Then 'CARTONS FILTERS, OIL, AIR, WATER & PARTS'
			When FrtTrms <> 0 And LH.LOCN_CLASS = 'A' Then 'CARTONS FILTERS, FUEL, WATER, "COLUMN 70"'
			When FrtTrms <> 0 And IM.FRT_CLASS = 65 Then 'CARTONS FILTERS, OIL, SPIN-ON TYPE, "COLUMN 65"'
			When FrtTrms <> 0 And IM.FRT_CLASS = 70 Then 'CARTONS FILTERS, FUEL, WATER, "COLUMN 70"'
			When FrtTrms <> 0 And IM.FRT_CLASS = 85 Then 'CARTONS FILTERS, OIL, "COLUMN 85"'
			When FrtTrms <> 0 And IM.FRT_CLASS > 85 Then 'CARTONS AIR CLEANER CARTRIDGES "IN EXCESS 5 LBS. PCF."'
		End As FrtClass,
		Case
			When FrtTrms = 0 Then 65
			When FrtTrms <> 0 And LH.LOCN_CLASS = 'A' Then 70
			When FrtTrms <> 0 And IM.FRT_CLASS = 65 Then 65
			When FrtTrms <> 0 And IM.FRT_CLASS = 70 Then 70
			When FrtTrms <> 0 And IM.FRT_CLASS = 85 Then 85
			When FrtTrms <> 0 And IM.FRT_CLASS > 85 Then 200
		End As SortOrder,
		Case
			When FrtTrms <> 0 Then 1
			Else
				0
			End As CollectFlag,
		LH.LOCN_CLASS As LocnClass,
		CH.CARTON_NBR As CrtnNbr
		
FROM
		BOLUnHdrData Inner Join
		CARTON_DTL As CD Inner Join
		CARTON_HDR As CH On CD.CARTON_NBR = CH.CARTON_NBR On
							PktCtrlNbr = CH.PKT_CTRL_NBR Inner Join
		PKT_DTL As PD On PktCtrlNbr = PD.PKT_CTRL_NBR And
						 CD.PKT_CTRL_NBR = PD.PKT_CTRL_NBR And
						 CD.PKT_SEQ_NBR = PD.PKT_SEQ_NBR Inner Join
		LOCN_HDR As LH On CH.PICK_LOCN_ID = LH.LOCN_ID Inner Join
		ITEM_MASTER As IM On PD.SKU_ID = IM.SKU_ID
 
WHERE
		{ fn LENGTH(CD.CARTON_NBR) } = 8 And
		LH.LOCN_CLASS = 'A'
)
Select
		PktCtrlNbr As PktCtrlNbr,
		Shipto As Shipto,
		CustName As CustName,
		Address1 As Address1,
		Address2 As Address2,
		Address3 As Address3,
		City As City,
		ShipToSt As ShipToSt,
		ZipCode As ZipCode,
		Country As Country,
		Routing As Routing,
		Terms As Terms,
		TrailerNbr As TrailerNbr,
		ProNbr As ProNbr,
		SealNbr As SealNbr,
		Max(PlltCnt) As PlltCnt,
		SkuId As Skuid,
		PackQty As PackQty,
		Sum(Weight) As Weight,
		FrtClass As FrtClass,
		SortOrder As SortOrder,
		CollectFlag As CollectFlag, 
		LocnClass As LocnClass,
		Count(Distinct(CrtnNbr)) As PackCnt
 
From
		BOLUnData
 
Group By
		PktCtrlNbr,
		Shipto,
		CustName,
		Address1,
		Address2,
		Address3,
		City,
		ShipToSt,
		ZipCode,
		Country,
		Routing,
		Terms,
		TrailerNbr,
		ProNbr,
		SealNbr,
		SkuId,
		PackQty,
		FrtClass,
		SortOrder,
		CollectFlag, 
		LocnClass
 
--End of first query
 
--Code for second query
 
With BOLHdr(PktCtrlNbr, Shipto, CustName, Address1, Address2, Address3, City, ShipToSt, ZipCode, Country, Routing, Terms, TrailerNbr, ProNbr, FrtTrms, SealNbr, CrtnNbr)
As
(
SELECT
		PH.PKT_CTRL_NBR As PktCtrlNbr,
		PH.SHIPTO AS ShipTo,
		PH.SHIPTO_NAME AS CustName,
		PH.SHIPTO_ADDR_1 AS Address1,
		PH.SHIPTO_ADDR_2 AS Address2,
		PH.SHIPTO_ADDR_3 AS Address3,
		PH.SHIPTO_CITY AS City,
		PH.SHIPTO_STATE AS ShipToSt,
		PH.SHIPTO_ZIP AS ZipCode,
		PH.SHIPTO_CNTRY AS Country,
		PH.RTE_ATTR AS Routing,
		PH.TERMS_DESC AS Terms,
		CH.TRLR_NBR As TrailerNbr,
		OS.PRO_NBR As ProNbr,
		OS.FREIGHT_TERMS As FrtTrms,
		OL.SEAL_NBR As SealNbr,
		CH.CARTON_NBR As CrtnNbr
		
FROM
		PKT_HDR As PH INNER JOIN
		CARTON_HDR As CH ON PH.PKT_CTRL_NBR = CH.PKT_CTRL_NBR INNER JOIN
		OUTBD_LOAD As OL On CH.LOAD_NBR = OL.LOAD_NBR Inner Join
		OUTBD_STOP As OS On OL.LOAD_NBR = OS.LOAD_NBR And
							CH.SHPMT_NBR = OS.SHPMT_NBR
 
Where
		CH.LOAD_NBR = '000000024'
), BOLHdrData(PktCtrlNbr, Shipto, CustName, Address1, Address2, Address3, City, ShipToSt, ZipCode, Country, Routing, Terms, TrailerNbr, ProNbr, FrtTrms, SealNbr, PlltCnt)
As
(
Select
		PktCtrlNbr,
		ShipTo,
		CustName,
		Address1,
		Address2,
		Address3,
		City,
		ShipToSt,
		ZipCode,
		Country,
		Routing,
		Terms,
		TrailerNbr,
		ProNbr,
		FrtTrms,
		SealNbr,
		Count(Distinct(CrtnNbr)) As PlltCnt
 
From
		BOLHdr
 
Group By
		PktCtrlNbr,
		ShipTo,
		CustName,
		Address1,
		Address2,
		Address3,
		City,
		ShipToSt,
		ZipCode,
		Country,
		Routing,
		Terms,
		TrailerNbr,
		ProNbr,
		FrtTrms,
		SealNbr
), BOLData(PktCtrlNbr, Shipto, CustName, Address1, Address2, Address3, City, ShipToSt, ZipCode, Country, Routing, Terms, TrailerNbr, ProNbr, SealNbr, PlltCnt, SkuId, PackQty, Weight, FrtClass, SortOrder, CollectFlag, LocnClass, CrtnNbr)
As
(
SELECT
		PktCtrlNbr,
		Shipto,
		CustName,
		Address1,
		Address2,
		Address3,
		City,
		ShipToSt,
		ZipCode,
		Country,
		Routing,
		Terms,
		TrailerNbr,
		ProNbr,
		SealNbr,
		PlltCnt,
		CD.SKU_ID As SkuId,
		Case
			When LH.LOCN_CLASS = 'A' And IM.STD_PACK_QTY > 1 Then (PD.UNITS_PAKD % IM.STD_PACK_QTY)
			When LH.LOCN_CLASS = 'A' And IM.STD_PACK_QTY = 1 Then PD.UNITS_PAKD
			Else
				({ fn TRUNCATE ((PD.UNITS_PAKD / IM.STD_PACK_QTY),0) })
			End As PackQty,
		Case
			When LH.LOCN_CLASS = 'A' And IM.STD_PACK_QTY > 1 Then Round((((PD.UNITS_PAKD % IM.STD_PACK_QTY) / IM.STD_PACK_QTY) * IM.STD_PACK_WT),4)
			When LH.LOCN_CLASS = 'A' And IM.STD_PACK_QTY = 1 Then PD.UNITS_PAKD * IM.STD_PACK_WT
			Else
				({ fn TRUNCATE ((PD.UNITS_PAKD / IM.STD_PACK_QTY),0) }) * IM.STD_PACK_WT
			End As Weight,
		Case
			When FrtTrms = 0 Then 'CARTONS FILTERS, OIL, AIR, WATER & PARTS'
			When FrtTrms <> 0 And LH.LOCN_CLASS = 'A' Then 'CARTONS FILTERS, FUEL, WATER, "COLUMN 70"'
			When FrtTrms <> 0 And IM.FRT_CLASS = 65 Then 'CARTONS FILTERS, OIL, SPIN-ON TYPE, "COLUMN 65"'
			When FrtTrms <> 0 And IM.FRT_CLASS = 70 Then 'CARTONS FILTERS, FUEL, WATER, "COLUMN 70"'
			When FrtTrms <> 0 And IM.FRT_CLASS = 85 Then 'CARTONS FILTERS, OIL, "COLUMN 85"'
			When FrtTrms <> 0 And IM.FRT_CLASS > 85 Then 'CARTONS AIR CLEANER CARTRIDGES "IN EXCESS 5 LBS. PCF."'
		End As FrtClass,
		Case
			When FrtTrms = 0 Then 65
			When FrtTrms <> 0 And LH.LOCN_CLASS = 'A' Then 70
			When FrtTrms <> 0 And IM.FRT_CLASS = 65 Then 65
			When FrtTrms <> 0 And IM.FRT_CLASS = 70 Then 70
			When FrtTrms <> 0 And IM.FRT_CLASS = 85 Then 85
			When FrtTrms <> 0 And IM.FRT_CLASS > 85 Then 200
		End As SortOrder,
		Case
			When FrtTrms <> 0 Then 1
			Else
				0
			End As CollectFlag,
		LH.LOCN_CLASS As LocnClass,
		CH.CARTON_NBR As CrtnNbr
		
FROM
		BOLHdrData Inner Join
		CARTON_DTL As CD Inner Join
		CARTON_HDR As CH On CD.CARTON_NBR = CH.CARTON_NBR On
							PktCtrlNbr = CH.PKT_CTRL_NBR Inner Join
		PKT_DTL As PD On PktCtrlNbr = PD.PKT_CTRL_NBR And
						 CD.PKT_CTRL_NBR = PD.PKT_CTRL_NBR And
						 CD.PKT_SEQ_NBR = PD.PKT_SEQ_NBR Inner Join
		LOCN_HDR As LH On CH.PICK_LOCN_ID = LH.LOCN_ID Inner Join
		ITEM_MASTER As IM On PD.SKU_ID = IM.SKU_ID
 
WHERE
		{ fn LENGTH(CD.CARTON_NBR) } = 8 And
		LH.LOCN_CLASS <> 'A'
)
Select
		PktCtrlNbr As PktCtrlNbr,
		Shipto As Shipto,
		CustName As CustName,
		Address1 As Address1,
		Address2 As Address2,
		Address3 As Address3,
		City As City,
		ShipToSt As ShipToSt,
		ZipCode As ZipCode,
		Country As Country,
		Routing As Routing,
		Terms As Terms,
		TrailerNbr As TrailerNbr,
		ProNbr As ProNbr,
		SealNbr As SealNbr,
		Max(PlltCnt) As PlltCnt,
		SkuId As SkuId,
		PackQty As PackQty,
		Sum(Weight) As Weight,
		FrtClass As FrtClass,
		SortOrder As SortOrder,
		CollectFlag As CollectFlag, 
		LocnClass As LocnClass,
		Count(Distinct(CrtnNbr)) As PackCnt
 
From
		BOLData
 
Group By
		PktCtrlNbr,
		Shipto,
		CustName,
		Address1,
		Address2,
		Address3,
		City,
		ShipToSt,
		ZipCode,
		Country,
		Routing,
		Terms,
		TrailerNbr,
		ProNbr,
		SealNbr,
		SkuId,
		PackQty,
		FrtClass,
		SortOrder,
		CollectFlag, 
		LocnClass
 
--End of second query

Open in new window

0
Jeff Geiselman
Asked:
Jeff Geiselman
  • 2
  • 2
1 Solution
 
BrandonGalderisiCommented:
Does each query get what you want independently?  I see some crazy nested CTE's there.
0
 
Jeff GeiselmanAuthor Commented:
Yes, each query returns the necessary data.  I just need to get the two together so I can then create my BOL.
0
 
BrandonGalderisiCommented:
If they return the IDENTICAL columns, then on line 166 above insert the following statement.

INTO #BOL

Then between the current 341 and 342, add this line.

INSERT INTO #BOL

Then at the end:

select * from #BOL

will return everything.
0
 
Jeff GeiselmanAuthor Commented:
Marvelous!!!  I had to add as ; to the end of line 191 in order to make it work, but that does just what I need!  Thank you for your help!
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now