Need to sum columns: loop

Showing posts with label loop. Show all posts

Friday, March 23, 2012

nested while loop

Hi there. I want to avoid a cursur using a quite basing nested while loop. The problem is, that the outer index-variable (i) won't increment at all while the inner loop works perfectly.

This one should be quite easy to solve I guess, I'd be very happy if someone could give me a hint what I should try, though because I don't know what to try. The manual didn't help me much either, using CONTINUE and BREAKs didn't solve this problem for me.

My code:
DECLARE @.i INTEGER
DECLARE @.j INTEGER
SET @.i = 1
SET @.j = 0

WHILE(@.i<= 10) BEGIN
WHILE(@.j <= 100) BEGIN
SELECT @.i, @.j, COUNT(*) as anz FROM mytable WHERE dim1 = @.i AND dim2 = @.j
SET @.j = @.j + 1
END
SET @.i = @.i + 1
END

Thanks a lot for your help :)
Bernhard

corrected typo...[code]
SET i = @.i + 1
/code]

try:

SET @.i = @.i + 1|||try: SET @.i = @.i + 1Hi Kaiowas. Thanks for your reply, but that was just a silly typo in my posting. The original code ist just semantically correct as you suggested. It's not working, though...

Greets, Bernhard|||there might be another thing:
once @.j reaches 100, it is not reset again, so the inner loop runs once.|||there might be another thing:
once @.j reaches 100, it is not reset again, so the inner loop runs once.

Thanks a lot Kaiowas. Silly me :mad: I have to reset the inner counter variable for sure. The correct code:
DECLARE @.i INTEGER
DECLARE @.j INTEGER
SET @.i = 1
SET @.j = 0

WHILE(@.i<= 10) BEGIN
WHILE(@.j <= 100) BEGIN
SELECT @.i, @.j, COUNT(*) as anz FROM mytable WHERE dim1 = @.i AND dim2 = @.j
SET @.j = @.j + 1
END
SET @.j = 0
SET @.i = @.i + 1
END

Thanks a lot :)
Bernhard

Monday, March 19, 2012

nested loops join

Hi,
I have a select statement that gets data from only one table.
When I write OPTION(LOOP JOIN) after this query and run it, the
execution time is 2-3 times faster than without OPTION(LOOP JOIN).
If I use OPTION(FAST 1) the execution time is as fast as with OPTION(LOOP
JOIN)
Does anyone know why its faster with nested loops join even though I don't
join any tables?
Thanks!
//MalinDid you look at the actual execution plan to see what it is doing in both
cases?
Andrew J. Kelly SQL MVP
"Malin Davidsson" <malin.davidsson(at)aus.teleca.se> wrote in message
news:uwxC2gaRFHA.1500@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I have a select statement that gets data from only one table.
> When I write OPTION(LOOP JOIN) after this query and run it, the
> execution time is 2-3 times faster than without OPTION(LOOP JOIN).
> If I use OPTION(FAST 1) the execution time is as fast as with OPTION(LOOP
> JOIN)
> Does anyone know why its faster with nested loops join even though I don't
> join any tables?
> Thanks!
> //Malin
>|||The graphical execution plans are identical. (select <-- Clustered index
s

)
If I have set showplan_text on there is a difference.
select col1, col2, col3
from table1
where col1=1234
option(loop join)
|--Clustered Index S

(OBJECT:([DB].[dbo].[table1].[PK_tIndex]),
SEEK:([table1].[col1]=Convert([@.1])) ORDERED FORWARD)
select col1, col2, col3
from table1
where col1=1234
|--Clustered Index S

(OBJECT:([DB].[dbo].[table1].[PK_tIndex]),
SEEK:([table1].[col1]=1234) ORDERED FORWARD)
Does "Convert([@.1])" have something to do with the execution time of the
query?
Thanks for helping.
// Malin
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eSqTusaRFHA.2604@.TK2MSFTNGP10.phx.gbl...
> Did you look at the actual execution plan to see what it is doing in both
> cases?
> --
> Andrew J. Kelly SQL MVP
>
> "Malin Davidsson" <malin.davidsson(at)aus.teleca.se> wrote in message
> news:uwxC2gaRFHA.1500@.TK2MSFTNGP09.phx.gbl...
>|||for instance, ...perhaps you have a couple of search arguments ANDed and SQL
Server can join these
by two indexes (aka index intersection) and this is the join which is influe
nced by your hint.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eSqTusaRFHA.2604@.TK2MSFTNGP10.phx.gbl...
> Did you look at the actual execution plan to see what it is doing in both
cases?
> --
> Andrew J. Kelly SQL MVP
>
> "Malin Davidsson" <malin.davidsson(at)aus.teleca.se> wrote in message
> news:uwxC2gaRFHA.1500@.TK2MSFTNGP09.phx.gbl...
>|||Hej :-),
I only have one argument in the where statement.
My query looks like "select col1, col2, col3 from table1 where col1=1234"
(as you probably already have seen in my previous message)
That's why I wonder where the "join" is?
// Malin
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23LGPO7aRFHA.3928@.TK2MSFTNGP09.phx.gbl...
> for instance, ...perhaps you have a couple of search arguments ANDed and
> SQL Server can join these by two indexes (aka index intersection) and this
> is the join which is influenced by your hint.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:eSqTusaRFHA.2604@.TK2MSFTNGP10.phx.gbl...
>|||Hej. :-)
Strange... Did you look at the execution plan?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Malin Davidsson" <malin.davidsson(at)aus.teleca.se> wrote in message
news:%23pJpeDbRFHA.3444@.tk2msftngp13.phx.gbl...
> Hej :-),
> I only have one argument in the where statement.
> My query looks like "select col1, col2, col3 from table1 where col1=1234"
(as you probably
> already have seen in my previous message)
> That's why I wonder where the "join" is?
> // Malin
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:%23LGPO7aRFHA.3928@.TK2MSFTNGP09.phx.gbl...
>|||The graphical execution plans are identical. (select <-- Clustered index
s

)
If I have set showplan_text on there is a difference.
select col1, col2, col3
from table1
where col1=1234
option(loop join)
|--Clustered Index S

(OBJECT:([DB].[dbo].[table1].[PK_tIndex]),
SEEK:([table1].[col1]=Convert([@.1])) ORDERED FORWARD)
select col1, col2, col3
from table1
where col1=1234
|--Clustered Index S

(OBJECT:([DB].[dbo].[table1].[PK_tIndex]),
SEEK:([table1].[col1]=1234) ORDERED FORWARD)
Does "Convert([@.1])" have something to do with the execution time of the
query?
Anything more I can do to find out what this can depend on?
// Malin
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eVHTpHbRFHA.3076@.tk2msftngp13.phx.gbl...
> Hej. :-)
> Strange... Did you look at the execution plan?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Malin Davidsson" <malin.davidsson(at)aus.teleca.se> wrote in message
> news:%23pJpeDbRFHA.3444@.tk2msftngp13.phx.gbl...
>|||In this example you have the value for Col1 as an integer. In the real
table is the datatype for Col1 an Integer?
Andrew J. Kelly SQL MVP
"Malin Davidsson" <malin.davidsson(at)aus.teleca.se> wrote in message
news:e0XLO5aRFHA.1172@.TK2MSFTNGP12.phx.gbl...
> The graphical execution plans are identical. (select <-- Clustered index
> s

)
> If I have set showplan_text on there is a difference.
> select col1, col2, col3
> from table1
> where col1=1234
> option(loop join)
> |--Clustered Index S

(OBJECT:([DB].[dbo].[table1].[PK_tIndex]),
> SEEK:([table1].[col1]=Convert([@.1])) ORDERED FORWARD)
>
> select col1, col2, col3
> from table1
> where col1=1234
> |--Clustered Index S

(OBJECT:([DB].[dbo].[table1].[PK_tIndex]),
> SEEK:([table1].[col1]=1234) ORDERED FORWARD)
> Does "Convert([@.1])" have something to do with the execution time of the
> query?
> Thanks for helping.
> // Malin
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:eSqTusaRFHA.2604@.TK2MSFTNGP10.phx.gbl...
>|||yes "col1" is an integer in the real table, the query looks exactly as I
have written except the names :-)
//Malin
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23LZ9yTbRFHA.904@.tk2msftngp13.phx.gbl...
> In this example you have the value for Col1 as an integer. In the real
> table is the datatype for Col1 an Integer?
> --
> Andrew J. Kelly SQL MVP
>
> "Malin Davidsson" <malin.davidsson(at)aus.teleca.se> wrote in message
> news:e0XLO5aRFHA.1172@.TK2MSFTNGP12.phx.gbl...
>|||I will post to the internal group and see if anyone has seen this before.
Andrew J. Kelly SQL MVP
"Malin Davidsson" <malin.davidsson(at)aus.teleca.se> wrote in message
news:uvQUHabRFHA.3076@.TK2MSFTNGP14.phx.gbl...
> yes "col1" is an integer in the real table, the query looks exactly as I
> have written except the names :-)
> //Malin
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:%23LZ9yTbRFHA.904@.tk2msftngp13.phx.gbl...
>

Nested Loops in the Control Flow

I have a problem when using nested loops in my Control Flow. The package contains an outer Foreach Loop using the Foreach File Enumerator which in my test case will loop over two files found in a directory. Inside this loop is another Foreach Loop using the Foreach Nodelist Enumerator. Before entering the inner loop a variable, xpath, is set to a value that depends on the current file, i e /file[name = '@.CurrentFileName']/content. The Nodelist Enumerator is set to use this variable as its OuterXPATHString. Now, this is what happens:

First Iteration:
The first file is found and the value of xpath = /file[name = 'test1.txt']/content. When the inner loop is entered it iterates over the content elements under the file with name test1.txt as expected.

Second Iteration:
The second file is found and the value of xpath = /file[name = 'test2.txt']/content. When the inner loop is entered it unexpectedly still iterates over the content elements under the file with name test1.txt.

My question is: Should it not be possible to change the loop condition of an inner loop in an outer loop such that the next time it is entered it will be done based on the new condition? It seems that the xpath variable is read once, the first time, and never again. If that is the case, does anyone know of a workaround?

Regards,
Lars R?nnb?ck

I noticed that you can set which enumerator to use on the Foreach Loop container using an Expression. On the off chance that this will cause the enumerator to reload at the start of the container and thereby solving my problem described above I thought I'd try it, but I cannot find what value Foreach Enumerator should be set to. It won't accept the string Foreach NodeList Enumerator as a string with or without quotation marks, neither the number 6 works, which seems to be the index of the enumerator in the drop down list. If anyone knows how to set this, let me know and I will try it.

Edit: After reading Kirks blog on Expressions: Part III it seems that expressions applied to Foreach Enumerators are evaluated Before Saving, After Loading, Before Initialization and Before returning from GetEnumerator calls. I am guessing that GetEnumerator is called only once, which causes the behaviour above. I desperately need a workaround then.

Regards,
Lars R?nnb?ck

|||

On what property of the inner loop nodelist enumerator is the property expression set?

You can't set the enumerator to use with an expression. Expressions don't understand objects or IDispatch, so they cannot provide an enumerator to the foreach loop.

It sounds to me like you may have the property expression on the wrong property of the Nodelist Enumerator.

Can you post the package on

http://lab.msdn.microsoft.com/productfeedback/default.aspx

We can take a look. I also have a sneaking suspicion that you've found a bug.

|||

I'll try to give you more detailed information. The settings for the inner Foreach Loop are (in three different variations that all produce the same result):

Variation 1:
In the Collection settings:
Under Foreach Loop Editor the Foreach NodeList Enumerator is selected and no expressions are used here.
In the Enumerator Configuration DocumentSourceType is set to Variable and DocumentSource is set to User::FileDefinition which changes for each iteration of the outer loop. EnumerationType is NodeText, OuterXPathStringSourceType is DirectInput and OuterXPathSource is set to /file/content.
Nothing is set in the other sections.
This iterates over the first FileDefinition for both iterations of the outer loop, even though it is clearly different the second time the inner loop is reached.

Variation 2:
In the Collection settings:
Under Foreach Loop Editor the Foreach NodeList Enumerator is selected and no expressions are used here.
In the Enumerator Configuration DocumentSourceType is set to Variable and DocumentSource is set to User::FileDefinition which does not change and contains content for both example files. EnumerationType is NodeText, OuterXPathStringSourceType is Variable and OuterXPathSource is set to User::xpath. For User::xpath EvaluateAsExpression is set to True and the Expression is "/file[name = '" + @.CurrentFileName + "']/content", so that it will change with each iteration of the outer loop.
Nothing is set in the other sections.
This iterates over the first content (test1.txt) in the FileDefinition for both iterations of the outer loop.

Variation 3:
In the Collection settings:
Under Foreach Loop Editor the Foreach NodeList Enumerator is selected and an expression is set here for the OuterXPathString to be User::xpath.
In the Enumerator Configuration DocumentSourceType is set to Variable and DocumentSource is set to User::FileDefinition which does not change and contains content for both example files. EnumerationType is NodeText, OuterXPathStringSourceType is DirectInput and OuterXPathSource is set to an empty string. For User::xpath EvaluateAsExpression is set to True and the Expression is "/file[name = '" + @.CurrentFileName + "']/content", so that it will change with each iteration of the outer loop.
Nothing is set in the other sections.
This iterates over the first content (test1.txt) in the FileDefinition for both iterations of the outer loop.

Under the Expression settings for the Foreach Container there is a Property named ForeachEnumerator, which is the one I was referring to above, but I could not find a valid value for it. I have no idea if that would have helped in any way though.

For different reasons I cannot put the package on the feedback pages. I could provide it to you in confidence though. I can be reached through lars(at)delicate.se.

Thanks for the reply,
Lars

|||

Kirk,

I managed to reproduce the problem with three new small packages, one for each variation described above. They are filed as a bug at http://lab.msdn.microsoft.com/ProductFeedback/viewFeedback.aspx?feedbackId=FDBK43839. I'm still hoping that it's me who has done something wrong though, since this is preventing me from finishing a step in our current project. Workarounds are welcome too ;)

Regards,
Lars

|||

Thanks,

we were able to repro the problem.

The workaround will be to use to move the inner ForEach Loop to a child package

|||

Thanks Nick,

I've done that and it works as intended now. One pitfall to avoid though that took me a while to figure out, if you have package level event handlers in the parent package, they are still active when the tasks in the child package is running.

Regards,
Lars

|||

Lars,

That's because child packages are just an extension of the parent's container hierarchy. All events "bubble-up" to the top of the container hierarchy unless System::Propogate=FALSE.

-Jamie

Nested Loops in the Control Flow

Regards,
Lars R?nnb?ck

|||

On what property of the inner loop nodelist enumerator is the property expression set?

You can't set the enumerator to use with an expression. Expressions don't understand objects or IDispatch, so they cannot provide an enumerator to the foreach loop.

It sounds to me like you may have the property expression on the wrong property of the Nodelist Enumerator.

Can you post the package on

http://lab.msdn.microsoft.com/productfeedback/default.aspx

We can take a look. I also have a sneaking suspicion that you've found a bug.

|||

I'll try to give you more detailed information. The settings for the inner Foreach Loop are (in three different variations that all produce the same result):

For different reasons I cannot put the package on the feedback pages. I could provide it to you in confidence though. I can be reached through lars(at)delicate.se.

Thanks for the reply,
Lars

|||

Kirk,

Regards,
Lars

|||

Thanks,

we were able to repro the problem.

The workaround will be to use to move the inner ForEach Loop to a child package

|||

Thanks Nick,

Regards,
Lars

|||

Lars,

That's because child packages are just an extension of the parent's container hierarchy. All events "bubble-up" to the top of the container hierarchy unless System::Propogate=FALSE.

-Jamie

Nested loop SQL

Hi
I'm so new in SQL. I have SQL statement that nested loop.
That I don't like.
for example.
I have one table that keep Product Master
----------------
Table T_PRD_MS
----------------
COLUMN I_PRD_TYPE : primary key
I_PRD_ID : primary key
I_MANUF_DATE
I_EXPIRE_DATE
I_VENDOR_ID
I_BRAND_ID

And Other tables that keep Information about this product to readable format eg. T_PRD_TYPE -- > to get product type name
T_PRD_ID -- > to gett product name and detail

When I select data from table T_PRD_MS that in my criteria eg. Product that ID = '111'
I must to copy data to temp table for transaction up date. then I must to work in two steps.
1. select data from T_PRD_MS in criteria to TEMP table
2. use primary key in each row in TEMP table to get data record by record.

EG. sql = select * from T_PRD_MS where I_EXPIRE_DATE
= '20060210'
set SQL as recordset A
If recordset A not eof then
select data from T_PRD_ID where I_PRD_ID = recordset A.fields(0)
and I_PRD_TYPE = recordset A.fields(1)

How can I reduce my job for increase performance?Why not perform a SQL join?

select m.*, i.data
from T_PRD_MS m, T_PRD_ID i
where m.I_EXPIRE_DATE = '20060210'
and m.I_PRD_TYPE = i.I_PRD_TYPE;
and m.I_PRD_ID = i.I_PRD_ID;

Nested Loop Join - need help :)

i'm running the following code on Ms SQL Server 2000, Query Analyzer to analyze the result of Nested Loop Join.

SET STATISTICS PROFILE ON
GO
SELECT pdN.ProductID, pdN.ProductName,
spN.CompanyName, spN.ContactName
FROM dbo.ProductsNew pdN
INNER JOIN dbo.SuppliersNew spN
ON pdN.SupplierId = spN.SupplierId
GO

but the execution plan give me the following result :-

http://i31.photobucket.com/albums/c366/i3lu3fun/executionplan.jpg

instead of using nested, why does it using hash join? is there anything wrong with my code?Hi

Welcome to the forum :D

Nowt wrong with your query. I didn't enlarge your image however the optimiser will select the best plan it can (within certain provisos e.g. it selects the best plan within a time limit, it bases it's plan on available statistics etc.). As such - it is probable for this query that a hash join is better than a nested loop join. BOL illustrates such cases:

A nested loops join is particularly effective if the outer input is quite small and the inner input is preindexed and quite large. In many small transactions, such as those affecting only a small set of rows, index nested loops joins are far superior to both merge joins and hash joins. In large queries, however, nested loops joins are often not the optimal choice.

Short of using hints (which is a bad idea unless you really know what you are doing and, arguably, not even then) all you can do is make sure that you write good, efficient SQL (as you have), ensure statistics are up to date and indexes are optimal. SQL Server does the rest.

HTH|||Thank you for replying :D

Is it possible for me to still get the result of using nested loop join because i need to make a comparison between the execution time of using nested loop, hash & merge join. Will i get the result that i want if i use FORCE option?|||You can force these using Join Hints - check the BOL entries for "FROM" and "Hints".|||BTW - out of curiosity - how come you want to compare the three rather than leave it up to the optimiser?|||hehe, thanks.
I'm doing my FYP, and i need this results to be included in the report. Plus, i need to come up with a better algorithm, to retrieve data in distributed database. Thanks again for ur help.|||where is it the BOL entries :p, sorry newbie here i couldn't find it.|||I'm doing my FYP, and i need this results to be included in the report.
Aw - an RFH.

Ah well - I noticed a little gotcha that I didn't know re hints (unsurprising as you can probably tell I don't tend to use them) - see if you can spot it. Having reread BOL I think that it is referred to but it is rather under stated...|||where is it the BOL entries :p, sorry newbie here i couldn't find it.Well - you are looking for Join Hints so I guess you need to search for... :rolleyes:

Nested Loop in SQL Query

Hi,
I'm probably missing something obvious (either that or doing this totally wrong).
I'm trying to use a nested loop to generate the following results:
Unit Day1 Day2 Day3 Day4 Day5
Name1 25 45 89 54 76
Name2 48 54 81 74 98
What I have so far is this:

WHILE @.FCount < @.TotalFoodUnits
BEGIN
SELECT
 (SELECT Unit FROM tbl_acc_FoodVenues WHERE UnitID = (@.FCount + 1)) AS Unit
 
 WHILE @.FDCount < @.Days
 BEGIN
 SELECT
  (SELECT FdRevenue_a FROM tbl_acc_aud_SportsAudits WHERE AudDate = DATEADD(day, @.FDCount, @.pdStartDate)) AS Rev
  SET @.FDCount = @.FDCount + 1
 END
 SET @.FCount = @.FCount + 1
END

Any suggestions pleaseThis won't do what you want. It looks like you need to do a pivot-table type query that aggregates your data. With loops, you're just going to generate a whole bunch of separate result sets. Doing a pivot type table is difficult if you don't know how many columns you'll have. One thing I've done is create a dynamic SQL statement with cursors that pivots them all together. When the statement is built, it looks like it was basically hardcoded, but it is correct for the data as it is when it is run. The cursor(s) you need depend on what the varying numbers of rows or columns are that you have. Build your SQL statement by looping through the cursors as needed. See if you can hardcode a statement that will work and then look to see what the repeating elements are and how would you get those repeating elements with a SQL statement. You may need to query schema tables to get field names or use DISTINCT to get what values you have. I know thi sisn't very clear, but if you hardcode it and then see how that statement repeats, you might see what I'm talking about.
|||Along the lines of what PDraigh has advised, you might find this post helpful:http://forums.asp.net/1041295/showpost.aspx
|||Hi,
Thanks for the info above, it actually led me to this page:
http://weblogs.sqlteam.com/jeffs/articles/5091.aspx
This provides an alternative solution which pivots the data using .net rather than trying to force sql into doing it for you.

nested loop function

I want to know how to create a recursive loop/function in SQL, I can't seem to figure out how to do it.

The database table I am working with is simply the following:

SeedID, ThisParentSeedID

1, 0

2, 1

3, 1

4, 2

5, 4

6, 5

7, 6

8, 7

9, 7

10, 7

11, 10

12, 0

13, 0

14, 0

The example table above shows that SeedID 1 = the parent level of the data. SeedID 2 and 3 are children of SeedID 1, 4 is child of 2, 5 is child of 4... 12 13 and 14 are also parent levels (they are not children of anything).

I want to know how to create a SQL script that is "object oriented" in that I will not have to create as many levels of nested scripts as there are nested "children" in the data.

What I am wanting to figure out is, with a single script, "which sub-children are assigned to [@.SeedID]"? So if this script was called, and @.SeedID = 1, it would return (2,3,4,5,6,7,8,9,10,11). If @.SeedID = 12, it would return null. If @.SeedID = 7, it would return (8,9,10,11)

I have tried to keep my question and data as simple as possible for the sake of getting some feedback or help. If you want me to clarify or explain better, please ask me to!

CTE (Common Table expression) is best suited for your needs. Here you go

Declare @.ID as INT
SET @.ID = 7

;WITH myCTE AS
(
SELECT SeedID, ParentID FROM Seeds where SeedID = @.ID
UNION ALL
SELECT Seeds.SeedID, Seeds.ParentID From Seeds INNER JOIN myCte
ON Seeds.ParentID = myCte.SeedID
)
SELECT SeedID FROM myCTE where SeedID <> @.ID

|||

--Create the functionCREATE FUNCTION dbo.udf_GetChildren (@.parentIdint )RETURNSVarchar(100)ASBEGINDECLARE @.Childvarchar(100)SELECT @.Child =coalesce(@.child,'') + (casewhen @.childisnot nullthen','else''end ) +convert(varchar,SeedId)FROM YourTable CWHERE C.ThisParentSeedID = @.parentIdSELECT @.Child = @.Child + dbo.udf_GetChildren(seedid)FROM YourTable CWHERE C.ThisParentSeedID = @.parentIdReturnCoalesce(@.child,'')END--Call the functionSELECT dbo.udf_GetChildren(1)

Monday, March 12, 2012

nested for loop with xquery

What is wrong with this snippet of XQuery like this
declare @.city xml
set @.city = (select @.x.query('
for $r in distinct-values(//Region)
return
<Regions>{
<Region>{$r}</Region>
{
for $mt in distinct-values(//MatterType)
return <MatterType>{$mt}</MatterType>
}
}</Regions>
') as cities)
select @.city
It works fine without the <Region>{$r}</Region> and the pair of
brackets after itThe problem is that you have too many {}... {} are used in XQuery to switch
from the lexical XML construction syntax into XQuery syntax...
so in your case you have:
XQUERY CONTEXT 0> for $r in distinct-values(//Region)
> return
THIS SWITCHES TO XML CONSTRUCTION CONTEXT 1> <Regions>
THIS INTO XQUERY CONTEXT 1> {
TO XML CONSTRUCTION CONTEXT 2> <Region>
XQUERY CONTEXT 1> {$r
BACK OUT INTO OUTER XML CONTEXT 2> }
CLOSES XML CONTEXT 2> </Region>
TRIES TO OPEN XQUERY CONTEXT INSIDE XQUERY CONTEXT AND ERRORS> {
> for $mt in distinct-values(//MatterType)
> return <MatterType>{$mt}</MatterType>
> }
> }</Regions>
So you write either
set @.city = (select @.x.query('
for $r in distinct-values(//Region)
return
<Regions>{
<Region>{$r}</Region>,
for $mt in distinct-values(//MatterType)
return <MatterType>{$mt}</MatterType>
}</Regions>
') as cities)
or
set @.city = (select @.x.query('
for $r in distinct-values(//Region)
return
<Regions>
<Region>{$r}</Region>{
for $mt in distinct-values(//MatterType)
return <MatterType>{$mt}</MatterType>
}</Regions>
') as cities)
Best regards
Michael
"joyce chan" <joyceschan@.fastmail.fm> wrote in message
news:1169755098.049990.46950@.l53g2000cwa.googlegroups.com...
> What is wrong with this snippet of XQuery like this
> declare @.city xml
> set @.city = (select @.x.query('
> for $r in distinct-values(//Region)
> return
> <Regions>{
> <Region>{$r}</Region>
> {
> for $mt in distinct-values(//MatterType)
> return <MatterType>{$mt}</MatterType>
> }
> }</Regions>
> ') as cities)
> select @.city
> It works fine without the <Region>{$r}</Region> and the pair of
> brackets after it
>|||hi Michael
Thank you for all of your help.
I have some more questions re xpath/xquery
1. What is the comma <Region>{$r}</Region>,
2. Continuing from the query I asked about before, if i want to
return a count, how would I do that? I am doing this, but it returns
0
set @.city = (select @.x.query('
for $r in distinct-values(//Region)
return
<Regions>
<Region>{$r}</Region> {
for $mt in distinct-values(//MatterType)
return <MatterType ><count>{count(//Region[.=$r]/MatterType)}</
count></MatterType>
}</Regions>
'))
On Jan 25, 11:08 pm, "Michael Rys [MSFT]" <m...@.online.microsoft.com>
wrote:
> The problem is that you have too many {}... {} are used in XQuery to switc
h
> from the lexical XML construction syntax into XQuery syntax...
> so in your case you have:
> XQUERY CONTEXT 0> for $r in distinct-values(//Region)> returnTHIS SWITCHES
TO XML CONSTRUCTION CONTEXT 1> <Regions>
> THIS INTO XQUERY CONTEXT 1> {
> TO XML CONSTRUCTION CONTEXT 2> <Region>
> XQUERY CONTEXT 1> {$r
> BACK OUT INTO OUTER XML CONTEXT 2> }
> CLOSES XML CONTEXT 2> </Region>
> TRIES TO OPEN XQUERY CONTEXT INSIDE XQUERY CONTEXT AND ERRORS> {
>
> set @.city = (select @.x.query('
> for $r in distinct-values(//Region)
> return
> <Regions>{
> <Region>{$r}</Region>,
> for $mt in distinct-values(//MatterType)
> return <MatterType>{$mt}</MatterType>
> }</Regions>') as cities)
> or
> set @.city = (select @.x.query('
> for $r in distinct-values(//Region)
> return
> <Regions>
> <Region>{$r}</Region>{
> for $mt in distinct-values(//MatterType)
> return <MatterType>{$mt}</MatterType>
> }</Regions>') as cities)
> Best regards
> Michael
> "joyce chan" <joycesc...@.fastmail.fm> wrote in messagenews:1169755098.0499
90.46950@.l53g2000cwa.googlegroups.com...
>
>
>|||and also
3. how would i create the xquery string to return a value as an
attribute rather than as an element as I've done before? (return
<MatterType>{$mt}</MatterType> )
Thanks!
On Jan 29, 11:51 am, "joyce" <joycesc...@.fastmail.fm> wrote:
> hi Michael
> Thank you for all of your help.
> I have some more questions re xpath/xquery
> 1. What is the comma <Region>{$r}</Region>,
> 2. Continuing from the query I asked about before, if i want to
> return a count, how would I do that? I am doing this, but it returns
> 0
> set @.city = (select @.x.query('
> for $r in distinct-values(//Region)
> return
> <Regions>
> <Region>{$r}</Region> {
> for $mt in distinct-values(//MatterType)
> return <MatterType ><count>{count(//Region[.=$r]/MatterTyp
e)}</
> count></MatterType>
> }</Regions>
> '))
> On Jan 25, 11:08 pm, "Michael Rys [MSFT]" <m...@.online.microsoft.com>
> wrote:
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>

nested for loop with xquery

What is wrong with this snippet of XQuery like this
declare @.city xml
set @.city = (select @.x.query('
for $r in distinct-values(//Region)
return
<Regions>{
<Region>{$r}</Region>
{
for $mt in distinct-values(//MatterType)
return <MatterType>{$mt}</MatterType>
}
}</Regions>
') as cities)
select @.city
It works fine without the <Region>{$r}</Region> and the pair of
brackets after it
The problem is that you have too many {}... {} are used in XQuery to switch
from the lexical XML construction syntax into XQuery syntax...
so in your case you have:
XQUERY CONTEXT 0> for $r in distinct-values(//Region)
> return
THIS SWITCHES TO XML CONSTRUCTION CONTEXT 1> <Regions>
THIS INTO XQUERY CONTEXT 1> {
TO XML CONSTRUCTION CONTEXT 2> <Region>
XQUERY CONTEXT 1> {$r
BACK OUT INTO OUTER XML CONTEXT 2> }
CLOSES XML CONTEXT 2> </Region>
TRIES TO OPEN XQUERY CONTEXT INSIDE XQUERY CONTEXT AND ERRORS> {
> for $mt in distinct-values(//MatterType)
> return <MatterType>{$mt}</MatterType>
> }
> }</Regions>
So you write either
set @.city = (select @.x.query('
for $r in distinct-values(//Region)
return
<Regions>{
<Region>{$r}</Region>,
for $mt in distinct-values(//MatterType)
return <MatterType>{$mt}</MatterType>
}</Regions>
') as cities)
or
set @.city = (select @.x.query('
for $r in distinct-values(//Region)
return
<Regions>
<Region>{$r}</Region>{
for $mt in distinct-values(//MatterType)
return <MatterType>{$mt}</MatterType>
}</Regions>
') as cities)
Best regards
Michael
"joyce chan" <joyceschan@.fastmail.fm> wrote in message
news:1169755098.049990.46950@.l53g2000cwa.googlegro ups.com...
> What is wrong with this snippet of XQuery like this
> declare @.city xml
> set @.city = (select @.x.query('
> for $r in distinct-values(//Region)
> return
> <Regions>{
> <Region>{$r}</Region>
> {
> for $mt in distinct-values(//MatterType)
> return <MatterType>{$mt}</MatterType>
> }
> }</Regions>
> ') as cities)
> select @.city
> It works fine without the <Region>{$r}</Region> and the pair of
> brackets after it
>
|||hi Michael
Thank you for all of your help.
I have some more questions re xpath/xquery
1. What is the comma <Region>{$r}</Region>,
2. Continuing from the query I asked about before, if i want to
return a count, how would I do that? I am doing this, but it returns
0
set @.city = (select @.x.query('
for $r in distinct-values(//Region)
return
<Regions>
<Region>{$r}</Region> {
for $mt in distinct-values(//MatterType)
return <MatterType ><count>{count(//Region[.=$r]/MatterType)}</
count></MatterType>
}</Regions>
'))
On Jan 25, 11:08 pm, "Michael Rys [MSFT]" <m...@.online.microsoft.com>
wrote:[vbcol=seagreen]
> The problem is that you have too many {}... {} are used in XQuery to switch
> from the lexical XML construction syntax into XQuery syntax...
> so in your case you have:
> XQUERY CONTEXT 0> for $r in distinct-values(//Region)> returnTHIS SWITCHES TO XML CONSTRUCTION CONTEXT 1> <Regions>
> THIS INTO XQUERY CONTEXT 1> {
> TO XML CONSTRUCTION CONTEXT 2> <Region>
> XQUERY CONTEXT 1> {$r
> BACK OUT INTO OUTER XML CONTEXT 2> }
> CLOSES XML CONTEXT 2> </Region>
> TRIES TO OPEN XQUERY CONTEXT INSIDE XQUERY CONTEXT AND ERRORS> {
>
> set @.city = (select @.x.query('
> for $r in distinct-values(//Region)
> return
> <Regions>{
> <Region>{$r}</Region>,
> for $mt in distinct-values(//MatterType)
> return <MatterType>{$mt}</MatterType>
> }</Regions>') as cities)
> or
> set @.city = (select @.x.query('
> for $r in distinct-values(//Region)
> return
> <Regions>
> <Region>{$r}</Region>{
> for $mt in distinct-values(//MatterType)
> return <MatterType>{$mt}</MatterType>
> }</Regions>') as cities)
> Best regards
> Michael
> "joyce chan" <joycesc...@.fastmail.fm> wrote in messagenews:1169755098.049990.46950@.l53g2000cwa.go oglegroups.com...
>
|||and also
3. how would i create the xquery string to return a value as an
attribute rather than as an element as I've done before? (return
<MatterType>{$mt}</MatterType>)
Thanks!
On Jan 29, 11:51 am, "joyce" <joycesc...@.fastmail.fm> wrote:[vbcol=seagreen]
> hi Michael
> Thank you for all of your help.
> I have some more questions re xpath/xquery
> 1. What is the comma <Region>{$r}</Region>,
> 2. Continuing from the query I asked about before, if i want to
> return a count, how would I do that? I am doing this, but it returns
> 0
> set @.city = (select @.x.query('
> for $r in distinct-values(//Region)
> return
> <Regions>
> <Region>{$r}</Region> {
> for $mt in distinct-values(//MatterType)
> return <MatterType ><count>{count(//Region[.=$r]/MatterType)}</
> count></MatterType>
> }</Regions>
> '))
> On Jan 25, 11:08 pm, "Michael Rys [MSFT]" <m...@.online.microsoft.com>
> wrote:
>
>
>
>
>
>
>
>
>
>
>
>
>

Nested Cursor

I think I am getting an endless loop here... anyone know how to fix it?

***********************

CREATE PROCEDURE TrigSendPreNewIMAlertP2
@.REID int

Declare @.RRID int
Declare @.ITID int
Declare @.FS2 int
Declare @.FS1 int

Declare crReqRec cursor for
select RRID from RequestRecords where REID = @.REID and RRSTatus = 'IA' and APID is not null
open crReqRec
fetch next from crReqRec
into
@.RRID

Declare crImpGrp cursor for
select ITID from RequestRecords where RRID = @.RRID
open crImpGrp
fetch next from crImgGrp
into
@.ITID

while @.@.fetch_status = 0
select @.FS1 = @.@.Fetch_Status

EXEC TrigSendNewIMAlertP2 @.ITID

FETCH NEXT FROM crImpGrp
into
@.ITID

close crImpGrp
deallocate crImpGrp

while @.@.Fetch_Status = 0
select @.FS2 = @.@.Fetch_Status

FETCH NEXT FROM crReqRec
into
@.RRID

close crReqRec
deallocate crReqRec
GOi think i had replied to you earlier with a similar question about cursors ? coz i recollect you did the same thing at that time..

heres a good template for a cursor..you should be able to figur eit out pretty easily for a nested cursor too :


DECLARE rs CURSOR
LOCAL
FORWARD_ONLY
OPTIMISTIC
TYPE_WARNING
FOR SELECT .........
OPEN rs
fetch next from rs into .....
WHILE ( @.@.FETCH_STATUS = 0 )
begin
 --do your processing here...in your case declare the nested cursor hereFETCH NEXT FROM rs INTO ......
END
close rs
deallocate rs

hth

Friday, March 23, 2012

nested while loop

Monday, March 19, 2012

nested loops join

Nested Loops in the Control Flow

Nested Loops in the Control Flow

Nested loop SQL

Nested Loop Join - need help :)

Nested Loop in SQL Query

nested loop function

Monday, March 12, 2012

nested for loop with xquery

nested for loop with xquery

Nested Cursor

Need to sum columns

Blog Archive

About Me