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 lastname.
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 columns.
I hope the above description makes sense and someone is able to help me out.