Ask Your Question
0

Problem with concatenation [closed]

asked 2014-03-29 02:24:15 +0200

stevehar gravatar image

updated 2015-09-09 20:33:37 +0200

Alex Kemp gravatar image

Why would version 4.2.2.1 of Libreoffice BASE keep giving me the following error when executing the query statement shown below: Error - Invalid use of vertical bars

Statement - SELECT "TRIP_MILES" || ', ' || "VEHICLE_ID" AS "Test" FROM "TripInfo"

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2016-02-20 16:16:12.843766

Comments

Are you using the default HSQL database in embedded configuration?

oweng gravatar imageoweng ( 2014-03-29 10:12:55 +0200 )edit

Yes, I am using the HSQL database in embedded config. Why?

stevehar gravatar imagestevehar ( 2014-03-29 16:40:58 +0200 )edit

Nearly all reports of this error relate to use of an MS Access database, which is why I asked. Which version of HyperSQL are you using? The default one that comes with Base (v1.8.x) or the newer one (v2.x)? It is also worth noting that I cannot find that error message anywhere in the LO Base or HyperSQL source code, so it is probably a JDBC/SDBC message. Do you know which you are using? JDBC is preferred.

oweng gravatar imageoweng ( 2014-03-30 00:13:34 +0200 )edit

2 Answers

Sort by » oldest newest most voted
0

answered 2014-03-30 03:22:05 +0200

frofa gravatar image

updated 2014-03-30 03:23:53 +0200

Although your SQL statement syntax looks OK, what happens if one value is NULL? Have you tried the SQL function CONCAT instead of using '||'? You might have to also use an inner COALESCE function to handle NULLS? See HERE.

edit flag offensive delete link more
0

answered 2014-03-29 10:15:04 +0200

oweng gravatar image

According to the FAQ here the comma between the two single quotation marks is not required. Have you tried it without this?

edit flag offensive delete link more

Comments

I have tried without the comma in quotes - same result.

stevehar gravatar imagestevehar ( 2014-03-29 16:40:55 +0200 )edit

Yes, according to this Apache OO forum post the comma should not present any problem anyway, I merely noticed this difference and thought I should mention it.

oweng gravatar imageoweng ( 2014-03-30 00:20:14 +0200 )edit

Question Tools

2 followers

Stats

Asked: 2014-03-29 02:24:15 +0200

Seen: 317 times

Last updated: Mar 29 '14