db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Dinesh Bajaj <dinesh.ba...@ymail.com>
Subject Need help with an sql query
Date Sat, 13 Feb 2010 07:31:01 GMT

I need help with an sql query that I think involves using the CASE statement. 

The problem description is that a table say 'table1' has three fields viz. firstname, middlename,
and lastname. The first and the third fields are mandatory, and when no value is given for
the 2nd filed, an empty string is inserted by default.

Now, I want to seach on the combination of these three fields such that I can specify the
seach string as 'abc d%' where abc is the firstname and 'd' could be either middlename or

I wrote the following sql statement, but the derby thew an exception. The sql statement is:

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

I hope the above description makes sense and someone is able to help me out.


      The INTERNET now has a personality. YOURS! See your Yahoo! Homepage. http://in.yahoo.com/
View raw message