Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Stored procedure help...

Posted on 2009-04-08
4
Medium Priority
?
299 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 2000 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …

609 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