[Solved] Syntax error if query run from F5 Edit window

[charlie.it]

Hello everyone, the query of this embedded HSQLDB database works if executed directly or if executed from the Edit window in SQL view (F5). If executed instead from the Edit window (F5) it gives me syntax error. I don't understand why, can you help me? My OS and LibreOffice versions are the ones in my signature below. Thank you.

Code: Select all

SELECT "ID", CAST( ( ( "Anni" * 12.00 + "Mesi" + DATEDIFF( 'mm', "Data_Registr", NOW( ) ) ) / 12 ) AS INTEGER ) AS "Anni attuali", MOD( "Anni" * 12.00 + "Mesi" + DATEDIFF( 'mm', "Data_Registr", NOW( ) ), 12 ) AS "Mesi Attuali" FROM "Pazienti"

Edit: Changed subject, was Query Issue  Make your post understandable by others  -- MrProgrammer, forum moderator

[Villeroy]

I can run the query in parsed mode and in direct SQL. Only the query designer has a problem with it. Just keep it in SQL view and everything should be fine.

[charlie.it]

Thanks for your reply, my query behaves as you said. But is this normal, is there a function that the query design doesn't support? CAST? DATEDIFF? MOD? I had already used them in the past but I didn't realize that they caused problems in design mode.

Update: it seems that query design doesn't support MOD, if I erase it, all works.

[UnklDonald418]

I’ve tried right clicking on your query, selecting Edit to open in the Query Design GUI and when I press F5 it displays the results without any error messages.

SS01.png (21.07 KiB) Viewed 3580 times

I get the same results
Windows 10
OO 4.1.14
LO 7.4.5.1 (x64)

Linux Mint
LO 6.4.7.2

Perhaps a platform issue?

[Nick N,]

Hello Charlie,

I've tested your query on AOO 4.1.14 and did not encounter any problem either ways too.

Here is the result.

Regards

Nick

[RPG]

I have the idea that you can solve your problems when you change 12.00 in 12.
I think the problem is in: double value's and integer values in one field.
I also agree with Villeroy.

[charlie.it]

Thanks @RPG, yours is the right solution, congratulations, bravo!
Anyway, thanks to everyone for testing and for your suggestions.