[SOLVED] Using a clause in a query

[NiksaVel]

Hi all,

I need some help with forming a query...

I have a table which among other things has two fields, lets call them person1 and person2.

Person1 is always there (mandatory field) while person2 is not always there.


I need to do some CONCAT to get the final result of "Person1, Person2.
BUT if there is no person2 then only "Person1".

The problem is of course in ', ' ... I need some way to tell the query to do the concat if there is a person2 or just use person1 if there is no person2...


I hope I made some sense here thanks for all the help in advance...

[Sliderule]

NiksaVel:

I will assume you are using the OpenOffice database engine HSQL with this answer. You can confirm this, after opening your database, if at the bottom, on the Status Bar, it says: HSQL database engine.

For a list of built in functions available for HSQL . . . please look at the following links:

1. http://www.hsqldb.org/doc/guide/ch09.html#N1251E
   
2. http://wiki.services.openoffice.org/wiki/Built-in_functions_and_Stored_Procedures

OK, now, in your case, have to handle a few situations, just in case. First, when the LENGTH ( number of characters for the field "Person2" ) is greater than or equal 1 . . . we will 'combine' the fields along with the string ', ' . Note that we will use two | characters to tell it to combine the fields and the string ', ' . COALESCE is used, just in case the value for "Person2" is NULL . . . in which case we want to return a string, but, of zero length ( '' ) . . . that is . . . literal field wrapped by two single quotes . . . length of zero. The CASEWHEN is just like a spreadsheet ( Calc or Excel ) IF function.

While creating your Query, on the 'Field' line, put the following. I would recommend adding an name for it on the 'Alias' line. You can elect to 'sort' on this new field if you desire.

[NiksaVel]

Wonderful!!!!


Works and does exactly what I was hoping it to do!

And yes... I am using HSQL DB


Thank you very much!