Combine field names in query 1

[oaklanders]

In my Oracle 9i I tried this and it gave me back an error saying fullname is invalid.

select firstname || ' ' || secondname as fullname where fullname like '%Jo%'

This works:

select firstname || ' ' || secondname where firstname || ' ' || secondname like '%Jo%'

Please advise how I can get it working the first attempt working?

 

[oaklanders]

Sorry my error on examples:

select firstname || ' ' || secondname as fullname from myTable where fullname like '%Jo%'


select firstname || ' ' || secondname from myTable where firstname || ' ' || secondname like '%Jo%'

 

[jdemmi]

I don't think you can use the alias of a concatenation in a where clause...this would work though

select firstname || ' ' || secondname as fullname from myTable where firstname || ' ' || secondname like '%Jo%

-- Jason
"It's Just Ones and Zeros

 

[jimirvine]

You would have to put the concatenation into a subquery:

SELECT fullname
FROM (SELECT firstname || ' ' || secondname as fullname 
      FROM yourtable) 
WHERE fullname like '%Jo%';

 

[ChrisHunt]

Or you could do this:

select firstname || ' ' || secondname as fullname where firstname like '%Jo%' or secondname like '%Jo%'

-- Chris Hunt
Webmaster & Tragedian
Extra Connections Ltd

 

[oaklanders]

Thanks

 

[jimirvine]

Glad one of our ideas helped florida