Stored procedure problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Stored procedure problem

Author

Topic

marwest98
Starting Member

9 Posts

Posted - 2011-09-21 : 15:55:40

I am selecting data in the stored procedure below that works great except in one situation. When selecting a record from the prodstructure file (C) if the prdstrparentitemno does not have a prdstrcomponentitemno of 260006 then I have to take the prdstrcomponentitemno and move it to the prdstrparentitemno and search for one that does (second Level component) If I find a 'second level' that has a component of 260006 I still must print the first parentitem on a label. For example:

select * from dbo.ProdStructureFile A

inner join ProdStructureFile B

ON A.PrdStrParentItemNo=B.PrdStrComponentItemNo

where B.PrdStrParentItemNo = '600972'

and A.PrdStrComponentItemNo='260006'

The first select without the inner join and the and condition would return a prdstrparentitemno of 600972 with the inner join

I get CA32112 L as the prdstrparentitemno field that would print on the label not the 600972

I'm lost at how to get this to work.

My stored procedure that works for all other items but this situation looks like:

USE [SilverLabel]
GO
/****** Object: StoredProcedure [dbo].[sel_OrderInformation] Script Date: 09/21/2011 15:45:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Mary Westerman
-- Create date: 08/17/11
-- Description: Stored procedure to get order
-- information necessary to create
-- a silverlabel
--[sel_OrderInformation] '74543','74714','260006','Y'
-- =============================================
ALTER PROCEDURE [dbo].[sel_OrderInformation]
-- Add the parameters for the stored procedure here
@BegOrderNo nvarchar(6),
@EndOrderNo nvarchar(6),
@label nvarchar(6),
@useCustPartNo nvarchar (1)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

SELECT CAST(a.OrderNo as NVarchar),
CAST(a.OrderCustomerNo as NVarchar),
CAST(a.OrderShippingDate as NVarchar),
CAST(B.LineItemQtyToShip as NVarchar),
CAST(C.prdstrComponentItemNo as NVarchar),
CAST(RIGHT(a.OrderNo,3) as NVARCHAR(3))Ord,
CAST(C.PrdStrParentItemNo as NVArchar)
From dbo.COPOrderHeaderFile as A
Inner Join dbo.COPOrderLineItemFile as B
on A.OrderNo = B.LineItemOrderNo
Inner Join dbo.ProdStructureFile as C
on ltrim(rtrim(B.LineItemItemNo)) = ltrim(rtrim(c.PrdStrParentItemNo))
left join dbo.ProdStructureFile as D ON D.PrdStrParentItemNo=C.PrdStrComponentItemNo

where A.OrderNo >= ltrim(rtrim(@BegOrderNo))
and A.OrderNo <= ltrim(rtrim(@EndOrderNo))
and B.LineItemLocation = ' 1'
and LTRIM(rtrim(D.prdstrComponentItemNo)) = @label
order by A.OrderNo

CREATE Table #OrderItems (OrderNo NVarchar(20),ParentItem NVarchar(20),ShippingDate Int,Qty Int,MatlComponent NVarchar(10),Ord NVarchar(3),CustomerItem NVarchar(50))

-- Insert statements for procedure here
if @useCustPartNo = 'N'
Begin
INSERT #OrderItems
SELECT CAST(a.OrderNo as NVarchar),
CAST(a.OrderCustomerNo as NVarchar),
CAST(a.OrderShippingDate as NVarchar),
CAST(B.LineItemQtyToShip as NVarchar),
CAST(C.prdstrComponentItemNo as NVarchar),
CAST(RIGHT(a.OrderNo,3) as NVARCHAR(3))Ord,
CAST(C.PrdStrParentItemNo as NVArchar)
From dbo.COPOrderHeaderFile as A
Inner Join dbo.COPOrderLineItemFile as B
on A.OrderNo = B.LineItemOrderNo
Inner Join dbo.ProdStructureFile as C
on ltrim(rtrim(B.LineItemItemNo)) = ltrim(rtrim(c.PrdStrParentItemNo))
left join dbo.ProdStructureFile as D ON D.PrdStrParentItemNo=C.PrdStrComponentItemNo

where A.OrderNo >= ltrim(rtrim(@BegOrderNo))
and A.OrderNo <= ltrim(rtrim(@EndOrderNo))
and B.LineItemLocation = ' 1'
and LTRIM(rtrim(D.prdstrComponentItemNo)) = @label
order by A.OrderNo

end
else

if @useCustPartNo = 'Y'
Begin
INSERT #OrderItems
SELECT a.OrderNo,
a.OrderCustomerNo,
a.OrderShippingDate,
B.LineItemQtyToShip,
C.prdstrComponentItemNo,
RIGHT(a.OrderNo,3)Ord,
ISNULL(D.CustPartCustPartNumber ,b.LineItemItemNo)CustPartCustPartNumber
From dbo.COPOrderHeaderFile as A
Inner Join dbo.COPOrderLineItemFile as B
on A.OrderNo = B.LineItemOrderNo
Left Outer Join dbo.ProdStructureFile as C
on ltrim(rtrim(LEFT(B.LineItemItemNo,PATINDEX('%LW%',B.LineItemItemNo)-1))) = ltrim(rtrim(c.PrdStrParentItemNo))
Left Outer Join dbo.CustPartNumberFile as d
on a.OrderCustomerNo = d.CustPartcustNumber2
where A.OrderNo >= @BegOrderNo
and A.OrderNo <= @EndOrderNo
and b.LineItemLocation = ' 1'
and LTRIM(rtrim(C.prdstrComponentItemNo)) = @label
and B.LineItemItemNo like '%LW%'

union

SELECT a.OrderNo,
a.OrderCustomerNo,
a.OrderShippingDate,
B.LineItemQtyToShip,
C.prdstrComponentItemNo,
RIGHT(a.OrderNo,3)Ord,
ISNULL(D.CustPartCustPartNumber ,b.LineItemItemNo)CustPartCustPartNumber
From dbo.COPOrderHeaderFile as A
Inner Join dbo.COPOrderLineItemFile as B
on A.OrderNo = B.LineItemOrderNo
Left Outer Join dbo.ProdStructureFile as C
on ltrim(rtrim(B.LineItemItemNo)) = ltrim(rtrim(c.PrdStrParentItemNo))
Left Outer Join dbo.CustPartNumberFile as d
on a.OrderCustomerNo = d.CustPartcustNumber2
where A.OrderNo >= @BegOrderNo
and A.OrderNo <= @EndOrderNo
and b.LineItemLocation = ' 1'
and LTRIM(rtrim(C.prdstrComponentItemNo)) = @label

order by A.OrderNo
end

SELECT OrderNo ShopOrder#,CustomerItem Item#,CAST(Right(ShippingDate,6) as varchar(10))+'-'+Cast(RIGHT('000' + RTRIM(ltrim(rtrim(Ord))), 3)as Varchar(6))Serial#,Qty FROM #OrderItems

END

Please help.

Mary Westerman

marwest98
Starting Member

9 Posts

Posted - 2011-09-21 : 15:59:41

Forgot to mention the second join to the prodstructure file was a test (D), Does not work.

Thanks,

Mary Westerman

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources