Column 'EMPLOYEE.EMP_ID' is invalid in the select

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 2005 Forums

Transact-SQL (2005)

Column 'EMPLOYEE.EMP_ID' is invalid in the select

Author

Topic

lovehui
Yak Posting Veteran

60 Posts

Posted - 2009-04-07 : 19:15:16

[code]select E.EMP_ID as EMP_ID,
E.LST_NM as LAST_NAME,
' ' as REASON_TYPE,
SYSTEM_TOTAL = isnull((select TH.AMT),0),
TENDER_TOTAL = isnull(sum(LN.EXT_PRC),0),
DISCREPENCY =isnull((select TH.AMT)+(sum(LN.EXT_PRC)),0),
REASON_TYPE = ' ',
REASON = ' ',
TT.TND_DESCR as TENDER_TYPE
from TENDER_ON_HAND TH
join TRANS_HEADER TR on TH.TILL_ID = TR.TILL_ID
join TENDER TT on TH.TND_CD=TT.TND_CD
join EMPLOYEE E on TR.EMP_ID = E.EMP_ID
join LN_DETAIL LN on TR.TRAN_ID = LN.TRAN_ID
where TR.TRAN_CD in (1,2,21,22) and BUS_DT = 'Mar 31 2009 12:00AM' Group By TT.TND_CD[/code]

Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-04-07 : 19:18:26

What is your question?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

lovehui
Yak Posting Veteran

60 Posts

Posted - 2009-04-07 : 19:43:35

I get an error

'EMPLOYEE.EMP_ID' is invalid in the select ...

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-04-07 : 19:49:50

It's because you are only grouping by TT.TND_CD and that column as well as others aren't used in an aggregate function. To fix this, you'll need to decide which row you want to display. Since you are using SQL Server 2005, you can get around this via the ROW_NUMBER() function.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2009-04-07 : 19:55:13

[code]
select
E.EMP_ID
,E.LST_NM as LAST_NAME
,' ' as REASON_TYPE
,Coalesce(TH.AMT,0) as SYSTEM_TOTAL
,sum(LN.EXT_PRC) as TENDER_TOTAL
,TH.AMT + sum(LN.EXT_PRC) as DISCREPENCY
,' ' as REASONTYPE
,' ' as REASON
,TT.TND_DESCR as TENDER_TYPE
from
TENDER_ON_HAND TH
Inner join
TRANS_HEADER TR
on TH.TILL_ID = TR.TILL_ID
Inner join
TENDER TT
on TH.TND_CD=TT.TND_CD
Inner join
EMPLOYEE E
on TR.EMP_ID = E.EMP_ID
Inner join
LN_DETAIL LN
on TR.TRAN_ID = LN.TRAN_ID
where
TR.TRAN_CD in (1,2,21,22)
and BUS_DT = 'Mar 31 2009 12:00AM'
Group By TT.TND_CD,E.EMP_ID,E.LST_NM,TH.AMT,TT.TND_DESCR

[/code]

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-04-07 : 20:05:00

quote:

Originally posted by Vinnie881


select 
 E.EMP_ID   
,E.LST_NM as LAST_NAME
,' ' as REASON_TYPE
,Coalesce(TH.AMT,0) as SYSTEM_TOTAL
,sum(LN.EXT_PRC) as TENDER_TOTAL
,TH.AMT + sum(LN.EXT_PRC) as DISCREPENCY
,' ' as REASONTYPE
,' ' as REASON
,TT.TND_DESCR as TENDER_TYPE
from  
	TENDER_ON_HAND TH
Inner join 
	TRANS_HEADER TR 
on		TH.TILL_ID = TR.TILL_ID
Inner join 
	TENDER TT 
on		TH.TND_CD=TT.TND_CD
Inner join 
	EMPLOYEE E 
on		TR.EMP_ID = E.EMP_ID
Inner join 
	LN_DETAIL LN 
on		TR.TRAN_ID =    LN.TRAN_ID
where 
		TR.TRAN_CD in (1,2,21,22)  
and		BUS_DT = 'Mar 31 2009 12:00AM'  
Group By TT.TND_CD,E.EMP_ID,E.LST_NM,TH.AMT,TT.TND_DESCR

That might eliminate the error, but it probably does not produce the correct results.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

lovehui
Yak Posting Veteran

60 Posts

Posted - 2009-04-07 : 20:29:49

Yes. It returns an empty.

I don't know how to achieve it.

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2009-04-08 : 00:48:43

Please post some sample data and sesited results.

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources