[Solved] COALESCE in queries

[ukphoneguy]

Hi all,

My first post but been lurking around the forum for awhile and found it a invaluable help.

I am attempting to create a database which will eventually export CSV for use in a woocommerce import.

I have stuck a problem with the following:

My query generates image-main, Image_002, Image_003,Image_004 however there is not always an image to show. I have worked out how to use COALESCE as below

COALESCE("image-main" || '|' ,'') || COALESCE("image_002"|| '|' ,'') as "image"

however...... the import requires a pipe | between images ie: "image|Image_002|Image_003!Image_004"

I thought it would be best to place the pipe in front of the next image, if it exists, but can't for the life of me get the syntax correct.

I respectfully request help from the gugru's

Cheerz

[Sliderule]

ukphoneguy:

Welcome to the OpenOffice / LibreOffice Base forum.

I think, if I understand you correctly, there is a way to accomplish what you want . . . using a few 'tricks'.

Explanation:

1. To use words, to attempt to explain what I want to do, so you can get the results you want . . . I will use a few additional functions:
   
   1. SPACE - SPACE(count) returns a string consisting of count spaces
   2. RTRIM - RTRIM(s) removes all trailing spaces
   3. REPLACE - REPLACE(s,replace,s2) replaces all occurrences of replace in s with s
2. So . . . in your COALESCE statement . . . rather than using the '|' . . . I want to start with SPACE(5). Next, use RTRIM to remove all of the trailing spaces. And, lastly, using REPLACE to change all occurrences of SPACE(5) to your desired '|'.

So, as you wrote it, change:

Code: Select all

COALESCE("image-main" || '|' ,'') || COALESCE("image_002"|| '|' ,'') as "image"

to

Code: Select all

REPLACE( RTRIM( COALESCE("image-main" || SPACE(5) ,'') || COALESCE("image_002" || SPACE(5) ,'') ), SPACE(5),'|') as "image"

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

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit buton top right) if this isue has ben resolved.

[Villeroy]

I am attempting to create a database which will eventually export CSV for use in a woocommerce import.

Depending on the type of database, GROUP_CONCAT could do this job for you. This function is not part of HSQLDB 1.8 which is the standard database engine when you create one in Base.

MySQL documentation: https://dev.mysql.com/doc/refman/5.0/en ... oup-concat
HSQL 2: http://www.hsqldb.org/doc/2.0/guide/dat ... gate_funcs

[ukphoneguy]

@Sliderule

You are a god among men! Whilst I do not fully comprehend your solution... it works a treat THANK YOU. I will dissect this in the hope of truly understanding it as I do not like to be beaten by resorting to copy ans paste.

@Villeroy

Thank you for the MYSQL suggestion. I am on a steep learning curve here!

Cheerz

[Sliderule]

Just as an FYI . . . another way to accomplish the same result . . . and . . . perhaps easier to understand and enter, is to use the TRIM function:

Code: Select all

TRIM( TRAILING '|' FROM COALESCE("image-main" || '|' ,'') || COALESCE("image_002"|| '|' ,'') ) as "image"

Explanation: The above using the HSQL function TRIM . . . and . . . the key word . . . TRAILING to tell it to only TRIM ( remove ) from the end, any occurrences of '|' if they exist.

Glad I could help.

Sliderule

[ukphoneguy]

Dam why didn't I think of that.

Created a complex problem for myself without looking for a simplistic answer

THANK YOU once again.

[DACM]

...the import requires a pipe | between images ie: "image|Image_002|Image_003|Image_004"

I thought it would be best to place the pipe in front of the next image, if it exists...

That should work as well...assuming "image-main" precedes any additional images:

Code: Select all

COALESCE("image-main",'') || COALESCE('|' || "image_002",'') || COALESCE('|' || "image_003",'') || COALESCE('|' || "image_004",'') as "image"