SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

kt
Yak Posting Veteran

75 Posts

Posted - 09/11/2013 :  15:10:31  Show Profile  Reply with Quote
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

Edited by - kt on 09/11/2013 15:14:10

James K
Flowing Fount of Yak Knowledge

3565 Posts

Posted - 09/11/2013 :  15:17:50  Show Profile  Reply with Quote
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'
Go to Top of Page

kt
Yak Posting Veteran

75 Posts

Posted - 09/11/2013 :  15:24:30  Show Profile  Reply with Quote
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'
Go to Top of Page

kt
Yak Posting Veteran

75 Posts

Posted - 09/11/2013 :  15:30:00  Show Profile  Reply with Quote
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
Go to Top of Page

MuMu88
Aged Yak Warrior

547 Posts

Posted - 09/11/2013 :  15:31:10  Show Profile  Reply with Quote
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'


Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3565 Posts

Posted - 09/11/2013 :  15:44:25  Show Profile  Reply with Quote
Thanks MuMu! Sorry kt!!

Can I get away by claiming that what Mumu posted is what I really meant?
Go to Top of Page

kt
Yak Posting Veteran

75 Posts

Posted - 09/11/2013 :  17:12:37  Show Profile  Reply with Quote
worked thank you for of your help James and MuMu , have a great day
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000