Solved

Stored procedure help...

Posted on 2009-04-08
4
269 Views
Last Modified: 2012-05-06
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
Comment
Question by:Jeff Geiselman
  • 2
  • 2
4 Comments
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 24097927
Does each query get what you want independently?  I see some crazy nested CTE's there.
0
 
LVL 1

Author Comment

by:Jeff Geiselman
ID: 24097944
Yes, each query returns the necessary data.  I just need to get the two together so I can then create my BOL.
0
 
LVL 39

Accepted Solution

by:
BrandonGalderisi earned 500 total points
ID: 24098036
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
 
LVL 1

Author Closing Comment

by:Jeff Geiselman
ID: 31568053
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question