February 27, 2010 at 1:00 am
Hi,
What I need is a way to run different CTE based on the condition. Something like this:
WITH CTE_1 AS (SELECT * FROM test1) -- both test1 and test2 have different table and data type structure.
, CTE_2 AS (SELECT * FROM test2) -- so both the CTE's return different data set in structure
IF <condition A>
SELECT FROM CTE_1
ELSE
SELECT * FROM CTE_2
But since, CTE's must be followed by a SELECT, INSERT., etc statements only, I tried to use SELECT CASE statement, s given above.
DECLARE @r INT
SELECT @r = 1 -- @r is a parameter, so the value could be anything.
;WITH CTE_1 AS (select * from test1), -- both test1 and test2 have different table and data type structure.
CTE_2 AS (select * from test2) -- so both the CTE's return the different data set in structure
SELECT CASE @r
WHEN 1 THEN (SELECT * FROM CTE_1)
WHEN 2 THEN (SELECT * FROM CTE_2)
ELSE 0
END
Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'SELECT'.
How do I do this, other than defining CTE's twice.
thanks,
_Ub
February 27, 2010 at 2:51 am
-- Table 1
DECLARE @T1
TABLE (A INT NOT NULL);
-- Table 2
DECLARE @T2
TABLE (A INT NOT NULL);
-- Sample data for table 1
INSERT @T1 (A) VALUES (1);
INSERT @T1 (A) VALUES (2);
INSERT @T1 (A) VALUES (3);
-- Sample data for table 2
INSERT @T2 (A) VALUES (4);
INSERT @T2 (A) VALUES (5);
INSERT @T2 (A) VALUES (6);
-- Condition variable : change it to see what happens
DECLARE @Value INTEGER;
SET @Value = 100;
-- CTE query
WITH T1 AS (SELECT * FROM @T1),
T2 AS (SELECT * FROM @T2)
SELECT A
FROM T1
WHERE @Value = 100
UNION ALL
SELECT A
FROM T2
WHERE @Value <> 100;
I have no idea what you are trying to do here - but I can say there is almost certainly a much better way.
Please, explain what you are trying to do, and why.
By the way, it took me all of two minutes to type that sample code. You will get higher quality answers faster in future if you include something similar to illustrate your question and make it easy for people to understand and work with.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
February 27, 2010 at 5:28 am
is this?
declare @test1 table(a int)
declare @test2 table(a int)
insert into @test1(a)values(1)
insert into @test1(a)values(2)
insert into @test1(a)values(3)
insert into @test1(a)values(4)
insert into @test2(a)values(10)
insert into @test2(a)values(20)
DECLARE @r INT
SELECT @r = 2
;WITH sam AS (
select 1 as r, * from @test1
union all
select 2 as r, * from @test2 )
SELECT
1
, *
from
sam
where
r = @r
columns must be equal in both tables
I Have Nine Lives You Have One Only
THINK!
February 27, 2010 at 10:02 am
Thanks Paul.
I modified my initial question to make more sense. See if it has enough information to better convey the problem.
thanks,
_Ub
February 27, 2010 at 11:56 am
If number of columns and data type would be identical for table 1 and table2 I guess Pauls approach would be the way to go.
But that doesn't seem to be the case.
If you insist to write the query just once you could use dynamic SQL.
But I vote against it. To me the business case itself isn't clear at all: why would you use the same query to return totally different data?
DECLARE @Variable sysname;
DECLARE @SQLString nvarchar(500);
SET @SQLString =
N'SELECT * FROM ' + @Variable;
set @Variable = 'Table1'
EXECUTE sp_executesql @SQLString
set @Variable = 'Table2'
EXECUTE sp_executesql @SQLString
February 27, 2010 at 7:26 pm
lmu92 (2/27/2010)
If number of columns and data type would be identical for table 1 and table2 I guess Pauls approach would be the way to go.
No, it's a daft idea. I just posted it to show that it is technically possible.
lmu92 (2/27/2010)
To me the business case itself isn't clear at all: why would you use the same query to return totally different data?
Exactly. UB, you should rethink this approach - it makes no sense at all. Again, I would encourage you to explain why you think this is necessary. There will be a better way. Step back from it for a second, and let us help.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
February 27, 2010 at 9:08 pm
Thanks guys. I greatly appreciate your input.
I am not sure, if I can explain the business case in the space available here. Its pretty complicated. But I can try to explain the technical requirement that could solve the problem at hand.
We have a base query (a complicated one), that returns, lets say, 5 columns. Lets say: Columns 1, Column 2.... Column 5. Of these Column 1...3 are just ID's. But Column 4 and Column 5 are codes. So, I use CTE's to get different data sets based of "GROUP BY " from the base query.
Ex:
Lets say, the base query returns a data set something like this:
SELECT * FROM BaseQuery
Column1 Column2 Column3 Column4 Column5
And CTEs are defined as follows:
;WITH CTE_1 AS
(
SELECT Column1, Column2, Column3, Column4
FROM BaseQuery
WHERE <some conditions>
GROUP BY Column1, Column2, Column3, Column4
),
CTE_2 AS
(
SELECT Column1, Column2, Column3, Column5
FROM BaseQuery
WHERE <some conditions>
GROUP BY Column1, Column2, Column3, Column5
)
So now, depending on a parameter, lets say @r, I execute different queries:
Something like this (actual query is 200 lines long with multiple UNIONS, GROUP BYs and other neat stuff. 200 lines not including SELECT column list):
IF @r = <some value>
SELECT ....
FROM
..... 200 lines of JOINs and UNIONs....
LEFT OUTER JOIN CTE_1
ON <some condition>
..... more GROUP BY''s .... and more UNIONs
ELSE
SELECT ....
FROM
..... 200 lines of JOINs and UNIONs....
LEFT OUTER JOIN CTE_2
ON <some condition>
..... more GROUP BY''s .... and more UNIONs
Obviously, this gives an error, as CTEs must be followed by SELECT, INSERT and other statements only, not IF clause
So, I thought, I could trick Sql Server and use CASE statement with in a SELECT and get away
SELECT CASE @r
WHEN <some value> THEN <put the 200 lines of query with CTE_1 here>
WHEN <some value> THEN <put the 200 lines of query with CTE_2 here>
ELSE <other query>
END
But, unfortunately, this is not allowed either. Sql Server is smarter that I was 😀
I am not sure, if it makes sense or not. But that's the problem. So, the way I got around to this is by defining CTE's multiple times. Once with in each IF....ELSE scope
IF @r = <some value>
; WITH CTE_1 AS (.....define CTE_1)
SELECT ....
FROM
..... 200 lines of JOINs and UNIONs....
LEFT OUTER JOIN CTE_1
ON <some condition>
..... more GROUP BY''s .... and more UNIONs
ELSE
WITH CTE_2 AS (.....define CTE_2)
SELECT ....
FROM
..... 200 lines of JOINs and UNIONs....
LEFT OUTER JOIN CTE_2
ON <some condition>
..... more GROUP BY''s .... and more UNIONs
And it works. That's all I care, at this point. I am so tired I do not care any more.
thanks for all your help guys,
_Ub
February 27, 2010 at 10:48 pm
--
-- Use a view instead of the CTE:
--
CREATE VIEW dbo.BaseData
WITH SCHEMABINDING
AS
SELECT Column1, Column2, Column3, Column4, Column5
FROM --
-- Complex base query goes here
--
GO
--
-- *** OR ***, for a parameterized view:
--
CREATE FUNCTION dbo.BaseData
(
@parameter1 INTEGER,
@parameter2 VARCHAR(50)
)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
SELECT Column1, Column2, Column3, Column4, Column5
FROM --
-- Complex base query goes here
--
WHERE -- Conditions involving parameters go here
-- e.g. "Column2 BETWEEN 50 AND @parameter1"
GO
DECLARE @r INTEGER;
IF @r = 1
BEGIN
SELECT Column1, Column2, Column3, Column4
FROM dbo.BaseData
WHERE --
-- Conditions go here
--
GROUP BY
Column1, Column2, Column3, Column4;
END
ELSE IF @r = 2
BEGIN
SELECT Column1, Column2, Column3, Column4, Column5
FROM dbo.BaseData
WHERE --
-- Conditions go here
--
GROUP BY
Column1, Column2, Column3, Column4, Column5;
GO
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
February 28, 2010 at 3:10 am
A similar approach to Pauls VIEW concept, but a different way to query the results:
You could insert the result set of your CTE (which would become a normal SELECT statement) into a temp table and use CASE to get the different columns.
SELECT Column1, Column2, Column3, Column4, Column5
INTO #intermediate
FROM complex query
SELECT Column1, Column2, Column3,
CASE @r
WHEN 1 THEN Column4 ELSE Column5 END as MixedResult
FROM #intermediate
February 28, 2010 at 9:56 am
May 17, 2016 at 1:39 pm
Veteran,
this is probably not the best way to use a CTE, a CTE is means to be re-usable across the T-SQL query and it is not meant to switch tables and columns on the fly, for this last it is better to use other techniques, like conditions with an IF statement or just a dynamic query:
-- Sub Query Technique:
CREATE PROCEDURE MYProcedure
@Type = 1
IF @Type = 1
BEGIN
SELECT * FROM TABLE 1
END
ELSE IF @Type = 2
BEGIN
SELECT * FROM TABLE 2
END
ELSE
BEGIN
SELECT * FROM TABLE 3
END
Regards,
May 17, 2016 at 2:52 pm
rivcomp (5/17/2016)
Veteran,this is probably not the best way to use a CTE, a CTE is means to be re-usable across the T-SQL query and it is not meant to switch tables and columns on the fly, for this last it is better to use other techniques, like conditions with an IF statement or just a dynamic query:
-- Sub Query Technique:
CREATE PROCEDURE MYProcedure
@Type = 1
IF @Type = 1
BEGIN
SELECT * FROM TABLE 1
END
ELSE IF @Type = 2
BEGIN
SELECT * FROM TABLE 2
END
ELSE
BEGIN
SELECT * FROM TABLE 3
END
Regards,
First, you do realize that this thread is over six years old and the OP hasn't been active in over a year, so he is very unlikely to see this.
Second, the OP already stated that he had decided to use an approach that was very similar to the approach that you outlined here.
Finally, I would disgree that CTEs are meant to be reusable. CTEs are meant to be a better version of derived tables, and being reusable is only one minor way in which they are better. Being reusable is not a defining characteristic of a CTE.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply