Nested CONCAT and COALESCE

Dazzer · January 17, 2020, 2:42am

LO 6.3.3.2, HSQLDB, MacOS Mojave 10.14.6

Trying to define an ALIAS as combining the following:

Guest_Preferred_Name (if it exists) + Guest_Lastname, otherwise Guest_Firstname & Guest_Lastname

Where if Guest_Preferred_Name doesn’t exist then Guest_Firstname is joined to Guest_Lastname

Can use COALESCE ( “Guest_Preferred_Name”, “Guest_Firstname” ) on it’s own which gives me the first part but then when I try and nest that within CONCAT to join to the Guest_Lastname within the same command I get a SYNTAX error from within the Query however somehow I managed to save it prior to this and running the Query without Editing first gives the right outcome

CONCAT( COALESCE ( [Guest_Preferred_Name]; [Guest_Firstname] ) || ', ’ || [Guest_Lastname] )

I’m guessing it’s a missing " or ’ somewhere but if someone could point me in the right direction I would greatly appreciate it

Ratslinger · January 17, 2020, 3:05am

Please, each time when posting you should include OS, specific LO version and with Base the database being used. All these can affect the answer.

Ratslinger · January 17, 2020, 3:21am

@Dazzer,

inbuilt is not a database. Base can use HSQLDB embedded or Firebird embedded - both are delivered with LibreOffice.

Dazzer · January 17, 2020, 4:19am

@Ratslinger Sorry, newbie to LO, just learning right now.

Is there a resource for requesting work on LO to be done for a (small) fee. I’m happy to pay for assistance/guidance and tend to learn better from examples

Ratslinger · January 17, 2020, 4:52am

@Dazzer,

Probably but I have no specifics. This may be difficult using (small) fee as a basis. There are many examples posted here already - problem is knowing what to look for. Another site → AOO Forum has many examples and has much better organization. LO is an off shoot from AOO (development there is way down).

As many of us roam about, please if posting on a site give a bit of time (few days sometimes) for a response. Then if wanted post elsewhere but provide a link in each post stating the cross posting to eliminate multiple effort. It’s rude if not mentioned and have seen questions closed because of this.

Dazzer · January 17, 2020, 5:08am

You helped me a couple of days ago, the dbase is much the same but with a bit of tidying up and I’ve started on reports, if you are happy to look at it and give me a cost for tidying up, fixing a couple of issues and making sure everything works I’d be happy to consider it

Dazzer · January 17, 2020, 5:09am

@Ratslinger

You helped me a couple of days ago, the dbase is much the same but with a bit of tidying up and I’ve started on reports, if you are happy to look at it and give me a cost for tidying up and making sure everything works I’d be happy to consider it

Ratslinger · January 17, 2020, 6:01am

@Dazzer,

Ask as a new question - anything about the Base file & post sample. Will take a look (no promises to outcome) but have no interest in receiving compensation. I answer questions to help others using the knowledge I have from research, trial/error and that which I have also gained from others here & above.

BTW ‘dbase’ is incorrect - that is one of the early (and popular) databases. Base file is better.

Dazzer · January 17, 2020, 4:51pm

@Ratslinger

Hi

I tried asking for help as a new question and it just got deleted for not being a real question ! I realise it was not a tech question, where should I post it ?

Ratslinger · January 17, 2020, 5:12pm

Yes, as this is not what I asked. Regardless I have downloaded the files & deleted the question itself as it is not a proper question for this site. Will review & get back to you on this post in a day or two.

Dazzer · January 17, 2020, 5:22pm

@Ratslinger

Thank you

Ratslinger · January 17, 2020, 10:07pm

@Dazzer,

Have looked close enough at your document and sample. Unfortunately most would not touch this without starting all over. Additionally this would take hundreds of hours to do correctly and someone undertaking this would want monetary compensation (with 25-50% up front) and it would not be small.

HSQLDB embedded in Base is probably the last choice for a database to be used for business. Paper & pencil is safer. Minimally Firebird embedded, and that has its’ own set of nuances. There seems to be no full plan laid out. It should be that the determined end result = what goes into the DB design. It would take many hours of discussion just to get the requirements down.

Don’t see anyone accepting this project under the terms you have expressed. Much bigger project than what you may think.

Ratslinger · January 17, 2020, 3:17am

Hello,

The problem is syntax. Even in your question you change dramatically from:

COALESCE ( "Guest_Preferred_Name", "Guest_Firstname" )

To:

COALESCE ( [Guest_Preferred_Name]; [Guest_Firstname] )

Can’t see any reason for this.

Try:

Select CONCAT( COALESCE ( "Guest_Preferred_Name", "Guest_Firstname" ) || ', ' || "Guest_Lastname" ) From "tbl_Guests"

Dazzer · January 17, 2020, 3:24am

Sorry about the confusion, thank you for helping - that worked great