How can I have multiple common table expressions in a single SELECT statement?

Question!

I am in the process of simplifying a complicated select statement, so thought I would use common table expressions.

Declaring a single cte works fine.

WITH cte1 AS (
    SELECT * from cdr.Location
    )

select * from cte1 

Is it possible to declare and use more than one cte in the same SELECT?

ie this sql gives an error

WITH cte1 as (
    SELECT * from cdr.Location
)

WITH cte2 as (
    SELECT * from cdr.Location
)

select * from cte1    
union     
select * from cte2

the error is

Msg 156, Level 15, State 1, Line 7
Incorrect syntax near the keyword 'WITH'.
Msg 319, Level 15, State 1, Line 7
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.

NB. I have tried putting semicolons in and get this error

Msg 102, Level 15, State 1, Line 5
Incorrect syntax near ';'.
Msg 102, Level 15, State 1, Line 9
Incorrect syntax near ';'.

Probably not relevant but this is on SQL 2008.



Answers

You can just use this is in the Username parameter.

admin



I think it should be something like:

WITH 
    cte1 as (SELECT * from cdr.Location),
    cte2 as (SELECT * from cdr.Location)
select * from cte1 union select * from cte2

Basically, WITH is just a clause here, and like the other clauses that take lists, "," is the appropriate delimiter.

By : MarkusQ


The first question to ask is, how would you change the IP address of your server once? In general, you will need the cooperation of your ISP, and even then, they will only have a limited size pool of IP addresses to offer you.

Changing the IP address of your server while it's up will cause any existing connections to eventually fail (because packets from the remote server won't find their own way back to your server, which just moved). So, you would need to set up multiple IP addresses on your server simultaneously, which is possible but again requires the cooperation of your ISP. There will also be a practical limit of how many IP addresses you can assign to your single computer.

The advantage of something like Tor is that requests might come from anywhere in the world. IP addresses are divided up by country and then allocated to ISPs, so there would be no way for your ISP to give you an IP address in the range assigned to Germany, for example (assuming you're not in Germany to begin with).



This video can help you solving your question :)
By: admin