[Solved] Concatenation rules in query fieldname field

[dilettant]

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.

[Sliderule]

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

"TABLE"."LAST" || ', ' || "TABLE"."FIRST"

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

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.

[dilettant]

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?

[Sliderule]

You asked:

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.ht ... on-section

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 , in my humble opinion, in many ways, does not follow 'standards'. The above ( concatenation character ) is an example.

Sliderule

[dilettant]

Thanks again for quick and effective response

[practicalcode]

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

SELECT 'test' + '2' as test, name + name as test2, areas.* from areas

For instance, none of the following work:

Code: Select all

SELECT 'test' + '2' as test, name + name as test2, areas.name & '2' as test3, areas.* from areas

Code: Select all

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

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

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/ ... 08&start=0

Creating Calculated Fields in OpenOffice Base
http://openoffice.blogs.com/openoffice/ ... alcul.html

Where they talk about various calculated field issues

[Sliderule]

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.

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

   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

[practicalcode]

Awesome job Mr. Sliderule!

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!

[eremmel]

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.

[papijo]

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