Hi,everyone.
Today I met with a interesting problem. According to Microsoft SQL SERVER 2005 BOOKS ONLINE, there is following SELECT Syntax:
(1) SELECT @.local_variable (Transact-SQL)
For example:
DECLARE @.i int
SELECt @.i=100
(2) SELECt QUERY statement:
SELECT [ ALL | DISTINCT ]
[ TOP expression [ PERCENT ] [ WITH TIES ] ]
<select_list>
<select_list> ::=
....
For example,
[AdventureWorks]
SELECT * FROM DatabaseLog
To my surprise, in the given project, I met this kind of SELECT statement,
DECLARE @.w_type varchar(10), @.i varchar(30)
SELECT @.i='3'
SELECT @.w_type='OLTPNORMAL' WHERE @.i IN ('0','1','2','3','4','5','9',)
SELECT @.w_type
The result is:
OLTPNORMAL
However, I have not met with the kind of SELECT statement before. Please give me some advice. Thank you in advance.DECLARE @.w_type varchar(10), @.i varchar(30)
SELECT @.i='3'
SELECT @.w_type='OLTPNORMAL' WHERE @.i IN ('0','1','2','3','4','5','9')
SELECT @.w_type
The result is:
OLTPNORMAL
Thats absolutely okay,
SELECT @.i='3' -- assigning value '3' to @.i
SELECT @.w_type='OLTPNORMAL' WHERE @.i IN ('0','1','2','3','4','5','9') -- Assigning @.w_type='OLTPMORMAL' if where clause satisfies,here it is satisfying bcoz @.i='3'
SELECT @.w_type -displaying @.w_type|||Thank you,Rudra.
Here I have gotten the same result as yours. However, the kind of SQL Synax "SELECT...@.local_variable ...WHERE..." is not introduced in SQL SERVER 2005 in Microsoft SQL SERVER 2005 BOOKS ONLINE. So when I found this statement I felt surprised. Can you give me some advice to look for some introductions about the kind of SQL Synax ?
Thank you very much!|||Read this ...LINK (http://msdn2.microsoft.com/en-us/library/ms189484.aspx)
And please read the whole topic( you must scroll down) and then everything will be clear to you.
Showing posts with label according. Show all posts
Showing posts with label according. Show all posts
Sunday, February 19, 2012
Thursday, February 9, 2012
about CTE in SQL 2k5
Hi all,
I tested a new feature of SQL2005 (named cte for recursive selection)
and found the order of the result was weird.
According to the description of help:
1. Split the CTE expression into anchor and recursive members.
2. Run the anchor member(s) creating the first invocation or base
result set (T0).
3. Run the recursive member(s) with Ti as an input and Ti+1 as an
output.
4. Repeat step 3 until an empty set is returned.
5. Return the result set. This is a UNION ALL of T0 to Tn.
But the result was just like the stack-calls.
Is there something wrong on the description of the help?
table creation script:
CREATE TABLE tbUser(
userId int NOT NULL,
mgrId int NULL,
CONSTRAINT [PK_tbUser] PRIMARY KEY CLUSTERED
(
[userId] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
table values:
insert tbUser values(1,null)
insert tbUser values(2,1)
insert tbUser values(3,2)
insert tbUser values(4,2)
CTE scripts
WITH reps_cte (userId, mgrId, recursion_level)
AS
(
SELECT userId, mgrId, 0 FROM tbUser
UNION ALL
SELECT reps_cte.userId, tbUser.mgrId, recursion_level+1 --get the
higher level of the mgr
FROM reps_cte inner join tbUser -- Join with Employee
on reps_cte.mgrId= tbUser.userId -- This employee's manager
where recursion_level<=20 -- up to 20 levels of mgmt
)
Result:
1 NULL 0
2 1 0
3 2 0
4 2 0
4 1 1
4 NULL 2
3 1 1
3 NULL 2
2 NULL 1
Expecting result:
1 NULL 0 - T0 result
2 1 0 - T0 result
3 2 0 - T0 result
4 2 0 - T0 result
2 NULL 1 - T1 result
3 1 1 - T1 result
4 1 1 - T1 result
3 NULL 2 - T2 result
4 NULL 2 - T2 result
Regards,
congling"congling" <congling@.hotmail.com> wrote in message
news:OK9CPzwAGHA.1312@.TK2MSFTNGP09.phx.gbl...
> Hi all,
> I tested a new feature of SQL2005 (named cte for recursive selection)
> and found the order of the result was weird.
> According to the description of help:
> 1. Split the CTE expression into anchor and recursive members.
> 2. Run the anchor member(s) creating the first invocation or base
> result set (T0).
> 3. Run the recursive member(s) with Ti as an input and Ti+1 as an
> output.
> 4. Repeat step 3 until an empty set is returned.
> 5. Return the result set. This is a UNION ALL of T0 to Tn.
> But the result was just like the stack-calls.
> Is there something wrong on the description of the help?
>
The order of a result is always undefined unless the query contains an ORDER
BY clause. The BOL has this description under the heading of "Pseudocode
and Semantics", and it's is a logical description of the result set, not a
guarantee about how it is processed or the order in which the rows are
returned.
David|||congling wrote:
> Hi all,
> I tested a new feature of SQL2005 (named cte for recursive selection)
> and found the order of the result was weird.
> According to the description of help:
> 1. Split the CTE expression into anchor and recursive members.
> 2. Run the anchor member(s) creating the first invocation or base
> result set (T0).
> 3. Run the recursive member(s) with Ti as an input and Ti+1 as an
> output.
> 4. Repeat step 3 until an empty set is returned.
> 5. Return the result set. This is a UNION ALL of T0 to Tn.
> But the result was just like the stack-calls.
> Is there something wrong on the description of the help?
>
> table creation script:
> CREATE TABLE tbUser(
> userId int NOT NULL,
> mgrId int NULL,
> CONSTRAINT [PK_tbUser] PRIMARY KEY CLUSTERED
> (
> [userId] ASC
> )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
> ) ON [PRIMARY]
> table values:
> insert tbUser values(1,null)
> insert tbUser values(2,1)
> insert tbUser values(3,2)
> insert tbUser values(4,2)
> CTE scripts
> WITH reps_cte (userId, mgrId, recursion_level)
> AS
> (
> SELECT userId, mgrId, 0 FROM tbUser
> UNION ALL
> SELECT reps_cte.userId, tbUser.mgrId, recursion_level+1 --get the
> higher level of the mgr
> FROM reps_cte inner join tbUser -- Join with Employe
e
> on reps_cte.mgrId= tbUser.userId -- This employee's manag
er
> where recursion_level<=20 -- up to 20 levels of mgmt
> )
> Result:
> 1 NULL 0
> 2 1 0
> 3 2 0
> 4 2 0
> 4 1 1
> 4 NULL 2
> 3 1 1
> 3 NULL 2
> 2 NULL 1
> Expecting result:
> 1 NULL 0 - T0 result
> 2 1 0 - T0 result
> 3 2 0 - T0 result
> 4 2 0 - T0 result
> 2 NULL 1 - T1 result
> 3 1 1 - T1 result
> 4 1 1 - T1 result
> 3 NULL 2 - T2 result
> 4 NULL 2 - T2 result
>
> Regards,
> congling
If you don't specify ORDER BY then the sorting of the result is
undefined. Try:
WITH reps_cte (userid, mgrid, recursion_level)
AS
(
SELECT userId, mgrId, 0 FROM tbUser
UNION ALL
SELECT reps_cte.userId, tbUser.mgrId, recursion_level+1
FROM reps_cte inner join tbUser
on reps_cte.mgrId= tbUser.userId
where recursion_level<=1
)
SELECT userid, mgrid, recursion_level
FROM reps_cte
ORDER BY recursion_level, mgrid, userid ;
David Portas
SQL Server MVP
--|||thx
Regards,
congling
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> ะด?:%23CcF$FxAGHA.20
40@.TK2MSFTNGP14.phx.gbl...
> "congling" <congling@.hotmail.com> wrote in message
> news:OK9CPzwAGHA.1312@.TK2MSFTNGP09.phx.gbl...
> The order of a result is always undefined unless the query contains an
> ORDER BY clause. The BOL has this description under the heading of
> "Pseudocode and Semantics", and it's is a logical description of the
> result set, not a guarantee about how it is processed or the order in
> which the rows are returned.
> David
>
I tested a new feature of SQL2005 (named cte for recursive selection)
and found the order of the result was weird.
According to the description of help:
1. Split the CTE expression into anchor and recursive members.
2. Run the anchor member(s) creating the first invocation or base
result set (T0).
3. Run the recursive member(s) with Ti as an input and Ti+1 as an
output.
4. Repeat step 3 until an empty set is returned.
5. Return the result set. This is a UNION ALL of T0 to Tn.
But the result was just like the stack-calls.
Is there something wrong on the description of the help?
table creation script:
CREATE TABLE tbUser(
userId int NOT NULL,
mgrId int NULL,
CONSTRAINT [PK_tbUser] PRIMARY KEY CLUSTERED
(
[userId] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
table values:
insert tbUser values(1,null)
insert tbUser values(2,1)
insert tbUser values(3,2)
insert tbUser values(4,2)
CTE scripts
WITH reps_cte (userId, mgrId, recursion_level)
AS
(
SELECT userId, mgrId, 0 FROM tbUser
UNION ALL
SELECT reps_cte.userId, tbUser.mgrId, recursion_level+1 --get the
higher level of the mgr
FROM reps_cte inner join tbUser -- Join with Employee
on reps_cte.mgrId= tbUser.userId -- This employee's manager
where recursion_level<=20 -- up to 20 levels of mgmt
)
Result:
1 NULL 0
2 1 0
3 2 0
4 2 0
4 1 1
4 NULL 2
3 1 1
3 NULL 2
2 NULL 1
Expecting result:
1 NULL 0 - T0 result
2 1 0 - T0 result
3 2 0 - T0 result
4 2 0 - T0 result
2 NULL 1 - T1 result
3 1 1 - T1 result
4 1 1 - T1 result
3 NULL 2 - T2 result
4 NULL 2 - T2 result
Regards,
congling"congling" <congling@.hotmail.com> wrote in message
news:OK9CPzwAGHA.1312@.TK2MSFTNGP09.phx.gbl...
> Hi all,
> I tested a new feature of SQL2005 (named cte for recursive selection)
> and found the order of the result was weird.
> According to the description of help:
> 1. Split the CTE expression into anchor and recursive members.
> 2. Run the anchor member(s) creating the first invocation or base
> result set (T0).
> 3. Run the recursive member(s) with Ti as an input and Ti+1 as an
> output.
> 4. Repeat step 3 until an empty set is returned.
> 5. Return the result set. This is a UNION ALL of T0 to Tn.
> But the result was just like the stack-calls.
> Is there something wrong on the description of the help?
>
The order of a result is always undefined unless the query contains an ORDER
BY clause. The BOL has this description under the heading of "Pseudocode
and Semantics", and it's is a logical description of the result set, not a
guarantee about how it is processed or the order in which the rows are
returned.
David|||congling wrote:
> Hi all,
> I tested a new feature of SQL2005 (named cte for recursive selection)
> and found the order of the result was weird.
> According to the description of help:
> 1. Split the CTE expression into anchor and recursive members.
> 2. Run the anchor member(s) creating the first invocation or base
> result set (T0).
> 3. Run the recursive member(s) with Ti as an input and Ti+1 as an
> output.
> 4. Repeat step 3 until an empty set is returned.
> 5. Return the result set. This is a UNION ALL of T0 to Tn.
> But the result was just like the stack-calls.
> Is there something wrong on the description of the help?
>
> table creation script:
> CREATE TABLE tbUser(
> userId int NOT NULL,
> mgrId int NULL,
> CONSTRAINT [PK_tbUser] PRIMARY KEY CLUSTERED
> (
> [userId] ASC
> )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
> ) ON [PRIMARY]
> table values:
> insert tbUser values(1,null)
> insert tbUser values(2,1)
> insert tbUser values(3,2)
> insert tbUser values(4,2)
> CTE scripts
> WITH reps_cte (userId, mgrId, recursion_level)
> AS
> (
> SELECT userId, mgrId, 0 FROM tbUser
> UNION ALL
> SELECT reps_cte.userId, tbUser.mgrId, recursion_level+1 --get the
> higher level of the mgr
> FROM reps_cte inner join tbUser -- Join with Employe
e
> on reps_cte.mgrId= tbUser.userId -- This employee's manag
er
> where recursion_level<=20 -- up to 20 levels of mgmt
> )
> Result:
> 1 NULL 0
> 2 1 0
> 3 2 0
> 4 2 0
> 4 1 1
> 4 NULL 2
> 3 1 1
> 3 NULL 2
> 2 NULL 1
> Expecting result:
> 1 NULL 0 - T0 result
> 2 1 0 - T0 result
> 3 2 0 - T0 result
> 4 2 0 - T0 result
> 2 NULL 1 - T1 result
> 3 1 1 - T1 result
> 4 1 1 - T1 result
> 3 NULL 2 - T2 result
> 4 NULL 2 - T2 result
>
> Regards,
> congling
If you don't specify ORDER BY then the sorting of the result is
undefined. Try:
WITH reps_cte (userid, mgrid, recursion_level)
AS
(
SELECT userId, mgrId, 0 FROM tbUser
UNION ALL
SELECT reps_cte.userId, tbUser.mgrId, recursion_level+1
FROM reps_cte inner join tbUser
on reps_cte.mgrId= tbUser.userId
where recursion_level<=1
)
SELECT userid, mgrid, recursion_level
FROM reps_cte
ORDER BY recursion_level, mgrid, userid ;
David Portas
SQL Server MVP
--|||thx
Regards,
congling
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> ะด?:%23CcF$FxAGHA.20
40@.TK2MSFTNGP14.phx.gbl...
> "congling" <congling@.hotmail.com> wrote in message
> news:OK9CPzwAGHA.1312@.TK2MSFTNGP09.phx.gbl...
> The order of a result is always undefined unless the query contains an
> ORDER BY clause. The BOL has this description under the heading of
> "Pseudocode and Semantics", and it's is a logical description of the
> result set, not a guarantee about how it is processed or the order in
> which the rows are returned.
> David
>
Subscribe to:
Posts (Atom)