Why Can't I Access My Cte After I Used It Once

why can't I access my CTE after I used it once?

In your example code, the CTE only persists for the UPDATE. If you need it to last longer, consider populating a #tempTable or @tableVariable with it, and then UPDATE and DELETE from those.

You may also augment your UPDATE to use an OUTPUT clause, like the following, so you can capture the affected rows. And use them in the DELETE, like here:

set nocount on
DECLARE @Table     table (PK int, col1 varchar(5))
DECLARE @SavedPks  table (PK int)

INSERT INTO @Table VALUES (1,'g')
INSERT INTO @Table VALUES (2,'g')
INSERT INTO @Table VALUES (3,'g')
INSERT INTO @Table VALUES (4,'g')
INSERT INTO @Table VALUES (5,'x')
INSERT INTO @Table VALUES (6,'x')
set nocount off

;WITH MYCTE
AS 
(
  SELECT PK, col1 FROM @Table
)
UPDATE MYCTE
    SET col1='xyz'
    OUTPUT INSERTED.PK
        INTO @SavedPks
    WHERE col1='g'

SELECT 'A',* FROM @Table

DELETE @Table
    WHERE PK IN (SELECT PK  from @SavedPks)

SELECT 'B',* FROM @Table

OUTPUT:

(4 row(s) affected)
     PK          col1
---- ----------- -----
A    1           xyz
A    2           xyz
A    3           xyz
A    4           xyz
A    5           x
A    6           x

(6 row(s) affected)

(4 row(s) affected)

     PK          col1
---- ----------- -----
B    5           x
B    6           x

(2 row(s) affected)

Use one CTE many times

A CTE is basically a disposable view. It only persists for a single statement, and then automatically disappears.

Your options include:

• Redefine the CTE a second time. This is as simple as copy-paste from WITH... through the end of the definition to before your SET.

• Put your results into a #temp table or a @table variable

• Materialize the results into a real table and reference that

• Alter slightly to just SELECT COUNT from your CTE:

.

SELECT @total = COUNT(*)
FROM Players p 
INNER JOIN Teams t 
    ON p.IdTeam=t.Id 
INNER JOIN Leagues l 
    ON l.Id=t.IdLeague
WHERE l.Id=@idleague

How I can use CTE table more then one time

You can not use CTE in more than one query. But alternatively you can use it by making another CTE for Sum or Count purpose from existing CTE and cross join them.

see below query.

With CTETable as
(
   select * from Table
), CTETotal As 
(
   SELECT COUNT(count) 'count' 
   from CTETable
) 
Select * from CTETable 
CROSS JOIN CTETotal

Why can't I use a CTE in my select statement?

As of 2016-04-27 the CTE statement is not supported by Mule.

I had to dig deep for this one.

• I tweeted this question and tagged @MuleSoft and @MuleDev
  
  
  • To which I got the reply:
    
    

    if you haven't already we'd also suggest posting this in the forums - http://forums.mulesoft.com

• I then posted the issue on the Mulesoft Forums
  
  
  • To no avail
• I then logged a support ticket with Mulesoft (case number 00107313)
  
  
  • To which I got the reply:
    
    

    WITH is not currently supported by Mule ESB. Enhancement request(SE-987) has been raised but not implemented yet.
    Engineering team work on JIRAs based on the priorities. If this JIRA is urgent to you, please provide the following information then I will escalate the JIRA for you

why can't I use WITH (Common Table Expressions) in this query?

You don't reference the CTE at all in your "final" select statement. Therefor you can't reference it. Additionally the where condition needs to reference columns not tables.

In your statement max_i is the name of a "table", thus it cannot be used in a where condition.

WITH t_max AS (
   SELECT MAX(i) as max_i FROM test
)
SELECT * 
FROM test 
  CROSS JOIN t_max as t
WHERE t.max_i - test.i < 2 
  AND t.max_i <> test.i;

The cross join doesn't do any harm because we know the CTE (named t_max) will always return only a single row.

Once you can reference the CTE in the final select, the comparison is easy, but to do that you need to provide an alias for the column of the CTE inner select.

Reuse results from SQL Server common table expression

From the WITH common_table_expression manual:

Specifies a temporary named result set, known as a common table expression (CTE). This is derived from a simple query and defined within the execution scope of a single SELECT, INSERT, UPDATE, or DELETE statement.

So, no, you can't extend the scope of the CTE beyond the SELECT statement it was defined in. You will have to store the result in a temporary table or table valued variable if you want to use the result more than once.

When to use Common Table Expression (CTE)

One example, if you need to reference/join the same data set multiple times you can do so by defining a CTE. Therefore, it can be a form of code re-use.

An example of self referencing is recursion: Recursive Queries Using CTE

For exciting Microsoft definitions
Taken from Books Online:

A CTE can be used to:

• Create a recursive query. For more information, see Recursive Queries Using Common Table Expressions.

• Substitute for a view when the general use of a view is not required; that is, you do not have to store the definition in metadata.

• Enable grouping by a column that is derived from a scalar subselect, or a function that is either not deterministic or has external access.

• Reference the resulting table multiple times in the same statement.

How to reference one CTE twice?

You can use commas to create multiple CTEs that references the CTEs Above.

Just to illustrate what I mean:

with recs as (
select 
    *, 
    row_number() over (order by id) as rownum from ......
    ),
counts as (
    select count(*) as totalrows from recs
)
select recs.*,count.totalrows
from recs
cross apply counts 
where rownum between @a and @b .... 

This is not the a good solution.

The best solution I found to have the total count in a CTE without counting the records is described in this article.

DECLARE @startRow INT; SET @startrow = 50;
WITH cols
AS
(
    SELECT table_name, column_name, 
        ROW_NUMBER() OVER(ORDER BY table_name, column_name) AS seq, 
        ROW_NUMBER() OVER(ORDER BY table_name DESC, column_name desc) AS totrows
    FROM [INFORMATION_SCHEMA].columns
)
SELECT table_name, column_name, totrows + seq -1 as TotRows
FROM cols
WHERE seq BETWEEN @startRow AND @startRow + 49
ORDERBY seq

How to create CTE which uses another CTE as the data to further limit?

You can chain 2 (or more) CTE's together.

For example

with ObjectsWithA as
(
  select * from sys.objects
  where name like '%A%'
),
ObjectsWithALessThan100 as
(
  select * from ObjectsWithA
  where object_id < 100
)
select * from ObjectsWithALessThan100;

Or the same example, with more "spelled out" names/aliases:

with ObjectsWithA (MyObjectId , MyObjectName) as
(
  select object_id as MyObjIdAlias , name as MyNameAlias 
  from sys.objects
  where name like '%A%'
),
ObjectsWithALessThan100 as
(
  select * from ObjectsWithA theOtherCte
  where theOtherCte.MyObjectId < 100
)
select lessThan100Alias.MyObjectId , lessThan100Alias.MyObjectName 
from ObjectsWithALessThan100 lessThan100Alias 
order by lessThan100Alias.MyObjectName;

---

Related Topics