October 30, 2009 at 9:14 am
Hello,
I would like to loop through the rows retrieved and capture the details of a particular column. The data on this column should be used in an if statement to check against some data.
Anyone with an idea on how this can be done ?
October 30, 2009 at 9:27 am
Here is a good tutorial:
http://justgeeks.blogspot.com/2008/08/using-t-sql-cursor-in-sql-server.html
But as is stated in the article, avoid this approach if possible.
October 30, 2009 at 9:33 am
Not sure if looping through the whole table is the best solution. Can you provide more details about your problem?
November 1, 2009 at 6:27 am
Ok,
This is what I am trying to do :
Using a certain query I get the following results
ItemName ItemCode TreeType
Book 1 P
Shoe 2 N
Socks 3 N
So, I want to create an If statement such that
IF (select TreeType from table ABC ) = P
Begin
Blah blah blah
END
ElSE
Begin
Blah blah blah
End
Because TreeType contains multiple values I keep getting the error
Msg 512, Level 16, State 1, Line 19
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
How can I loop over the rows to affect the if statement accordingly.
November 1, 2009 at 7:14 am
Do you just want to check if there is any row that satisfies that condition and do the stuff in the if once, or do you want to do the stuff in the IF once for each row that has that value?
If the former, use EXISTS
IF EXISTS (select 1 from table ABC WHERE TreeType = P)
Begin
Blah blah blah
END
ElSE
Begin
Blah blah blah
End
If the latter, can you give more details as to what 'blah blah blah' is so that we can offer alternatives to a cursor.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 2, 2009 at 1:31 am
Ok, Please help me out construct this query, its a bit long but I need to know how to achieve the required result set. Please bear with and all explain more if need be : -
SET @itemcode = 'P000040'
/** GET THE PRODUCT TREE DETAILS **/
WITH BOM (Code) AS
(
SELECT ROOT.Father, ROOT.Code, ROOT.Quantity
FROM dbo.ITT1 AS ROOT
WHERE ROOT.Father = @itemcode
UNION ALL
SELECT CHILD.Father, CHILD.Code, CHILD.Quantity
FROM dbo.ITT1 AS CHILD
JOIN BOM ON CHILD.Father = BOM.Code
)
select t2.itemname,T0.ItemCode, T0.InQty,
T1.BatchNum,T1.BaseEntry
from oinm T0
inner join ibt1 T1 on T0.ItemCode = T1.ItemCode inner join oitm t2 on t1.itemcode = t2.itemcode
and T0.Warehouse = T1.WhsCode and T0.TransType = T1.BaseType
and T0.CreatedBy = T1.BaseEntry and T0.DocLineNum = T1.BaseLinNum
where T0.applobj = '202' and prodnum = '58'
and T0.ItemCode in (select code from BOM)
OPTION (MAXRECURSION 12); -- Handles 10 levels dept of BOM
RETURN
This results in a BOM Tree, Which is not accurate as it does not digress into
the grandchild items i.e. the child of the child items and so on.
It gives the following : -
ItemName ItemNumber Qty TreeType BatchNum
Book 1 1 P B1
Chair 2 1 N C1
Table 3 1 P T1
The items with the TreeType 'P' Need be brocken further down to get the childitems and so on.
Hence to get the child of child items, I need to get the prodnum, which I will use in the query above to get the BOM tree of the parent displayed. The difficulty am having right now is how to sequence the entire query into a loop, such that where there is treetype 'P' for parent the recursive query is repeated again and again until broken down completely.
/** Get the prodnum **/
Select @prodnum = AppObjAbs FROM oinm T0
inner join ibt1 T1 on T0.ItemCode = T1.ItemCode inner join oitm t2 on t1.itemcode = t2.itemcode
and T0.Warehouse = T1.WhsCode and T0.TransType = T1.BaseType
and T0.CreatedBy = T1.BaseEntry and T0.DocLineNum = T1.BaseLinNum
where T0.applobj = '202' and BaseType = '59' and BatchNum = 'BT2600002' and t2.treetype = 'P'
I need to place this part into a loop, as it will have to be repeated for all parent items in the BOM. Should you need the sample tables, I will provide as soon as requested.
RETURN
November 2, 2009 at 1:42 am
martin.edward (11/2/2009)
/** This results in a BOM Tree, Which is not accurate as it does not digress into
the grandchild items i.e. the child of the child items and so on **/
Lets start at the beginning , recursive CTE's will go through a tree upto MAXRECURSION (defaults to 100) Levels.
So even before we look at the rest of you code, can you provide sample data (both DDL and an insert script) where you believe that this is not happening ?
November 2, 2009 at 1:53 am
This query will go through a maxrecursion 12, as indicated in the edited post. Sorry I left that out.
November 2, 2009 at 2:02 am
Should you need the sample tables, I will provide as soon as requested.
Lets have it then, im still not 100% clear on what you would be expecting as your final output , so please provide that as well.
November 2, 2009 at 2:12 am
Your subquery is returning more than 1 row, and based on that, you want to update the lookup.
why not create a table with multiple columns for child, grandchild, etc and update the columns based on the intial data inserted?
November 2, 2009 at 4:23 am
I have attached a samples database with all the four tables used. Use the query below :-
The item P10004 is a parent item. It returns 3 rows of items, two of which are parent items which I would like to drill into as well.
Kindly assist.
use TESTQUERY
DECLARE @itemcode VARCHAR (20)
DECLARE @prodnum VARCHAR (20)
SET @itemcode = 'P10004'; /* This is the parent item code passed as a parameter */
SET @prodnum = '15' -- Set to 15 the first loop.
/*
15 is the production number, this could as well be passed as a parameter.
When a Parent child item is found, this number as well as the itemcode should be
passed as parameters to get the drill down information on that parent items and so on. This will happen for all parent items.
*/
WITH BOM (Father,Code,Quantity) AS
(
SELECT ROOT.Father, ROOT.Code, ROOT.Quantity
FROM dbo.ITT1 AS ROOT
WHERE ROOT.Father = @itemcode
UNION ALL
SELECT CHILD.Father, CHILD.Code, CHILD.Quantity
FROM dbo.ITT1 AS CHILD
JOIN BOM ON CHILD.Father = BOM.Code
)
select t2.itemname,T0.ItemCode, T0.InQty,
T1.BatchNum,T1.BaseEntry
from oinm T0
inner join ibt1 T1 on T0.ItemCode = T1.ItemCode inner join oitm t2 on t1.itemcode = t2.itemcode
and T0.Warehouse = T1.WhsCode and T0.TransType = T1.BaseType
and T0.CreatedBy = T1.BaseEntry and T0.DocLineNum = T1.BaseLinNum
where T0.applobj = '202' and T1.BaseNum = @prodnum -- The production number 15
and T0.ItemCode in (select code from BOM)
OPTION (MAXRECURSION 12); -- Handles 10 levels dept of BOM
RETURN
/** Get the production number for the parent item,supposed to be repetitive for
all the parent items. I need to throw the @prodnum and itemcode for the parent item back up.
**/
/**
select @prodnum =t0.AppObjAbs , @itemcode = t2.itemcode
from oinm T0
inner join ibt1 T1 on T0.ItemCode = T1.ItemCode inner join oitm t2 on t1.itemcode = t2.itemcode
and T0.Warehouse = T1.WhsCode and T0.TransType = T1.BaseType
and T0.CreatedBy = T1.BaseEntry and T0.DocLineNum = T1.BaseLinNum
where T0.applobj = '202' and T1.BaseNum = @prodnum and t2.treetype = 'P'
**/
November 2, 2009 at 4:52 am
MISTAKE IN POSTING. DELETE THIS DOUBLE POST PLEASE. REFER TO THE FIRST POSTING ABOVE.
Find the attached samples DB with the four tables used in the query. Kind run the query above and view the results posted.
use TESTQUERY
DECLARE @itemcode VARCHAR (20)
DECLARE @prodnum VARCHAR (20)
SET @itemcode = 'P10004';
WITH BOM (Father,Code,Quantity) AS
(
SELECT ROOT.Father, ROOT.Code, ROOT.Quantity
FROM dbo.ITT1 AS ROOT
WHERE ROOT.Father = @itemcode
UNION ALL
SELECT CHILD.Father, CHILD.Code, CHILD.Quantity
FROM dbo.ITT1 AS CHILD
JOIN BOM ON CHILD.Father = BOM.Code
)
select t2.itemname,T0.ItemCode, T0.InQty,
T1.BatchNum,T1.BaseEntry
from oinm T0
inner join ibt1 T1 on T0.ItemCode = T1.ItemCode inner join oitm t2 on t1.itemcode = t2.itemcode
and T0.Warehouse = T1.WhsCode and T0.TransType = T1.BaseType
and T0.CreatedBy = T1.BaseEntry and T0.DocLineNum = T1.BaseLinNum
where T0.applobj = '202' and T1.BaseNum = '15'
and T0.ItemCode in (select code from BOM)
OPTION (MAXRECURSION 12); -- Handles 10 levels dept of BOM
RETURN
/** Get the prodnum,supposed to be recursive **/
/**Select @prodnum = AppObjAbs FROM oinm T0
inner join ibt1 T1 on T0.ItemCode = T1.ItemCode inner join oitm t2 on t1.itemcode = t2.itemcode
and T0.Warehouse = T1.WhsCode and T0.TransType = T1.BaseType
and T0.CreatedBy = T1.BaseEntry and T0.DocLineNum = T1.BaseLinNum
where T0.applobj = '202' and BaseType = '59' and BatchNum = 'BT2600002' and t2.treetype = 'P'
**/
November 2, 2009 at 5:25 am
Hi ,
im not seeing your attachments.
But is all your after a chain of CTE's ?
use TESTQUERY
DECLARE @itemcode VARCHAR (20)
DECLARE @prodnum VARCHAR (20)
SET @itemcode = 'P10004';
WITH BOM (Father,Code,Quantity) AS
(
SELECT ROOT.Father, ROOT.Code, ROOT.Quantity
FROM dbo.ITT1 AS ROOT
WHERE ROOT.Father = @itemcode
UNION ALL
SELECT CHILD.Father, CHILD.Code, CHILD.Quantity
FROM dbo.ITT1 AS CHILD
JOIN BOM ON CHILD.Father = BOM.Code
)
,
AnotherCte as
(
select t2.itemname,T0.ItemCode, T0.InQty,
T1.BatchNum,T1.BaseEntry
from oinm T0
inner join ibt1 T1 on T0.ItemCode = T1.ItemCode inner join oitm t2 on t1.itemcode = t2.itemcode
and T0.Warehouse = T1.WhsCode and T0.TransType = T1.BaseType
and T0.CreatedBy = T1.BaseEntry and T0.DocLineNum = T1.BaseLinNum
where T0.applobj = '202' and T1.BaseNum = '15'
and T0.ItemCode in (select code from BOM)
)
Select AppObjAbs FROM AnotherCTE ,
oinm T0
inner join ibt1 T1 on T0.ItemCode = T1.ItemCode inner join oitm t2 on t1.itemcode = t2.itemcode
and T0.Warehouse = T1.WhsCode and T0.TransType = T1.BaseType
and T0.CreatedBy = T1.BaseEntry and T0.DocLineNum = T1.BaseLinNum
where T0.applobj = '202' and BaseType = '59' and TO.BatchNum = AnotherCte.BatchNum and t2.treetype = 'P'
OPTION (MAXRECURSION 12); -- Handles 10 levels dept of BOM
RETURN
November 2, 2009 at 8:57 am
The database is attached herein. Click here to download
http://www.sqlservercentral.com/Forums/Attachment4424.aspx
Dave,
Well, yes there could be a chain of CTEs but how many ? 12 CTEs ? This was just my way of going through the logic. If there is a better way kindly assist.
November 3, 2009 at 2:55 am
I think a string of CTE's would be in the works though tedious, however the tricky part is how to keep sending over the production number and item code of the next parent item to the CTE.
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply