How to concat two entried in a query

Creating and using forms
Post Reply
User avatar
NiksaVel
Posts: 91
Joined: Fri Jan 25, 2008 12:00 pm
Location: Croatia

How to concat two entried in a query

Post by NiksaVel »

Hi,

I'm trying to concat two entries in my query plus add a space (" ") between them...

I've managed to get the value in form edit screen by using

Code: Select all

"table1"."value1" + "table2"."value2"


but this gives me value1value2 as result... and I want value1 value2

Code: Select all

"table1"."value1" + " " +  "table2"."value2"

wont work so pleas help me out here :)
OOo 3.1.X on Ubuntu 8.x + Linux Mint 7, Win XP, Vista
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: How to concat two entried in a query

Post by Villeroy »

The concatenation operator in SQL is || and literal strings are wrapped in single quotes.

Code: Select all

"table1"."value1" || ' ' || "table2"."value2"
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
NiksaVel
Posts: 91
Joined: Fri Jan 25, 2008 12:00 pm
Location: Croatia

Re: How to concat two entried in a query

Post by NiksaVel »

maybe I'm just doing this wrong, but when I try to replace the + with || I get an error...

this is my original SQL:

Code: Select all

SELECT "Nalazi"."nalazID" AS "ID baze", "Nalazi"."Datum" AS "Datum", "Odjeli"."Odjel" AS "Odjel", "Nalazi"."ImePacijenta" AS "Ime pacijenta", "Nalazi"."PrezimePacijenta" AS "Prezime pacijenta", "Nalazi"."GodistePacijenta" AS "Godiste pacijenta", "Nalazi"."MaticnibrPacijenta" AS "Matični broj pacijenta", "Nalazi"."KlinickaDijagnoza" AS "Klinička dijagnoza", "Nalazi"."VrstaPregleda" AS "Vrsta pregleda", "Doktori"."Titula_prije" AS "Titula prije", "Doktori"."Prezime" AS "Doktor", "Doktori"."Titula_poslije" AS "Titula poslije", "Inzenjeri"."Prezime" AS "Inženjer", "Nalazi"."Daktilograf" AS "Daktilograf", "Nalazi"."Nalaz" AS "Nalaz", "Lista_doktora"."Prezime" AS "Doktor2", "Nalazi"."Doktor2" AS "Doktor2", "Doktori"."Prezime" + "Lista_doktora"."Prezime" AS "obaDoktora" FROM { OJ "Lista_doktora" RIGHT OUTER JOIN "Nalazi" ON "Lista_doktora"."ID" = "Nalazi"."Doktor2" }, "Inzenjeri", "Odjeli", "Doktori" WHERE ( "Inzenjeri"."ID" = "Nalazi"."Inzenjer" AND "Nalazi"."Odjel" = "Odjeli"."ID" AND "Doktori"."ID" = "Nalazi"."Doktor" ) ORDER BY "Datum" DESC

What I need to do is connect "Doktori"."Prezime" + "Lista_doktora"."Prezime" and insert a space or two in there...
OOo 3.1.X on Ubuntu 8.x + Linux Mint 7, Win XP, Vista
User avatar
NiksaVel
Posts: 91
Joined: Fri Jan 25, 2008 12:00 pm
Location: Croatia

Re: How to concat two entried in a query

Post by NiksaVel »

I've also stumbled upon this info that || no longer works...
http://www.oooforum.org/forum/viewtopic ... 999#169999


but I got it to work with CONCAT()
OOo 3.1.X on Ubuntu 8.x + Linux Mint 7, Win XP, Vista
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: How to concat two entried in a query

Post by Villeroy »

OK, I prefer direct SQL. In direct SQL I tried:

Code: Select all

SELECT "Remark"||' '||"Issue" AS "Concat" FROM "Dates"
with + and || successfully.
In parsed mode I can use the concat function which allows two arguments at once:

Code: Select all

SELECT Concat(Concat("Remark",' '),"Issue") AS "Concat" FROM "Dates"
In any case literal strings have to be quoted.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
NiksaVel
Posts: 91
Joined: Fri Jan 25, 2008 12:00 pm
Location: Croatia

Re: How to concat two entried in a query

Post by NiksaVel »

okay, I got it working with the following code...

Code: Select all

SELECT "Nalazi"."nalazID" AS "ID baze", "Nalazi"."Datum" AS "Datum", "Odjeli"."Odjel" AS "Odjel", "Nalazi"."ImePacijenta" AS "Ime pacijenta", "Nalazi"."PrezimePacijenta" AS "Prezime pacijenta", "Nalazi"."GodistePacijenta" AS "Godiste pacijenta", "Nalazi"."MaticnibrPacijenta" AS "Matični broj pacijenta", "Nalazi"."KlinickaDijagnoza" AS "Klinička dijagnoza", "Nalazi"."VrstaPregleda" AS "Vrsta pregleda", "Doktori"."Titula_prije" AS "Titula prije", "Doktori"."Prezime" AS "Doktor", "Doktori"."Titula_poslije" AS "Titula poslije", "Inzenjeri"."Prezime" AS "Inženjer", "Nalazi"."Daktilograf" AS "Daktilograf", "Nalazi"."Nalaz" AS "Nalaz", "Lista_doktora"."Prezime" AS "Doktor2", "Nalazi"."Doktor2" AS "Doktor2", CONCAT( CONCAT( "Doktori"."Prezime", ' ' ), "Lista_doktora"."Prezime" ) AS "obaDoktora" FROM { OJ "Lista_doktora" RIGHT OUTER JOIN "Nalazi" ON "Lista_doktora"."ID" = "Nalazi"."Doktor2" }, "Inzenjeri", "Odjeli", "Doktori" WHERE ( "Inzenjeri"."ID" = "Nalazi"."Inzenjer" AND "Nalazi"."Odjel" = "Odjeli"."ID" AND "Doktori"."ID" = "Nalazi"."Doktor" ) ORDER BY "Datum" DESC

now let me expend on the problem.... I am joining here Doktori.Prezime and Lista_doktora.Prezime
(CONCAT( CONCAT( "Doktori"."Prezime", ' ' ), "Lista_doktora"."Prezime" ) AS "obaDoktora")
with a ' ' in between... I've tried inserting multiple spaces there but in the report I always get only one... these are two lastnames for the reviewing doctors in my report (prezime=lastname in croatian)... I need to separate them better... either insert more spaces somehow, or preferably

make something like "if then..." to check wether Lista_doktora.Prezime is a null value, and if it is not, than isert ', '...
the data in Lista_doktora.Prezime does not show in every record....
OOo 3.1.X on Ubuntu 8.x + Linux Mint 7, Win XP, Vista
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: How to concat two entried in a query

Post by Villeroy »

Built-in hsql functions are documented in http://wiki.services.openoffice.org/wik ... Procedures
The original documentation of the database engine is here: http://hsqldb.org/web/hsqlDocsFrame.html

Just for the records: There is another set of common functions to be used with the simple drivers for spreadsheets, csv, LDAP, address books and dBase http://dba.openoffice.org/specification ... tions.html
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
DrewJensen
Volunteer
Posts: 1734
Joined: Sat Oct 06, 2007 9:01 pm
Location: Cumberland, MD - USA

Re: How to concat two entried in a query

Post by DrewJensen »

One final point the double bars || that has been missing from the Base query parser is finally fixed - will be part of the next release. Although you have always been able to use it if you turn escape processing off in the query designer...

There is still one issue and that is what happens with NULL fields.

Suppose I create the statement

SELECT "Col1" || ' ' || "Col2" AS "Long_Col" FROM "SomeTable"

So if Col1 = 'Barrak' and Col2 = 'Obama' the query gives me Barrak Obama,
but if Col1 = 'Barrak' and Col2 = NULL it gives me NULL
in other words if either of those fields is NULL I get NULL.


On the other hand if I used
SELECT CONCAT( "Col1", CONCAT( ' ', "Col2") AS "Long_Col" FROM "SomeTable"
then when Col1 = 'Barrak' and Col2 = NULL it gives me Barrak

hmmm not sure I spelled his name correctly - but I just refuse to use his opponent - she whose name must not be spoken...LOL

This post has NOT been paid for by the McCain for President committee.
Former member of The Document Foundation
Former member of Apache OpenOffice PMC
LibreOffice on Ubuntu 18.04
User avatar
NiksaVel
Posts: 91
Joined: Fri Jan 25, 2008 12:00 pm
Location: Croatia

Re: How to concat two entried in a query

Post by NiksaVel »

:D
OOo 3.1.X on Ubuntu 8.x + Linux Mint 7, Win XP, Vista
Post Reply