temp table columns not showing up in CTE
-
I'm new to the development side of the house and stuck on a CTE issue.
In the CTE data is pulled from a temp table. There was a request for more columns, so I added them.
Everything works, but the columns don't show up in my CTE results? What am I overlooking here?
Thank you for any insight.
-
Well, it should work, but without seeing your code, we cannot diagnose your issue. Try running this, for example
DROP TABLE IF EXISTS #SomeData;
CREATE TABLE #SomeData
(
A INT
,b INT
);
INSERT #SomeData
(
A
,b
)
VALUES
(1, 2);
WITH cte1
AS (SELECT sd.A
,sd.b
FROM #SomeData sd)
SELECT cte1.A
,cte1.b
FROM cte1;
ALTER TABLE #SomeData ADD C INT;
WITH cte2
AS (SELECT sd.A
,sd.b
,sd.c
FROM #SomeData sd)
SELECT cte2.A
,cte2.b
,cte2.c
FROM cte2; -
Right after the final ), add the following and see what happens:
SELECT d.Code
,d.CoName
,d.TerName
,d.RegName
,d.DisplayName
,d.isCurrentYearActivation
,d.AELevel
,d.ContractNumber
,d.Count_Panel
,d.Amount_CurrentYear
,d.Amount_YTD
,d.Amount_Range
FROM d; -
I tried that and the columns still don't show up. It must be deeper in this mess. I'm still looking and again I appreciate your input.
- DBA_318 wrote:
I tried that and the columns still don't show up. It must be deeper in this mess. I'm still looking and again I appreciate your input.
There must be more going on than you have shown. You need to find the final SELECT … ie, the one for which you are seeing the results.
-
Not sure who marked your last post as spam, it wasn't me.
That is a mega query. Not one for someone who is 'new to the development side', in my opinion
Have you been able to work out exactly which SELECT statement is giving you the results that you are seeing?
-
Your final select in that statement is
select contractnumber from d group by contractnumber having count(*)>1
Those added columns do not appear in that
SELECT, so they do not appear in your results.NOTE: When posting code, you should use the
{;} Codebutton so that the code is formatted properly.Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA -
When you define your CTE, you have to specify the column names...
WITH cteExample (FirstName, LastName, IDNo)
AS
(
SELECT fName, LName, NotMySSN
FROM tableX
)
SELECT FirstName, LastName
FROM cteExample
WHERE IDNo > 10;If you don't include any columns in the list (in the parentheses), the cte doesn't output any. And the column names don't have to match - but the column order matters.
If I try to refer to the original column names when I query the CTE, the query won't work. (Yes, I'm fully aware that this is a ridiculous case for a CTE) so if I used fName, LName etc... I'd get an error. If you remember that a CTE is basically a temporary view, it makes more sense... the CTE can create new columns, rename old ones, do joins etc... and after all that, you query that "view" that the CTE creates, but you have to do it by querying the CTE and referring to the columns as the CTE has used.
- pietlinden wrote:
When you define your CTE, you have to specify the column names...
WITH cteExample (FirstName, LastName, IDNo)
AS
(
SELECT fName, LName, NotMySSN
FROM tableX
)
SELECT FirstName, LastName
FROM cteExample
WHERE IDNo > 10;If you don't include any columns in the list (in the parentheses), the cte doesn't output any. And the column names don't have to match - but the column order matters.
That's not true at all. I almost never specify my columns names there.
WITH Cal -- No column names specified here
AS
(
SELECT *
FROM (VALUES(CAST('20250401' AS DATE), 'Tuesday', '1')
,('20250402', 'Wednesday', '2')
,('20250403', 'Thursday', '3')
) dt(Cal_Date, Day_Name, Day_Of_Month)
)
SELECT * -- CTE still outputs all the columns.
FROM Cal;Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply