query - 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

General SQL Server Forums

New to SQL Server Programming

query

Author

Topic

kt
Yak Posting Veteran

88 Posts

Posted - 2013-09-11 : 15:10:31

Hi,

this query returned the results below

select RIGHT(t_item,5) as ENo ,REPLACE(t_item,RIGHT(t_item,5),'') as dItem from tcb
where t_item like '%/E%'
---and dItem = 'abc'

Results:
ENo dItem
123 abc
456 defg
789 xyz

but when i add the condition
and dItem = 'abc' then error:Invalid column name 'delItem'.
Thanks

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-09-11 : 15:17:50

You cannot use the aliases you define in your select list (In this case dItem) anywhere else in the query except in the order by clause. So to achieve what you want, you have to reproduce the expression in the where clause as well.

select RIGHT(t_item,5) as ENo ,REPLACE(t_item,RIGHT(t_item,5),'') as dItem from tcb
where t_item like '%/E%'
and RIGHT(t_item,5) as ENo ,REPLACE(t_item,RIGHT(t_item,5),'')  = 'abc'

kt
Yak Posting Veteran

88 Posts

Posted - 2013-09-11 : 15:24:30

tried and got error Incorrect syntax near the keyword 'as'.
where it was point to this line
and RIGHT(t_item,5) as ENo ,REPLACE(t_item,RIGHT(t_item,5),'') = 'abc'

kt
Yak Posting Veteran

88 Posts

Posted - 2013-09-11 : 15:30:00

it is not working either.
i got this working with this
select ENo
from
(

select RIGHT(t_item,5) as ENo ,REPLACE(t_item,RIGHT(t_item,5),'') as dItem from tcb
where t_item like '%/E%'
) x
where dItem ='abc'

thanks all

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-09-11 : 15:31:10

quote:
Originally posted by James K

You cannot use the aliases you define in your select list (In this case dItem) anywhere else in the query except in the order by clause. So to achieve what you want, you have to reproduce the expression in the where clause as well.
select RIGHT(t_item,5) as ENo ,REPLACE(t_item,RIGHT(t_item,5),'') as dItem from tcb
where t_item like '%/E%'
and ~~RIGHT(t_item,5) as ENo ,~~REPLACE(t_item,RIGHT(t_item,5),'') = 'abc'

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-09-11 : 15:44:25

Thanks MuMu! Sorry kt!!

Can I get away by claiming that what Mumu posted is what I really meant?

kt
Yak Posting Veteran

88 Posts

Posted - 2013-09-11 : 17:12:37

worked thank you for of your help James and MuMu , have a great day

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources