Avoiding Iteration - Updating Parent Field

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 2000 Forums

SQL Server Development (2000)

Avoiding Iteration - Updating Parent Field

Author

Topic

Bex
Aged Yak Warrior

580 Posts

Posted - 2006-11-13 : 10:06:47

I have the following data in a temporary table:


CatalogueItemRef	Description			LineItemCatalogueItemRef		Quantity
11-22				Table & Chair set	NULL							1
11-22				Table				212-122							1
11-22				Chair				213-123							4

Once inserted into a table, I have to update it like so:


ID		ParentID	CatalogueItemRef	Description			LineItemCatalogueItemRef		Quantity
1		NULL		11-22				Table & Chair set	NULL							1
2		1			11-22				Table				212-122							1
3		1			11-22				Chair				213-123							4

This is fine as I can obtain the ID based on the CatalogueItemRef and whether the LineItemCatalogueItemRef is null. However, it becomes more problematic when I have multiple Items. For example, if a customer orders 2 sets, then the data will look like this:

CatalogueItemRef	Description			LineItemCatalogueItemRef
11-22				Table & Chair set	NULL					
11-22				Table				212-122					
11-22				Chair				213-123					
11-22				Table & Chair set	NULL					
11-22				Table				212-122					
11-22				Chair				213-123

The final result would have to look like this:

ID		ParentID	CatalogueItemRef	Description			LineItemCatalogueItemRef
1		NULL		11-22				Table & Chair set	NULL					
2		1			11-22				Table				212-122					
3		1			11-22				Chair				213-123					
4		NULL		11-22				Table & Chair set	NULL					
5		4			11-22				Table				212-122					
6		4			11-22				Chair				213-123

How can I update the ParentID field without using iteration??

Thankyou

Hearty head pats

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-11-13 : 10:18:30

"if a customer orders 2 sets"
anyway to identify set 1 from set 2 ?

is set 1 records is to be inserted into the temp table consecutively such that the ID is continous ?

Bex
Aged Yak Warrior

580 Posts

Posted - 2006-11-13 : 10:42:50

After inserting all the data into the actual table from the temp table, it will look like this (ID is auto Increment):

ID	ParentID	CatalogueRef	LineItemRef
1	null		11-22			null
2	null		11-22			112a
3	null		11-22			112b
4	null		11-22			null
5	null		11-22			112a
6	null		11-22			112b

But then I have to update the ParentID field of the lineItems to point to its parent, but the lineItems should point to the parent of 1 set:

ID	ParentID	CatalogueRef	LineItemRef
set 1
1	null		11-22			null
2	1			11-22			112a
3	1			11-22			112b
set 2
4	null		11-22			null
5	4			11-22			112a
6	4			11-22			112b

and not like so:

ID	ParentID	CatalogueRef	LineItemRef
set 1
1	null		11-22			null
2	1			11-22			112a
3	1			11-22			112b
set 2
4	null		11-22			null
5	1			11-22			112a
6	1			11-22			112b

Hearty head pats

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-11-13 : 10:56:56

[code]
update t
set ParentID = (select max(ID) from #table x where x.LineItemRef is null and x.ID <= t.ID)
from #table t
where t.LineItemRef is not null
[/code]

Assumption. Parent's ID must be < Child's ID

Bex
Aged Yak Warrior

580 Posts

Posted - 2006-11-13 : 11:25:07

Hi Khtan

Thankyou for your reply. I can insert the parent items first if I order the result set, but the code does still appear to insert the first parent item for all the child items. I have a small prototype that creates the tables etc, if you run this, then you can see the output and it may make a bit more sense what I am trying to achieve:

Create table #Basketline
(
  BasketID int identity primary key
, PartOrderID int
, ParentID int
, CatalogueItemReference nvarchar(50)
, ParentCatalogueItemReference nvarchar(50)
, Quantity int
, IsBundle bit
, IsWarranty bit
, LineItemTPNB nvarchar(20)
, LineItemDescription  nvarchar(max)
, LineItemCatalogueItem nvarchar(20)
, LineItemQuantity int
)

Create table #TempBasketLine
(
  BasketLineID int identity primary key
, ParentID int
, PartOrderID int
, CatalogueItemreference varchar (10)
, ParentCatalogueItemreference varchar (10)
, CatalogueItem xml
)

-- Insert Item of quantity of 5
insert into #TempBasketLine
(
  ParentID
, PartOrderID
, CatalogueItemreference 
, ParentCatalogueItemreference
, CatalogueItem 
)
select null,107,'211-111',null,
'<OrderLine CatalogueItemReference = "211-111" IsWarranty = "0" Quantity = "2" IsBundle = "1">
	<LineItems>
		<LineItem TPNB = "122a" Description = "Table" CatalogueItemReference = "212-122" Quantity = "1"/>
		<LineItem TPNB = "123b" Description = "Chair" CatalogueItemReference = "213-123" Quantity = "4"/>
		<LineItem TPNB = "124c" Description = "Stools" CatalogueItemReference = "214-124" Quantity = "2"/>
	</LineItems>
</OrderLine>'


insert into #BasketLine
select 
  PartOrderID
, null as ParentID
, CatalogueItemReference
, ParentCatalogueItemReference
, CatalogueItem.value('(//OrderLine/@Quantity)[1]','int')as Quantity
, CatalogueItem.value('(//OrderLine/@IsBundle)[1]','bit')as IsBundle
, CatalogueItem.value('(//OrderLine/@IsWarranty)[1]','bit')as IsWarranty
, li.value('@TPNB','varchar(10)')as LineItemTPNB
, li.value('@Description','varchar(max)')as LineItemDescription
, li.value('@CatalogueItemReference','varchar(10)')as LineItemCatalogueItem
, li.value('@Quantity','int')as LineItemQuantity

from #TempBasketLine
CROSS APPLY CatalogueItem.nodes('//OrderLine/LineItems/LineItem')as L(li)

select * from #BasketLine

insert into #BasketLine
(
  PartOrderID
, ParentID
, CatalogueItemReference
, ParentCatalogueItemReference
, Quantity
, IsBundle
, IsWarranty
, LineItemTPNB
, LineItemDescription
, LineItemCatalogueItem
, LineItemQuantity
)
select distinct
  PartOrderID
, null
, CatalogueItemReference
, ParentCatalogueItemReference
, CatalogueItem.value('(//OrderLine/@Quantity)[1]','int')as Quantity
, CatalogueItem.value('(//OrderLine/@IsBundle)[1]','bit')as IsBundle
, CatalogueItem.value('(//OrderLine/@IsWarranty)[1]','bit')as IsWarranty
, null as LineItemTPNB
, null as LineItemDescription
, null as LineItemCatalogueItem
, 1
from #TempBasketLine
CROSS APPLY CatalogueItem.nodes('//OrderLine/LineItems/LineItem')as L(li)
where CatalogueItem.value('(//OrderLine/@IsBundle)[1]','bit')=1

declare @count int
set @count = 0

while @count <= (select max(quantity*LineItemQuantity) from #BasketLine)
begin
	declare @number table (number int)

	insert into @number 
	select @count 

	set @count = @count + 1
end

create table #temp 
(
  BasketID int identity primary key
, PartOrderID int
, ParentID int
, CatalogueItemReference nvarchar(50)
, ParentCatalogueItemReference nvarchar(50)
, Quantity int
, IsBundle bit
, IsWarranty bit
, LineItemTPNB nvarchar(20)
, LineItemDescription  nvarchar(max)
, LineItemCatalogueItem nvarchar(20)
, LineItemQuantity int
)
insert into #temp
(
  PartOrderID
, ParentID
, CatalogueItemReference
, ParentCatalogueItemReference
, Quantity
, IsBundle
, IsWarranty
, LineItemTPNB
, LineItemDescription
, LineItemCatalogueItem
, LineItemQuantity
)
select   PartOrderID
, ParentID
, CatalogueItemReference
, ParentCatalogueItemReference
, Quantity
, IsBundle
, IsWarranty
, LineItemTPNB
, LineItemDescription
, LineItemCatalogueItem
, LineItemQuantity
from #BasketLine b
inner join @number n on n.number < (b.quantity*LineItemQuantity)
order by LineItemTPNB


update #temp
set ParentID = (select max(BasketID) from #temp p where p.LineItemTPNB is null and p.BasketID <=b.BasketID)
from #temp b where LineItemTPNB is not null

select * from #temp


drop table #BasketLine
drop table #temp
drop table #TempBasketLine

Thanks again for your time

Hearty head pats

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources