December 14, 2021 at 3:11 am
I have such a situation. I basically need to put my whole code into a second temp table(?) to be able to select from it. The code inside works just fine I just cannot understand how to basically create a bigger temp table that will absorb the first one and the previous select and later allow select one more time.
This is what I have right now
WITH t AS
( Select
p.[PDITM#], l.SGDWHS, p.PDCHN, p.PDWEDT, Sum(p.PDQSLD) AS 'Sum',
case when p.PDCHN = 'DH' then p.PDQSLD else 0 end as DHSLD,
case when p.PDCHN = 'WM' then p.PDQSLD else 0 end as WMSLD,
case when p.PDCHN = 'MJ' then p.PDQSLD else 0 end as MJSLD,
case when p.PDCHN = 'BJ' then p.PDQSLD else 0 end as BJSLD
FROM [Repit].[LEVYDTA].[POSDTLM] p
LEFT OUTER JOIN [Repit].[LEVYDTA].[LDSSGEN] l
ON p.[PDAGY]=l.[SGAGY] AND p.[PDCHN] = l.[SGCHN] AND p.[PDSTR] = l.[SGSTR]
WHERE p.[PDQSLD] > 0 AND p.[PDAGY] != 15
AND l.[SGDWHS] IN (01, 21, 30, 40, 02)
AND CONVERT(DATE, CONVERT(CHAR(8), p.PDWEDT )) = cast(DATEADD(dd, DATEPART(DW,GETDATE())*-1, GETDATE()) as date)
GROUP BY p.[PDITM#], l.[SGDWHS], p.[PDCHN], p.[PDWEDT], p.[PDQSLD]
)
select t.[PDITM#] as [PDITM#],
t.SGDWHS as [SGDWHS],
SUM(t.Sum) as [PDQSLD],
SUM(t.DHSLD) AS [DHSLD],
SUM(t.WMSLD) AS [WMSLD],
SUM(t.MJSLD) AS [MJSLD],
SUM(t.BJSLD) AS [BJSLD],
SUM(t.Sum) - (SUM(t.DHSLD) + SUM(t.WMSLD) + SUM(t.MJSLD) + SUM(t.BJSLD)) as OTHRSLD
from t
GROUP BY t.PDITM#, t.SGDWHS
ORDER BY t.PDITM#, t.SGDWHS
I need to select such fields from the upper table
SELECT t.PDITM#, t.SGDWHS,
CAST((t.DHSLD/t.Sum) * 100 as DECIMAL (18,2)) AS 'DHPCT',
CAST((t.WMSLD/t.Sum) * 100 as DECIMAL (18,2)) AS 'WMPCT',
CAST((t.MJSLD/t.Sum) * 100 as DECIMAL (18,2)) AS 'MJPCT',
CAST((t.BJSLD/t.Sum) * 100 as DECIMAL (18,2)) AS 'BJPCT',
CAST(((t.Sum - ( t.DHSLD + t.WMSLD + t.MJSLD + t.BJSLD))/t.Sum) * 100 as DECIMAL (18,2)) AS 'OTHRPCT'
FROM second temp table
I will appreciate if someone can let me know how it may be possible. I have spent time trying to figure it out by myself, but there is not much info when you need to make multiple temp tables and selecting them.
December 14, 2021 at 3:56 am
What do you mean "second temp table"? You can define multiple CTEs inside your expression, and then just use them both/all in the final one.
WITH cte1 (col1, col2, col3)
AS (SELECT...),
cte2(colA, colB, colC)
AS (SELECT...)
SELECT colA, col1,...
FROM cte1 INNER JOIN cte2 ON col3 = ColC;
December 14, 2021 at 10:04 am
There are precisely zero temp tables contained in your code.
CREATE TABLE #T (SomeCol INT)
, for example, is how test tables are created.
If you want to use them in your code, it requires significant change.
pietlinden has already shown how to use cascading CTEs, on the other hand.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
December 14, 2021 at 1:56 pm
Also, a Common Table Expression, CTE, is not a TABLE, but an EXPRESSION. That label causes so many problems for so many people. You're not defining a temporary storage space. You're defining an expression, a query, that can be used like a table. But it's not a table. Also, the issue you're hitting is that you're attempting to define the CTE and then use it in two queries. You can't. A CTE can only be used in the query where it is defined. This is because, it's an expression, not a table. Read more here, especially the Guidelines section.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply