SQL server two WITH statement - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

SQL server two WITH statement

Author

Topic

Johnseito
Starting Member

28 Posts

Posted - 2014-11-04 : 18:35:35

Hi people,

I wrote an SQL statement, however there is an error.
I have two WITH statements in one big QUERY not sure if I
can do that with SQL server and that is correct.

Here is what it looks like:

WITH X (su, ii, st, sp, nb) AS
(SELECT a.sl, a.i, a.st, max(bp), count(*)
FROM Au a join Bd B on a.sl = b.sl and a.i = b.i
GROUP BY a.sl, a.i, a.st)
WITH Y (b, s, id, st, sp, nb, bt) AS
(SELECT b.b, w.su, w.ii, w.st, w.sp, w.nb, b.bi)
FROM X w join Bd B on w.su = b.se and w.ii = b.ii
WHERE w.sp = b.bi) SELECT id FROM Y WHERE currentime - btime < 7

Can I do two with statement for one query. This is the error I got. The query that I have on top is just an example.


Msg 156, Level 15, State 1, Line 8
Incorrect syntax near the keyword 'WITH'.
Msg 319, Level 15, State 1, Line 8
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.

Thanks !!!

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-04 : 18:40:54

You cannot have two with statements in a single query. You probably want


WITH X (su, ii, st, sp, nb) AS 
(SELECT a.sl, a.i, a.st, max(bp), count(*)
FROM Au a 
join Bd B on a.sl = b.sl and a.i = b.i
GROUP BY a.sl, a.i, a.st),

Y (b, s, id, st, sp, nb, bt) AS 
(SELECT b.b, w.su, w.ii, w.st, w.sp, w.nb, b.bi 
FROM X w 
join Bd B on w.su = b.se and w.ii = b.ii
WHERE w.sp = b.bi)

SELECT id
  FROM Y
  WHERE currentime - btime < 7;

Johnseito
Starting Member

28 Posts

Posted - 2014-11-04 : 19:16:14

what I am really asking is can we do multiple with statement in SQL server and can you provide an example.

I tried it and it didn't work so I don't think it can.

Johnseito
Starting Member

28 Posts

Posted - 2014-11-04 : 19:21:30

quote:
You cannot have two with statements in a single query. You probably want

I took out the with from the second query and now I have a syntax error for the second error, said incorrect syntax near Y.

do you know what could be the error ?

do you know what SQL language or database can use two WITH statements, maybe ORACLE ?

I know access can't use with at all. Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-11-04 : 19:25:20

Why do you want to use two WITH in a single query ?

KH
[spoiler]Time is always against us[/spoiler]

Johnseito
Starting Member

28 Posts

Posted - 2014-11-04 : 19:41:19

the first WITH is to find the max from a list, and the counts that they appear.

the second WITH is to join to another table to get certain fields that the first with doesn't have.

The reason why we did the second WITH is because the first WITH
we couldn't include those fields that we want because if we do, we can't do our GROUP by to find the MAX and counts.

After the second WITH, We have our results and we want id from the second WITH of the currenttime - btime < 7

btime is gotten when second WITH is join with another table.

Johnseito
Starting Member

28 Posts

Posted - 2014-11-04 : 19:48:05

actually two main reason for two WITH,

FIRST is to create variable with a query that we find the max and group by with and store this first query as a variable.

SECOND is to join to get another table that with the first WITH we can't
because we are using GROUP BY.

but this can be go around since we can just use SELECT from first WITH to join another table. HOWEVER, we can't be we want to use the result to find a field, and for currentime - bt < 7

the second reason is because we want the result of the second WITH, just SELECT one field and meeting the criteria that currentime - bt < 7

the second WITH actually have two reason why we use second WITH,
1 - we want to join another column and create a variable for that query to have the final result meet this criteria

Currentime - bt < 7

Johnseito
Starting Member

28 Posts

Posted - 2014-11-04 : 19:57:17

quote:
Y (b, s, id, st, sp, nb, bt) AS
(SELECT b.b, w.su, w.ii, w.st, w.sp, w.nb, b.bi
FROM X w
join Bd B on w.su = b.se and w.ii = b.ii
WHERE w.sp = b.bi)

this code does not work because SQL server don't know what Y is.

Either you use Y with a WITH clause or you just get rid of it. However with the WITH clause you can't use two WITH. getting rid of it works, but now you don't have a variable that can store that query that you can then reference to further get your result from.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-11-04 : 21:04:56

What gbritton posted is a single query. Not 2. You suppose to execute the whole lot from WITH til the WHERE line.

You can't have 2 WITH in a single query and you don't need that to. The general syntax for CTE is


WITH
cteX ( . . . ) as ( <select query> ) ,
cteY ( . . . ) as ( <select query> )
select ...
from   ...

KH
[spoiler]Time is always against us[/spoiler]

Jlamb62480
Starting Member

10 Posts

Posted - 2014-11-04 : 21:49:45

Thanks for that syntax! Was wondering.. Couldn't find an easy to read one anywhere.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-11-04 : 22:03:21

true. Takes a while to get used to reading the syntax in Books Online.

http://msdn.microsoft.com/en-us/library/ms175972%28v=sql.120%29.aspx


[ WITH <common_table_expression> [ ,...n ] ]

<common_table_expression>::=
    expression_name [ ( column_name [ ,...n ] ) ]
    AS
    ( CTE_query_definition )

quote:
WITH <common_table_expression> [ ,...n ]

this means, you can have multiple CTE definition separated with comma. And it starts with single "WITH"

KH
[spoiler]Time is always against us[/spoiler]

Johnseito
Starting Member

28 Posts

Posted - 2014-11-04 : 22:32:36

Thank you Jlamb62480 and Khtan for the info.

I see what you are saying, and you are right.

I Followed this:

quote:

WITH
cteX ( . . . ) as ( <select query> ) ,
cteY ( . . . ) as ( <select query> )
select ...
from ...

good to know !! :-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources