Re: [libreoffice-users] LO Base report Auto Grow function

You will need to research documentation for mariadb syntax / functions,
I'm not familiar with it.

Base query editor will complain on SQL that is non-compliant to hsqldb,
but there is a "Passthrough" (or maybe "Native SQL") option on the query
editor that you can use to silence the Base parser and have it pass the
exact text you input to the back-end database.  I've used this with
PostgreSQL.


I threw the query translation question at an AI and it came back with
this.  Absolutely no idea if it will work or not;

My Question:

Can you translate this PostgreSQL query into a mariaDB complient query? 
SELECT "Ort1" || COALESCE(CHR(10) || CHR(13) || NULLIF("Ort2", ''), '')
COALESCE(CHR(10) || CHR(13) || NULLIF("Ort3", ''), '') FROM "OrtTable"

The Response from AI:

Translating PostgreSQL specific syntax to MariaDB involves a few
changes. To achieve the same functionality, this query can be rewritten
for MariaDB as:

```sql
SELECT CONCAT_WS(
  '',
  Ort1,
  IF(Ort2 = '', '', CONCAT('\n', '\r', Ort2)),
  IF(Ort3 = '', '', CONCAT('\n', '\r', Ort3))
)
FROM OrtTable;
```

Notes:
- `CONCAT_WS` is used for concatenating while skipping any NULL values.
- Replaced `COALESCE` with `IF` for checking empty strings and adding
line breaks.
- MariaDB doesn't support `CHR()`, so we use `'\n'` and `'\r'` literals
for newline and carriage return.



On 10/30/24 09:08, Harvey Nimmo wrote:
> I am using LOBase with a mariadb backend, so I an not sure how that
> should be handled. In any case, the Query GUI does not like my formula
> as is.

--
To unsubscribe e-mail to: users+unsubscribe@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy