Getting a firstname - 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 2000 Forums

Transact-SQL (2000)

Getting a firstname

Author

Topic

icw
Constraint Violating Yak Guru

378 Posts

Posted - 2007-11-05 : 05:27:57

Hi whats wrong with my query.

_________________________________________________________
SELECT LEFT([contact1.contact], CASE WHEN CHARINDEX(' ', [contact1.contact]) > 0 THEN CHARINDEX(' ', [contact1.contact]) -1 ELSE LEN([contact1.contact]) END)
from contact1
_____________________________________________________________

I am getting an error that says 'Invalid column nanem - contact1.contact.
The column does exist and if I run the query with no prefix i.e.
-----------------------------------------------------------
SELECT LEFT([contact], CASE WHEN CHARINDEX(' ', [contact]) > 0 THEN CHARINDEX(' ', [contact]) -1 ELSE LEN([contact]) END)
from contact1
------------------------------------------------------------

Then it works fine, but the problem that this is only a part of the query and I am joining with another in the query. the second table aslo has a column called contact.

Thanks
ICW

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-11-05 : 05:29:48

Can you post the joined query?

Madhivanan

Failing to plan is Planning to fail

icw
Constraint Violating Yak Guru

378 Posts

Posted - 2007-11-05 : 05:47:35

select c1.accountno,c1.contact,LEFT([c1.contact], CASE WHEN CHARINDEX(' ', [c1.contact]) > 0 THEN CHARINDEX(' ', [c1.contact]) -1 ELSE LEN([c1.contact]) END),c1.lastname, c1.company, contsupp.contsupref from contact1 as c1, contsupp where c1.accountno = contsupp.accountno and contsupp.contact = 'E-mail Address' and contsupp.zip like '_1__%'

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-11-05 : 07:07:39

Try

select c1.accountno,c1.contact,LEFT([c1.contact], CASE WHEN CHARINDEX(' ', [c1.contact]) > 0 THEN CHARINDEX(' ', [c1.contact]) -1 ELSE LEN([c1.contact]) END),c1.lastname, c1.company, contsupp.contsupref from contact1 as c1 inner join contsupp cp on c1.accountno = cp.accountno where cp.contact = 'E-mail Address' and cp.zip like '_1__%'

Madhivanan

Failing to plan is Planning to fail

icw
Constraint Violating Yak Guru

378 Posts

Posted - 2007-11-05 : 07:26:57

Sorry..Still gives me
Invalid column name 'c1.contact'.

icw
Constraint Violating Yak Guru

378 Posts

Posted - 2007-11-05 : 10:45:51

couldn't figure it out, so I made a view.
I often do that when i get stuck...is that cheating??

Koji Matsumura
Posting Yak Master

141 Posts

Posted - 2007-11-05 : 21:30:37

[c1.contact] should be [c1].[contact]