OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

[SOLVED] Using a clause in a query

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base
View previous topic :: View next topic  
Author Message
NiksaVel
General User
General User


Joined: 25 Jan 2008
Posts: 14
Location: Croatia

PostPosted: Sun Nov 30, 2008 8:50 am    Post subject: [SOLVED] Using a clause in a query Reply with quote

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 Smile thanks for all the help in advance...


Last edited by NiksaVel on Sun Nov 30, 2008 11:11 am; edited 1 time in total
Back to top
View user's profile Send private message Visit poster's website Yahoo Messenger MSN Messenger
Sliderule
Super User
Super User


Joined: 29 May 2004
Posts: 2499
Location: 3rd Rock From The Sun

PostPosted: Sun Nov 30, 2008 9:22 am    Post subject: Reply with quote

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.
Code:
CASEWHEN( LENGTH( COALESCE("Person2",'') ) >= 1, "Person1" || ', ' || "Person2",  "Person1" )

I hope this helps, please be sure to let me / us know.

Sliderule

Thanks to add [Solved] in your first post title (edit button) if your issue has been fixed / resolved.[/list]
Back to top
View user's profile Send private message
NiksaVel
General User
General User


Joined: 25 Jan 2008
Posts: 14
Location: Croatia

PostPosted: Sun Nov 30, 2008 11:11 am    Post subject: Reply with quote

Wonderful!!!!


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

And yes... I am using HSQL DB


Thank you very much!
Back to top
View user's profile Send private message Visit poster's website Yahoo Messenger MSN Messenger
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group