Solved

Stored procedure help...

Posted on 2009-04-08
4
255 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

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…
So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

863 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

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now