Updating on the fly a CTE - 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

SQL Server 2008 Forums

Transact-SQL (2008)

Updating on the fly a CTE

Author

Topic

Ciupaz
Posting Yak Master

232 Posts

Posted - 2012-09-07 : 06:02:34

Hello all,
I'm having this CTE:

SELECT EventiInOutParMinTec_ID,
CASE WHEN EventiInOutParMinTec_ID = 1053833 THEN '2012-06-14 20:31:00.000'
ELSE DataOra
END AS DataOra,
Gruppi_ID, CodiceEvento,
ROW_NUMBER() OVER (ORDER BY DataOra) rownum FROM app.T_EventiInOut
WHERE Gruppi_ID = 462

The problem is that I have to Update "on the fly" my DataOra field of the CTE
and I get the wrong order at the end of the Select, because it uses the "old" DataOra field.

How can I solve this problem?

Thanks a lot.

Luigi

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-09-07 : 06:54:13

I can't see a CTE.
I can't see an ORDER BY at the end of the SELECT.

The only thing I can see is: the CASE expression should be repeated in the OVER() part.

Too old to Rock'n'Roll too young to die.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-07 : 06:54:58

Can you show the entire query including the "with cte as" statement and the update statement? Without that it is hard to figure out what the issue might be.

Ciupaz
Posting Yak Master

232 Posts

Posted - 2012-09-07 : 07:45:19

Here it is:

WITH CTE (EventiInOutParMinTec_ID, DataOra, Gruppi_ID,
CodiceEvento, rownum) AS (
SELECT
EventiInOutParMinTec_ID,
CASE WHEN EventiInOutParMinTec_ID = 1053833 THEN '2012-06-14 20:31:00.000'
ELSE DataOra
END AS DataOra,
Gruppi_ID, CodiceEvento,
ROW_NUMBER() OVER (ORDER BY DataOra) rownum FROM app.T_EventiInOut_Parallelo_MinTec
WHERE Gruppi_ID = 462
ORDER BY DataOra
)
SELECT Curr.EventiInOutParMinTec_ID CurrentMinTecID, Curr.DataOra CurrentDataOra,
Curr.CodiceEvento CurrentCodiceEvento,
ISNULL(Precedente.EventiInOutParMinTec_ID,0) PreviousMinTecID, Precedente.DataOra PreviousDataOra,
ISNULL(Precedente.CodiceEvento,0) PreviousCodiceEvento, ISNULL(Successivo.EventiInOutParMinTec_ID,0)
NextMinTecID, Successivo.DataOra NextDataOra, ISNULL(Successivo.CodiceEvento,0) NextCodiceEvento
FROM CTE Curr LEFT JOIN CTE Precedente
ON Curr.rownum = Precedente.rownum+1
LEFT JOIN CTE Successivo
ON Curr.rownum = Successivo.rownum-1
WHERE Curr.EventiInOutParMinTec_ID = 1053833 AND Curr.Gruppi_ID = 462

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-07 : 07:57:30

Use the same expression in the order by clause in row_number function. Also, no need (and cannot)use order by clause within the CTE. See changes in red below:

WITH CTE (EventiInOutParMinTec_ID, DataOra, Gruppi_ID, 
CodiceEvento, rownum) AS (
SELECT 
EventiInOutParMinTec_ID, 
CASE WHEN EventiInOutParMinTec_ID = 1053833 THEN '2012-06-14 20:31:00.000'
ELSE DataOra
END AS DataOra, 
Gruppi_ID, CodiceEvento, 
ROW_NUMBER() OVER (ORDER BY 
CASE WHEN EventiInOutParMinTec_ID = 1053833 THEN '2012-06-14 20:31:00.000'
ELSE DataOra
END
) rownum FROM app.T_EventiInOut_Parallelo_MinTec 
WHERE Gruppi_ID = 462 
ORDER BY DataOra
) 
SELECT Curr.EventiInOutParMinTec_ID CurrentMinTecID, Curr.DataOra CurrentDataOra, 
Curr.CodiceEvento CurrentCodiceEvento, 
ISNULL(Precedente.EventiInOutParMinTec_ID,0) PreviousMinTecID, Precedente.DataOra PreviousDataOra, 
ISNULL(Precedente.CodiceEvento,0) PreviousCodiceEvento, ISNULL(Successivo.EventiInOutParMinTec_ID,0) 
NextMinTecID, Successivo.DataOra NextDataOra, ISNULL(Successivo.CodiceEvento,0) NextCodiceEvento 
FROM CTE Curr LEFT JOIN CTE Precedente 
ON Curr.rownum = Precedente.rownum+1 
LEFT JOIN CTE Successivo 
ON Curr.rownum = Successivo.rownum-1 
WHERE Curr.EventiInOutParMinTec_ID = 1053833 AND Curr.Gruppi_ID = 462

Ciupaz
Posting Yak Master

232 Posts

Posted - 2012-09-07 : 10:30:24

Perfect, thank you very much SunitaBeck.

Luigi

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-07 : 10:37:57

Quite welcome - glad to be of help;

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources