[Solved] Concatenation rules in query fieldname field

Creating tables and queries

[Solved] Concatenation rules in query fieldname field

Postby dilettant » Sat Oct 30, 2010 7:56 pm

Creating query in design view. Fieldnames FIRST and LAST come from TABLE. Wish to create a calculated field containing LAST followed by ", " followed by FIRST. It won't accept ampersand as concatenation operator in Field cell, but will accept "+" as in "TABLE"."LAST" + "TABLE"."FIRST". How do I add the literals comma and space between the two? It won't accept them with or without single or double quotes. Thank you.
Last edited by dilettant on Sat Oct 30, 2010 9:01 pm, edited 1 time in total.
OpenOffice 3.2 on Windows Vista
dilettant
 
Posts: 6
Joined: Sat Oct 30, 2010 7:36 pm

Re: Concatenation rules in query fieldname field

Postby Sliderule » Sat Oct 30, 2010 8:23 pm

I suggest you use two pipe characters ( || ) to tell the database engine that you want to concatenate ( combine data together ).

  1. The database engine uses wrapped single quotes ( ' ) to indicate a 'literal'. For example: ', ' .

  2. The database engine will know you are talking about a Table or Field name when it is wrapped in double quotes ( " ). For example: "MyTable", or, "MyField" .

Bottom line, per your description, on the Field line place, and, I recommend you also assign an Alias name for the new, calculated column:

Code: Select all   Expand viewCollapse view
"TABLE"."LAST" || ', ' || "TABLE"."FIRST"

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

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.
User avatar
Sliderule
Volunteer
 
Posts: 1188
Joined: Thu Nov 29, 2007 9:46 am

Re: Concatenation rules in query fieldname field

Postby dilettant » Sat Oct 30, 2010 8:59 pm

Thanks ever so much Sliderule (I used to use one about 60 years ago), it worked. I am a bit confused about the inconsistency. Ampersand and plus sign work in other contexts, even in this context plus sign is accepted but it won't let you reopen in design view, only in SQL view. The "||" characters are new to me (I come from Access). Where do I find definitive rules on the subject?
OpenOffice 3.2 on Windows Vista
dilettant
 
Posts: 6
Joined: Sat Oct 30, 2010 7:36 pm

Re: (SOLVED)Concatenation rules in query fieldname field

Postby Sliderule » Sat Oct 30, 2010 9:11 pm

You asked:

dilettant wrote:Where do I find definitive rules on the subject?


According to HSQL documentation ( the database engine, I assume you are using -- you can confirm this by looking at the bottom of the screen -- status line -- after opening your OpenOffice database file ), found at:

http://www.hsqldb.org/doc/guide/ch09.html#expression-section

http://www.hsqldb.org/doc/guide/ch09.html#expression-section wrote:
string

Strings in HSQLDB are Unicode strings. A string starts and ends with a single ' (singlequote). In a string started with ' (singlequote) use '' (two singlequotes) to create a ' (singlequote).

String contatenation should be performed with the standard SQL operator || rather than the non-standard + operator.

The LIKE keyword uses '%' to match any (including 0) number of characters, and '_' to match exactly one character. To search for '%' or '_' itself an escape character must also be specified using the ESCAPE clause. For example, if the backslash is the escaping character, '\%' and '\_' can be used to find the '%' and '_' characters themselves. For example, SELECT .... LIKE '\_%' ESCAPE '\' will find the strings beginning with an underscore.


Just so you understand, Microsoft Access :crazy: , in my humble opinion, in many ways, does not follow 'standards'. The above ( concatenation character ) is an example. :super:

Sliderule
User avatar
Sliderule
Volunteer
 
Posts: 1188
Joined: Thu Nov 29, 2007 9:46 am

Re: [Solved] Concatenation rules in query fieldname field

Postby dilettant » Sun Oct 31, 2010 2:35 am

Thanks again for quick and effective response
OpenOffice 3.2 on Windows Vista
dilettant
 
Posts: 6
Joined: Sat Oct 30, 2010 7:36 pm

(UN)Solved Concatenation rules in query fieldname field

Postby practicalcode » Thu Mar 03, 2011 4:48 am

Sliderule ( or someone else ... ;-))

So, what do you do if you want to concatenate strings between a field and a string literal using Base, connected to an ODBC Access db?...

The following works:
Code: Select all   Expand viewCollapse view
SELECT 'test' + '2' as test, name + name as test2, areas.* from areas

For instance, none of the following work:

Code: Select all   Expand viewCollapse view
SELECT 'test' + '2' as test, name + name as test2, areas.name & '2' as test3, areas.* from areas

Code: Select all   Expand viewCollapse view
SELECT 'test' + '2' as test, name + name as test2, areas.name + '2' as test3, areas.* from areas

Base won't even let you save either of the above queries ERR:
SQL Status: HY000
Error code: 1000
syntax error, unexpected $end, expecting BETWEEN or IN or SQL_TOKEN_LIKE


Code: Select all   Expand viewCollapse view
SELECT 'test' + '2' as test, name + name as test2, areas.name || '2' as test3, areas.* from areas

Base will let you save the query, but when you try to execute, you get ERR:
[Microsoft][ODBC Microsoft Access Driver] Invalid use of vertical bars in query expression `areas`.`name` || '2".

Similar to this ...
Code: Select all   Expand viewCollapse view
SELECT 'test' + '2' as test, `name` + `name` as test2, CONCAT('test', '2') as test3, areas.* from areas

Base will let you save the query, but when you try to execute, you get ERR:
SQL Status: 42000
Error code: -3102
[Microsoft][ODBC Microsoft Access Driver] Undefined function 'CONCAT' in expression.


It would appear there's no way to concatenate a field and a string literal in Base, connected to an ODBC Access db...!?...

____________________________________________________________________________________
Note:
Because the two topics are related, I'm posting a link to a similar question. In the following topics...

Base form or Query with calculated fields
http://user.services.openoffice.org/en/forum/viewtopic.php?f=61&t=3408&start=0

Creating Calculated Fields in OpenOffice Base
http://openoffice.blogs.com/openoffice/2007/03/creating_calcul.html

Where they talk about various calculated field issues
George

LibreOffice 3.3.0 on Windows 7
practicalcode
 
Posts: 6
Joined: Thu Feb 17, 2011 9:00 pm

Re: [Solved] Concatenation rules in query fieldname field

Postby Sliderule » Thu Mar 03, 2011 6:45 am

George:

It is important that you understand, these 'limitations' are NOT because of BASE, but rather, the limitations that the ODBC driver places on the connection with your Access database. Since you are using Access, the following might help.

George wrote:So, what do you do if you want to concatenate strings between a field and a string literal using Base, connected to an ODBC Access db?...


Now, to attempt to give you a solution, like it or not.

  1. The ODBC driver expects your Access field and table names to be surrounded by the ` character. Therefore, a field name might be . . . `areas`.`name`
  2. The ODBC driver will support a CONCAT function with TWO fields . . . and . . . they may be nested. BUT, it also wants it wrapped with the FUNCTION 'qualifier {FN your complete function } .
  3. For example ( I added the `First Name` and `Last Name` one to demonstrate a NESTED CONCAT function ):

    Code: Select all   Expand viewCollapse view
    SELECT
       'test' + '2' as test,
       `name` + `name` as `Concat Name with +`,
       {FN CONCAT(`name `, `name`)} as test2,
       {FN CONCAT(`areas`.`name`,  '2')} as test3,
       `First Name` + ' ' + `Last Name` as `Full Name 01`,
       {FN CONCAT({FN CONCAT(`First Name`,' ')},`Last Name`)} as `Full Name 02`,
       areas.*
    From areas

  4. MOST IMPORTANT: Since the OpenOffice Base Parser does NOT know about this syntax, it can ONLY be utilized when it is Run Directly ( the SQL is passed directly to the ODBC driver engine ). Therefore, you MUST tell OpenOffice Base that you want to pass the SQL directly to your database engine by EITHER:

    1. On the Toolbar, press the icon with the word SQL and the green check mark
    2. From the Menu: Edit -> Run SQL command directly

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

Sliderule
User avatar
Sliderule
Volunteer
 
Posts: 1188
Joined: Thu Nov 29, 2007 9:46 am

Re: [Solved!] Concatenation rules in query fieldname field

Postby practicalcode » Thu Mar 03, 2011 4:11 pm

Awesome job Mr. Sliderule! :super:

Not only did it work, but I understand the Base / ODBC interaction & syntax a bit better now! Thank you very much for a great & timely repsonse!

George

PS Pls note, with respect to:
these 'limitations' are NOT because of BASE

I didn't necessarily intend to attribute it to one specific technology: Base vs ODBC vs Underlying Access MDB, but instead with the conflaguration of technologies causing 'overridden' operators to not be passed / interpreted correctly... Which is why I'm even more appreciative of the detail of your response (esp #4 where you explain 'how' to tell Base to pass the sql directly to the ODBC source.) Again, thank you!
George

LibreOffice 3.3.0 on Windows 7
practicalcode
 
Posts: 6
Joined: Thu Feb 17, 2011 9:00 pm

Re: [Solved] Concatenation rules in query fieldname field

Postby eremmel » Thu Mar 03, 2011 9:41 pm

This is a bug in Base see issue Base SQL parser does not accept literal string value using '+' for concatenation with an other workaround that does not need the native mode.
It's Microsoft marketing that tells you computers are qualified for non-technicians
eremmel
 
Posts: 1037
Joined: Tue Dec 30, 2008 1:15 am

Re: [Solved] Concatenation rules in query fieldname field

Postby papijo » Sun Dec 14, 2014 4:54 pm

eremmel wrote:This is a bug in Base see issue Base SQL parser does not accept literal string value using '+' for concatenation with an other workaround that does not need the native mode.

Correct link to bug report is : https://issues.apache.org/ooo/show_bug.cgi?id=97811
LO: LibreOffice 5.2.3 on Windows 10 64bits. Split database HSQL 2.3.4.
papijo
 
Posts: 86
Joined: Sat Nov 08, 2014 5:46 pm
Location: Brittany, West of France


Return to Tables & Queries

Who is online

Users browsing this forum: No registered users and 1 guest