query equivalent: relationship

Showing posts with label relationship. Show all posts

Wednesday, March 28, 2012

Loops and building comma delimited strings

The problem:

I have 2 tables, with a one to many relationship - lets say customers, and order items.

Each order record has a field that is meant to be a comma delimited list (they are reference numbers) that is driven by the quantity field. So, say in the order record, an item has a quantity of 3. The reference number will look like this:

1, 2, 3

And if the next order item for that customer has a quantity of 4, the reference number value is

4, 5, 6, 7

And the final item with quantity of 2:

8, 9

Reference numbers can either be auto assigned (and are in my web application) or manually set. If manually set they will NOT be numeric.

In my web application, it is possible for users to return to a customer's order and edit a line item. My problem is when users changes the quantity of an item, and I have to reset the reference numbers.

If the quantity of line item 2 changes from 4 to 3, I need to reset all the values for that, and any other, order item that comes after it:

4, 5, 6 (2nd)
7,8 (3rd with same quantity of 2).

I felt a cursor would be the best way to handle this. But I am having trouble re-assigning my variable to be the next number in the series when the cursor is running.

This is what I have so far. The print lines and hard coded values are for debugging purposes only.

DECLARE @.NumberingType varchar(10)
DECLARE @.TotalSum int
DECLARE @.DoorLineItemID int
DECLARE @.Quantity int
DECLARE @.SeedInt int

SET @.SeedInt = 1

SELECT @.TotalSum = SUM(Quantity) FROM DoorLineItems WHERE UniversalOrderID = 12345

DECLARE UpdateRefCursor CURSOR FOR
SELECT DoorLineItemID, Quantity FROM DoorLineItems WHERE UniversalOrderID = 12345 AND NumberingType = 1

OPEN UpdateRefCursor

FETCH NEXT FROM UpdateRefCursor INTO @.DoorLineItemID, @.Quantity
DECLARE @.RefNumberLine varchar(1024)
SET @.RefNumberLine = ''

WHILE @.@.FETCH_STATUS = 0
BEGIN

WHILE @.SeedInt <= @.Quantity
BEGIN

SET @.RefNumberLine = @.RefNumberLine + CONVERT(varchar, @.SeedInt, 101) + ', '
SET @.SeedInt = @.SeedInt + 1

END
PRINT @.RefNumberLine

SET @.SeedInt = @.Quantity + @.SeedInt
PRINT 'new seed: ' + CONVERT(varchar, @.SeedInt, 101) + 'Quantity ' + CONVERT(varchar, @.Quantity + @.SeedInt, 101)

FETCH NEXT FROM UpdateRefCursor INTO @.DoorLineItemID, @.Quantity

END

CLOSE UpdateRefCursor
DEALLOCATE UpdateRefCursor

This returns the same delimited string for X number of items. So I'm getting this:

1,2,3
1,2,3
1,2,3

When I really want the results described above.

What am I doing wrong?

Thanks!

You really need to post a table structure and some data for us to use to try this out. That's a lot of variables with no data to reference to try out.|||solved. Thanks for your input.

Monday, March 26, 2012

Looping through records

Hi,
I have a table with papers and second table with types of papers. Now
beetwen papers and types there is many to many relationship.
But in my application I would like to display records the way:
eg.
paper1, type1 type2 ... typex
paper2, type2
paper3, type3 type5
..
I don't know how to create view, procedure or function to create loop
through types for one paperid.
something like
for i = 0 to counter(numeber of records for paperid)
string_types = string_types + type
next record
Thank you
*** Sent via Developersdex http://www.examnotes.net ***Look at some examples that I'd not relied on it :-))))
Seriously, such kind of reports you will be better doing on the client side
create table w
(
id int,
t varchar(50)
)
insert into w values (1,'abc')
insert into w values (1,'def')
insert into w values (1,'ghi')
insert into w values (2,'ABC')
insert into w values (2,'DEF')
select * from w
create function dbo.fn_my ( @.id int)
returns varchar(100)
as
begin
declare @.w varchar(100)
set @.w=''
select @.w=@.w+coalesce(t,'')+',' from w where id=@.id
return @.w
end
select id,
dbo.fn_my (dd.id)
from
(
select distinct id from w
)
as dd
drop function dbo.fn_my
<schapopa> wrote in message news:uw4Wrl8%23FHA.272@.tk2msftngp13.phx.gbl...
> Hi,
> I have a table with papers and second table with types of papers. Now
> beetwen papers and types there is many to many relationship.
> But in my application I would like to display records the way:
> eg.
> paper1, type1 type2 ... typex
> paper2, type2
> paper3, type3 type5
> ..
> I don't know how to create view, procedure or function to create loop
> through types for one paperid.
> something like
> for i = 0 to counter(numeber of records for paperid)
> string_types = string_types + type
> next record
> Thank you
> *** Sent via Developersdex http://www.examnotes.net ***|||schapopa (schapopa) writes:
> I have a table with papers and second table with types of papers. Now
> beetwen papers and types there is many to many relationship.
> But in my application I would like to display records the way:
> eg.
> paper1, type1 type2 ... typex
> paper2, type2
> paper3, type3 type5
> ..
> I don't know how to create view, procedure or function to create loop
> through types for one paperid.
> something like
> for i = 0 to counter(numeber of records for paperid)
> string_types = string_types + type
> next record
> Thank you
In SQL 2000, the only reliable way to do this is to run a cursor. Uri showed
you an example with a function, but that relies an undefined behaviour, and
may not produce the correct result.
In SQL 2005, there is syntax for this, thanks to the XML support. (It's
somewhat obscure, but it works.)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thank you for your help. I used function that Uri showed me as an
example. It works properly.
Cheers
Schapopa
*** Sent via Developersdex http://www.examnotes.net ***|||Thank you for your help. I used function that Uri showed me as an
example. It works properly.
Cheers
Schapopa
*** Sent via Developersdex http://www.examnotes.net ***

Saturday, February 25, 2012

looking for nested XML document as output on a recursive query from sql table

Is it possible to finally have a nested XML document on a recursive query? Let's say that we have a classic parent-child relationship inside a table that is self-refernced. An example of this situation can be found in the Employees table of Northwind DB or in the shining new AdventureWorks DB, HumanResources.Employee table.
In both cases we have somthing like that:

EmployeeID ManagerID Title Firstname LastName

Where ManagerID refers to an existing EmployeeID. I'd like to create and organizational tree.

Using a query like that:

WITH DirectReports (ManagerID, EmployeeID, Title, DeptID, Level, Path)
AS
(
SELECT
ManagerID, EmployeeID, Title, DepartmentID, 0 AS Level, CAST(EmployeeID AS VARCHAR(MAX)) AS Path
FROM
HumanResources.Employee
WHERE
ManagerID IS NULL

UNION ALL

SELECT
e.ManagerID, e.EmployeeID, e.Title, e.DepartmentID, Level + 1, Path + '/'+ CAST(e.EmployeeID AS VARCHAR(MAX))
FROM
HumanResources.Employee AS e
JOIN
DirectReports AS d
ON
e.ManagerID = d.EmployeeID
)
SELECT *
FROM DirectReports
ORDER BY Path

The query is a sample one present in the BOL and modified a little. I also remeber the I've see something similar to a document present at the MSDN site.

Now i'd like to have this result in XML, with the correct nesting:

but without the need to use SQLXML (as I did to obtain the result above).
I've made some attempts, but seems impossible! :-(

Using FOR XML clause will produce a similar xml document but with no nesting, will I was expecting it since a simple FOR XML on a simple JOIN does create them.

It works using UDF but UDF has limit like maximum of 32 recursively nested function invocations. So it will not work for unlimited depth.

Feedback appreciated :-)

vshah:

I am really just starting to learn XML and so I am not sure that I understand your question correctly. Here is some code that seems to produce the output you have requested. Would you please give this a look and tell me if this is headed in the right direction?

Code Snippet

declare @.employee table
( EmployeeId integer,
ManagerId integer,
DepartmentId integer,
Title varchar(18)
)
insert into @.employee
select 109, null, 1, 'Top Dog' union all
select 12, 109, 1, 'Second Dog' union all
select 3, 12, 1, '2-Dog''s Dog' union all
select 4, 3, 1, 'Just Another Dog' union all
select 9, 3, 1, 'Just Another Dog' union all
select 11, 3, 1, 'Just Another Dog' union all
select 158, 3, 1, 'Just Another Dog' union all
select 79, 158, 1, 'Just Another Dog' union all
select 114, 158,1, 'Just Another Dog' union all
select 217, 158,1, 'Just Another Dog' union all
select 263, 3, 1, 'Just Another Dog' union all
select 5, 263, 1, 'Just Another Dog' union all
select 265,263, 1, 'Just Another Dog' union all
select 267, 3, 1, 'Just Another Dog' union all
select 270, 3, 1, 'Just Another Dog'
--select * from @.employee

;with hierarchyCTE as
( select 1 as level,
EmployeeId,
ManagerId,
DepartmentId,
Title,
cast(right(' ' + convert(varchar(11), employeeId), 11) as varchar(240))
as path
from @.employee
where ManagerId is null
union all
select a.level + 1,
b.EmployeeId,
a.EmployeeId,
b.DepartmentId,
b.Title,
cast( a.Path + '/' +
right(' ' + convert(varchar(11), b.employeeId), 11)
as varchar(240))
from hierarchyCTE a
join @.employee b
on a.EmployeeId = b.ManagerId
), sequenceCTE as
( select row_number() over (order by path) as Seq,
level,
EmployeeId,
ManagerId,
DepartmentId,
Title,
Path
from hierarchyCTE
), maxSeqCTE as
( select max(seq) as maxSeq
from sequenceCTE
), nextLevelCTE as
( select -1 + row_number() over (order by path) as Seq,
Level
from hierarchyCTE
union all
select seq,
1
from sequenceCTE
join maxSeqCTE
on seq = maxSeq
), outputCTE as
( select cast(a.Seq as integer) as Seq1,
1 as Seq2,
a.level,
b.level as nextLevel,
a.EmployeeId,
a.ManagerId,
a.DepartmentId,
a.Title,
a.Path
from sequenceCTE a
join nextLevelCTE b
on a.Seq = b.Seq
union all
select Seq1,
Seq2 + 1,
level - 1,
nextLevel,
EmployeeId,
ManagerId,
DepartmentId,
Title,
Path
from outputCte
where level > nextLevel
)
select replicate (' ', Level - 1) +
case when Seq2 = 1
then '<Emp ' + 'ManagerID="' +
case when ManagerId is null then ''
else convert(varchar(11), ManagerId)
end + '" EmployeeId="' +
convert(varchar(11), EmployeeId) +
'" DepartmentId="' +
convert(varchar(11), Departmentid) +
'" Title="' +
case when Title is null then ''
else Title
end + '"' +
case when level >= nextLevel
then '/' else ''
end + '>'
else '</Emp>'
end as xmlOutput
from outputCTE
order by Seq1, Seq2

/*
xmlOutput
--
<Emp ManagerID="" EmployeeId="109" DepartmentId="1" Title="Top Dog">
<Emp ManagerID="109" EmployeeId="12" DepartmentId="1" Title="Second Dog">
<Emp ManagerID="12" EmployeeId="3" DepartmentId="1" Title="2-Dog's Dog">
<Emp ManagerID="3" EmployeeId="4" DepartmentId="1" Title="Just Another Dog"/>
<Emp ManagerID="3" EmployeeId="9" DepartmentId="1" Title="Just Another Dog"/>
<Emp ManagerID="3" EmployeeId="11" DepartmentId="1" Title="Just Another Dog"/>
<Emp ManagerID="3" EmployeeId="158" DepartmentId="1" Title="Just Another Dog">
<Emp ManagerID="158" EmployeeId="79" DepartmentId="1" Title="Just Another Dog"/>
<Emp ManagerID="158" EmployeeId="114" DepartmentId="1" Title="Just Another Dog"/>
<Emp ManagerID="158" EmployeeId="217" DepartmentId="1" Title="Just Another Dog"/>
</Emp>
<Emp ManagerID="3" EmployeeId="263" DepartmentId="1" Title="Just Another Dog">
<Emp ManagerID="263" EmployeeId="5" DepartmentId="1" Title="Just Another Dog"/>
<Emp ManagerID="263" EmployeeId="265" DepartmentId="1" Title="Just Another Dog"/>
</Emp>
<Emp ManagerID="3" EmployeeId="267" DepartmentId="1" Title="Just Another Dog"/>
<Emp ManagerID="3" EmployeeId="270" DepartmentId="1" Title="Just Another Dog"/>
</Emp>
</Emp>
</Emp>
*/

|||

Kent,

Isn't there a way to simplify that solution?

The actual code lenght?

looking for nested XML document as output on a recursive query from sql table

EmployeeID ManagerID Title Firstname LastName

Where ManagerID refers to an existing EmployeeID. I'd like to create and organizational tree.

Using a query like that:

UNION ALL

The query is a sample one present in the BOL and modified a little. I also remeber the I've see something similar to a document present at the MSDN site.

Now i'd like to have this result in XML, with the correct nesting:

but without the need to use SQLXML (as I did to obtain the result above).
I've made some attempts, but seems impossible! :-(

Using FOR XML clause will produce a similar xml document but with no nesting, will I was expecting it since a simple FOR XML on a simple JOIN does create them.

It works using UDF but UDF has limit like maximum of 32 recursively nested function invocations. So it will not work for unlimited depth.

Feedback appreciated :-)

vshah:

Code Snippet

|||

Kent,

Isn't there a way to simplify that solution?

The actual code lenght?

looking for nested XML document as output on a recursive query from sql table

EmployeeID ManagerID Title Firstname LastName

Where ManagerID refers to an existing EmployeeID. I'd like to create and organizational tree.

Using a query like that:

UNION ALL

The query is a sample one present in the BOL and modified a little. I also remeber the I've see something similar to a document present at the MSDN site.

Now i'd like to have this result in XML, with the correct nesting:

but without the need to use SQLXML (as I did to obtain the result above).
I've made some attempts, but seems impossible! :-(

Using FOR XML clause will produce a similar xml document but with no nesting, will I was expecting it since a simple FOR XML on a simple JOIN does create them.

It works using UDF but UDF has limit like maximum of 32 recursively nested function invocations. So it will not work for unlimited depth.

Feedback appreciated :-)

vshah:

Code Snippet

|||

Kent,

Isn't there a way to simplify that solution?

The actual code lenght?

Wednesday, March 28, 2012

Loops and building comma delimited strings

Monday, March 26, 2012

Looping through records

Saturday, February 25, 2012

looking for nested XML document as output on a recursive query from sql table

looking for nested XML document as output on a recursive query from sql table

looking for nested XML document as output on a recursive query from sql table

query equivalent

Blog Archive

About Me