Invalid Column Name - 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)

Invalid Column Name

Author

Topic

Spica66
Starting Member

23 Posts

Posted - 2012-08-07 : 11:49:42

I am working in SQL Server Management Studio.

In my query, I am retrieving a column from my table "h". It works perfectly.

SELECT c.CLAIMID,
c.NFCUSTOMER,
t.HRVYEARID,
t.HRVBRANDID,
RIGHT(c.WARRANTYNUMBER,6) AS SERIALNUMBER,
w.NFWARRANTYPARTID + ' - ' + p.NFWARRANTYPARTDESCRIPTION AS WCCDESCRIPTION,
w.NFCAUSEDESCRIPTION,
w.NFCORRECTIVECODE,
w.NFCAUSEID,
h.TRANSDATE

FROM dbo.NFWARRANTYCLAIMS AS c

INNER JOIN (SELECT NFCLAIMID,
MAX(TRANSDATE) AS TRANSDATE
FROM NFWARRANTYCLAIMHISTORY
GROUP BY NFCLAIMID
)AS h ON h.NFCLAIMID = c.CLAIMID

If I add another column from the table,

SELECT c.CLAIMID,
c.NFCUSTOMER,
t.HRVYEARID,
t.HRVBRANDID,
RIGHT(c.WARRANTYNUMBER,6) AS SERIALNUMBER,
w.NFWARRANTYPARTID + ' - ' + p.NFWARRANTYPARTDESCRIPTION AS WCCDESCRIPTION,
w.NFCAUSEDESCRIPTION,
w.NFCORRECTIVECODE,
w.NFCAUSEID,
h.NFCLAIMSTATUS,<<<<--Another Column Added Here
h.TRANSDATE

FROM dbo.NFWARRANTYCLAIMS AS c

INNER JOIN (SELECT NFCLAIMID,
MAX(TRANSDATE) AS TRANSDATE
FROM NFWARRANTYCLAIMHISTORY
GROUP BY NFCLAIMID
)AS h ON h.NFCLAIMID = c.CLAIMID

I get the following error:

Msg 207, Level 16, State 1, Line 18
Invalid column name 'NFCLAIMSTATUS'.

I DRAGGED the column onto the page so it HAS to be valid. What gives?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-07 : 12:01:24

you've not selected the column inside derived table thats why the error. include it inside if you want it to be selected.

SELECT c.CLAIMID,
c.NFCUSTOMER, 
t.HRVYEARID,
t.HRVBRANDID,
RIGHT(c.WARRANTYNUMBER,6) AS SERIALNUMBER,
w.NFWARRANTYPARTID + ' - ' + p.NFWARRANTYPARTDESCRIPTION AS WCCDESCRIPTION,
w.NFCAUSEDESCRIPTION,
w.NFCORRECTIVECODE,
w.NFCAUSEID,
h.NFCLAIMSTATUS,<<<<--Another Column Added Here
h.TRANSDATE 

FROM dbo.NFWARRANTYCLAIMS AS c
INNER JOIN NFWARRANTYCLAIMHISTORY h
ON h.NFCLAIMID = c.CLAIMID
INNER JOIN (SELECT NFCLAIMID, 
MAX(TRANSDATE) AS TRANSDATE 
FROM NFWARRANTYCLAIMHISTORY
GROUP BY NFCLAIMID
)AS h1 
ON h1.NFCLAIMID = h.CLAIMID
AND h1.TRANSDATE = h.TRANSDATE

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources