Hello Bryan,

Your solution worked well :-) Thanks!

--- On Sat, 13/2/10, Bryan Pendleton <bpendleton@amberpoint.com> wrote:

From: Bryan Pendleton <bpendleton@amberpoint.com>
Subject: Re: Need help with an sql query
To: "Derby Discussion" <derby-user@db.apache.org>
Date: Saturday, 13 February, 2010, 8:45 PM

> select firstname || case when length(middlename) = 0 then '' else ' '
>   || middlename end || ' ' || lastname as name from studentmaster
>   where name like 'abc d%'
>
> In the above statement, I have tried to give the name 'name' to the combined filed of three columns.

You need an 'end' for the 'case', and you need to use a subquery in
order to reference the field 'name' in your where clause.

Something like this should work:

select * from ( select firstname ||
    case when length(middlename) = 0 then '' else ' ' end  ||
    middlename || ' ' || lastname as name from studentmaster) t
  where t.name like 'William J%';

thanks,

bryan




The INTERNET now has a personality. YOURS! See your Yahoo! Homepage.