March 6, 2014 at 10:48 am
In existing t-sql 2008 that I need to modify, there is already an existing cte that looks like the following:
;WITH rCust AS ( SELECT CustuserSK]
,[structureName]
,[custType]
,[lastName]
,[firstName]
,[middleName]
,[suffix]
FROM
[DataStore].[dbo].[vwBridgeUser]
WHERE
custType = @custType
)
I would like to add the following cte so I can remove the hard-code year values.
;WITH sYear AS (
SELECT [CustSK],[startYear],[endYear]
FROM DimCustCalendarYear
where DimCustCalendarYear.active = 1
)
The both ctes do not reference each other. They are used in separate parts of the sql.
Thus I have the following questions:
1. Can you show me sql and or point me to a reference that shows me how to use 2 cte's in the same sql?
2. If 2 cte's can not be used in the same sql, would you explain to me how I would solve my problem or possibly show
me some alternative that would solve my problem? Would I need to use temp tables, temp variables, view, function, or
what?
March 6, 2014 at 10:51 am
Just put a comma not a ;with between them
March 6, 2014 at 10:56 am
Hi Wendy,
If they're referenced in the same sql, then:
WITH CTE_1
AS (),
CTE_2
AS()
SELECT ...
FROM CTE_1... CTE_2
If not:
WITH CTE_1 AS ()
SELECT ... FROM CTE_1;
WITH CTE_2 AS ()
SELECT ... FROM CTE_2;
This whole ;WITH thing really bugs me; it's the previous statement that should be terminated with ; , not the new one started with it! 🙂
March 6, 2014 at 12:21 pm
Gazareth (3/6/2014)
This whole ;WITH thing really bugs me; it's the previous statement that should be terminated with ; , not the new one started with it! 🙂
+1
To paraphrase Lynn Pettis, "The semicolon is a line terminator not a line beginnifier". 😀
It is however very common because it is one of the few statements that requires the previous statement to terminate with a semicolon. Eventually the semicolon will be required for every statement but not yet.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 6, 2014 at 1:53 pm
Lynn also refuses to put commas at the beginning of a list of values. 🙂
Given how SQL treats white space, a semicolon is more properly described as a separator, rather than a terminator or a beginner. If you always start with a semicolon, you can go back and add or change code prior to the semicolon without worrying about having to move the semicolon around.
You say potatoes... I say "spuds"....
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
March 6, 2014 at 2:05 pm
The Dixie Flatline (3/6/2014)
Lynn also refuses to put commas at the beginning of a list of values. 🙂Given how SQL treats white space, a semicolon is more properly described as a separator, rather than a terminator or a beginner. If you always start with a semicolon, you can go back and add or change code prior to the semicolon without worrying about having to move the semicolon around.
You say potatoes... I say "spuds"....
I should have said statement terminator, not line terminator.
I like your point of white space. It is just like any of the languages in the C/C++/C#/java style syntax. You can have as many statements as you want on a single line. The compiler/interpreter knows it should end the statement when it finds a semicolon.
These two scripts are 100% identical to the sql engine.
create table #Something(ID int, SomeVal varchar(10));insert #Something select 10,'Val1';with mycte as(select * from #Something)select * from mycte;drop table #Something;
create table #Something
(
ID int,
SomeVal varchar(10)
);
insert #Something
select 10,'Val1';
with mycte as
(
select *
from #Something
)
select *
from mycte;
drop table #Something;
And as with other languages you can arbitrarily decide to end as many statements as you want.
select * from SomeTable;;;;;;;;;;;;;;;;;;;;;;;
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 6, 2014 at 2:33 pm
wendy elizabeth (3/6/2014)
In existing t-sql 2008 that I need to modify, there is already an existing cte that looks like the following:;WITH rCust AS ( SELECT CustuserSK]
,[structureName]
,[custType]
,[lastName]
,[firstName]
,[middleName]
,[suffix]
FROM
[DataStore].[dbo].[vwBridgeUser]
WHERE
custType = @custType
)
I would like to add the following cte so I can remove the hard-code year values.
;WITH sYear AS (
SELECT [CustSK],[startYear],[endYear]
FROM DimCustCalendarYear
where DimCustCalendarYear.active = 1
)
The both ctes do not reference each other. They are used in separate parts of the sql.
Thus I have the following questions:
1. Can you show me sql and or point me to a reference that shows me how to use 2 cte's in the same sql?
2. If 2 cte's can not be used in the same sql, would you explain to me how I would solve my problem or possibly show
me some alternative that would solve my problem? Would I need to use temp tables, temp variables, view, function, or
what?
Looking at this code I am trying to figure out how you are trying use these queries together. Perhaps if you would provide full details regarding what you are trying to accomplish it would help us give you better answers.
March 6, 2014 at 4:54 pm
Sean Lange (3/6/2014)
Gazareth (3/6/2014)
This whole ;WITH thing really bugs me; it's the previous statement that should be terminated with ; , not the new one started with it! 🙂
+1
To paraphrase Lynn Pettis, "The semicolon is a line terminator not a line beginnifier". 😀
It is however very common because it is one of the few statements that requires the previous statement to terminate with a semicolon. Eventually the semicolon will be required for every statement but not yet.
I don't like the constant placement of a ; right before a CTE. It's wrong. A CTE does not require a ; in order to execute. You can run a CTE without a ; if it is the first statement in a batch, or if it is separated by some control of flow language. IMO, it would be better to describer a CTE as requiring a control of flow statement, unless it is the first statement (or only statement) in a batch.
Consider the following
Declare @Limit BigInt
Set @Limit = 100
/* statement terminator not required because BEGIN creates a separation of statements */
BEGIN
WITH Nbrs_2( n ) AS (select 1 union all
select 1 union all
select 1 union all
select 1 union all
select 1 union all
select 1 union all
select 1 union all
select 1 union all
select 1 union all
select 0),
Nbrs_1( n ) AS ( SELECT 1 FROM Nbrs_2 n1 CROSS JOIN Nbrs_2 n2 ),
Nbrs_0( n ) AS ( SELECT 1 FROM Nbrs_1 n1 CROSS JOIN Nbrs_1 n2 ),
Nbrs ( n ) AS ( SELECT 1 FROM Nbrs_0 n1 CROSS JOIN Nbrs_0 n2 )
Select *
FROM ( SELECT ROW_NUMBER() OVER (ORDER BY n)
FROM Nbrs ) D (n)
WHERE n <= abs(@Limit);
END
GO
Declare @Limit BigInt = 100;
/* statement terminator required on the previous statement */
WITH Nbrs_2( n ) AS (select 1 union all
select 1 union all
select 1 union all
select 1 union all
select 1 union all
select 1 union all
select 1 union all
select 1 union all
select 1 union all
select 0),
Nbrs_1( n ) AS ( SELECT 1 FROM Nbrs_2 n1 CROSS JOIN Nbrs_2 n2 ),
Nbrs_0( n ) AS ( SELECT 1 FROM Nbrs_1 n1 CROSS JOIN Nbrs_1 n2 ),
Nbrs ( n ) AS ( SELECT 1 FROM Nbrs_0 n1 CROSS JOIN Nbrs_0 n2 )
Select *
FROM ( SELECT ROW_NUMBER() OVER (ORDER BY n)
FROM Nbrs ) D (n)
WHERE n <= abs(@Limit);
GO
/* control of flow used in the use of a GO as the previous statement */
WITH Nbrs_2( n ) AS (select 1 union all
select 1 union all
select 1 union all
select 1 union all
select 1 union all
select 1 union all
select 1 union all
select 1 union all
select 1 union all
select 0),
Nbrs_1( n ) AS ( SELECT 1 FROM Nbrs_2 n1 CROSS JOIN Nbrs_2 n2 ),
Nbrs_0( n ) AS ( SELECT 1 FROM Nbrs_1 n1 CROSS JOIN Nbrs_1 n2 ),
Nbrs ( n ) AS ( SELECT 1 FROM Nbrs_0 n1 CROSS JOIN Nbrs_0 n2 )
Select *
FROM ( SELECT ROW_NUMBER() OVER (ORDER BY n)
FROM Nbrs ) D (n)
WHERE n <= abs(100);
/* try this one now, the "END" is not adequate by itself as a control of flow and a statement terminator should be used*/
BEGIN
Select 'blah'
END;
WITH Nbrs_2( n ) AS (select 1 union all
select 1 union all
select 1 union all
select 1 union all
select 1 union all
select 1 union all
select 1 union all
select 1 union all
select 1 union all
select 0),
Nbrs_1( n ) AS ( SELECT 1 FROM Nbrs_2 n1 CROSS JOIN Nbrs_2 n2 ),
Nbrs_0( n ) AS ( SELECT 1 FROM Nbrs_1 n1 CROSS JOIN Nbrs_1 n2 ),
Nbrs ( n ) AS ( SELECT 1 FROM Nbrs_0 n1 CROSS JOIN Nbrs_0 n2 )
Select *
FROM ( SELECT ROW_NUMBER() OVER (ORDER BY n)
FROM Nbrs ) D (n)
WHERE n <= abs(100);
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 6, 2014 at 6:04 pm
Sean Lange (3/6/2014)
select * from SomeTable;;;;;;;;;;;;;;;;;;;;;;;
That is the SQL equivalent of "you're terminated sucker!" 😛
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
March 6, 2014 at 6:06 pm
Lynn Pettis (3/6/2014)
wendy elizabeth (3/6/2014)
In existing t-sql 2008 that I need to modify, there is already an existing cte that looks like the following:;WITH rCust AS ( SELECT CustuserSK]
,[structureName]
,[custType]
,[lastName]
,[firstName]
,[middleName]
,[suffix]
FROM
[DataStore].[dbo].[vwBridgeUser]
WHERE
custType = @custType
)
I would like to add the following cte so I can remove the hard-code year values.
;WITH sYear AS (
SELECT [CustSK],[startYear],[endYear]
FROM DimCustCalendarYear
where DimCustCalendarYear.active = 1
)
The both ctes do not reference each other. They are used in separate parts of the sql.
Thus I have the following questions:
1. Can you show me sql and or point me to a reference that shows me how to use 2 cte's in the same sql?
2. If 2 cte's can not be used in the same sql, would you explain to me how I would solve my problem or possibly show
me some alternative that would solve my problem? Would I need to use temp tables, temp variables, view, function, or
what?
Looking at this code I am trying to figure out how you are trying use these queries together. Perhaps if you would provide full details regarding what you are trying to accomplish it would help us give you better answers.
My opportunity once again to thank you Lynn for cajoling me into terminating all of my SQL with a semicolon.
I've tried to be a good boy...
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
March 6, 2014 at 8:12 pm
But when semicolons are required between statements as separators, it would just be so EASY to start each new statement with a semicolon. Besides, reading from left to right means that the semicolons are easier to spot.
I love a good argument over personal preferences.
😛
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
March 6, 2014 at 9:07 pm
The Dixie Flatline (3/6/2014)
But when semicolons are required between statements as separators, it would just be so EASY to start each new statement with a semicolon. Besides, reading from left to right means that the semicolons are easier to spot.I love a good argument over personal preferences.
😛
Okay, but the MERGE statement requires that it be TERMINATED by a semicolon (;).
That means you would have something like this:
;WITH ctename as (
...
)
MERGE ...;
Looks a little silly having a statement terminator both before and after a single statement.
Just adding a bit more to the discussion. 😉
March 7, 2014 at 7:21 am
SQLRNNR (3/6/2014)
I don't like the constant placement of a ; right before a CTE. It's wrong. A CTE does not require a ; in order to execute. You can run a CTE without a ; if it is the first statement in a batch, or if it is separated by some control of flow language. IMO, it would be better to describer a CTE as requiring a control of flow statement, unless it is the first statement (or only statement) in a batch.
I agree 100%. I don't like that either. I never said that I like it, I said it is pretty common.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 7, 2014 at 7:23 am
Sean Lange (3/7/2014)
SQLRNNR (3/6/2014)
I don't like the constant placement of a ; right before a CTE. It's wrong. A CTE does not require a ; in order to execute. You can run a CTE without a ; if it is the first statement in a batch, or if it is separated by some control of flow language. IMO, it would be better to describer a CTE as requiring a control of flow statement, unless it is the first statement (or only statement) in a batch.
I agree 100%. I don't like that either. I never said that I like it, I said it is pretty common.
Yeah, didn't mean for it to come of that way. Just simply wanted to continue along that vein.:-D
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 7, 2014 at 3:54 pm
IIIIIIIIIIIII LIKE IT !!:w00t:
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply