Issue with Invalid column name

Question

Issue with Invalid column name

mcfarlandparkway

SSCertifiable

Points: 7699
More actions
March 10, 2017 at 6:38 am

#336046

     SELECT DISTINCT
   t.EmpId AS Id,
   t.STDID
   INTO #Temp
   FROM dbo.Employee t

   SELECT DISTINCT
   t.EmpAuditId AS Id,
   t.STDID
    INTO #Temp2
   FROM dbo.EmployeeAudit t
WHERE t.EmpCode IS NOT NULL
   AND EXISTS (SELECT 1 FROM #TEMP t2 WHERE (t.EmpRecordNum = t2.EmpRecordNum AND t.EmpReferenceNum = t2.EmpReferenceNum) AND t2.EmpStatus = 'ZZ')

   These two queries are in the procedure, When i execute the Sp its showing as Invalid column name EmpRecordNum and Invalid column name EmpReferenceNum
But these two columns are added in both the tables Employee and EmployeeAudit
Not sure what is causing this issue? Any idea?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

DesNorton SSC-Insane Points: 24387 More actions · Answer 1

mcfarlandparkway - Friday, March 10, 2017 6:38 AM
SELECT DISTINCT
   t.EmpId AS Id,
   t.STDID
   INTO #Temp
   FROM dbo.Employee t

   SELECT DISTINCT
   t.EmpAuditId AS Id,
   t.STDID
    INTO #Temp2
   FROM dbo.EmployeeAudit t
WHERE t.EmpCode IS NOT NULL
   AND EXISTS (SELECT 1 FROM #TEMP t2 WHERE (t.EmpRecordNum = t2.EmpRecordNum AND t.EmpReferenceNum = t2.EmpReferenceNum) AND t2.EmpStatus = 'ZZ')

   These two queries are in the procedure, When i execute the Sp its showing as Invalid column name EmpRecordNum and Invalid column name EmpReferenceNum
But these two columns are added in both the tables Employee and EmployeeAudit
Not sure what is causing this issue? Any idea?

You are selecting from #TEMP as t2. However, #TEMP only has [Id] and [STDID] as columns.

How to post data/code on a forum to get the best help.

Neil Burton SSC-Insane Points: 23001 More actions · Answer 2

You need to include the two columns in the #Temp table that you reference in the subquery. It doesn't matter that they are present in the permanent tables, they need to be on the temp table before you can reference them.

What Des said 🙂

On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
—Charles Babbage, Passages from the Life of a Philosopher

How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537

Henrico Bekker One Orange Chip Points: 27652 More actions · Answer 3

You are not inserting EmpRecordNum nor EmpReferenceNum nor EmpStatus into #TEMP, so it is not available for insert into #temp2 (t2), which makes it unavailable for your comparison.

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle