Solved

Stored procedure help...

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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
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…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

744 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

11 Experts available now in Live!

Get 1:1 Help Now