Stored procedure help - new to T-SQL

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 help - new to T-SQL

Author

Topic

marwest98
Starting Member

9 Posts

Posted - 2011-09-23 : 09:57:42

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))
where A.OrderNo >= ltrim(rtrim(@BegOrderNo))
and A.OrderNo <= ltrim(rtrim(@EndOrderNo))
and B.LineItemLocation = ' 1'
and LTRIM(rtrim(C.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))

where A.OrderNo >= ltrim(rtrim(@BegOrderNo))
and A.OrderNo <= ltrim(rtrim(@EndOrderNo))
and B.LineItemLocation = ' 1'
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

This SQL is basically what I need to get to but does not seem to work in the stored procedure even if I add another column to the table:

SELECT a.OrderNo,
a.OrderCustomerNo,
a.OrderShippingDate,
B.LineItemQtyToShip,
C.prdstrComponentItemNo,
D.prdstrComponentItemNo,
RIGHT(a.OrderNo,3)as Ord,
C.PrdStrParentItemNo
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
B.LineItemLocation = ' 1'
and A.OrderNo >='74526'
and A.OrderNo <='74714'
and (LTRIM(rtrim(C.prdstrComponentItemNo)) = '260006'
or LTRIM(rtrim(D.prdstrcomponentitemno)) = '260006')

order by A.OrderNo

Please help.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-23 : 10:20:35

can you provide table structure,sample data and required output?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources