[Solved] How to use variables defined with @ in a base query

Discuss the database features
Post Reply
Madlena
Posts: 3
Joined: Tue Mar 14, 2017 11:44 am

[Solved] How to use variables defined with @ in a base query

Post by Madlena »

Hi there,

I need a help with MySql query containig variable definition.
I created my query using squirrel sql client and it work fine with it:

Code: Select all

select 
@last_start as rehearsalStartTime,
@rehearsal_duration := addTime('00:05:00', addtime(cStrDuration,cStrDuration)) as rehearsalDuration,
@new_end := addtime(@last_start, @rehearsal_duration) AS rehearsalEndTime,
@last_start := @new_end as new_last_start,
a.*
from vShowAct a,
(SELECT @rehearsal_duration := 0, @last_start:='17:30:00', @rehersalDate := '2017-03-31') AS vars -- variable initialization
where a.cSacRehearsalDate1 = '2017-03-31'
order by a.cSacRehearsalDate1, a.cSacRehearsalNumber;
As you can see, in the query I use variables to calculate some values.

If I try to create a query in libreoffice base I get an error like that:

SQL-Status: HY000
Fehler-Code: 1000

syntax error, unexpected $end, expecting BETWEEN or IN or SQL_TOKEN_LIKE

Unfortunately I'm unfamiliar with base syntax and need your help guys. What is wrong with the syntax?

Regards, madlena
Last edited by Madlena on Tue Mar 14, 2017 4:10 pm, edited 1 time in total.
LibreOffice 5.1.6.2 on Ubuntu 16.04 LTS
eremmel
Posts: 1080
Joined: Tue Dec 30, 2008 1:15 am

Re: How to use variables defined with @ in a base query

Post by eremmel »

This is typical MySQL way of rolling up and have moving calculations (even recursive SQL can be simulated this way). This type of SQL is far away from any standard SQL.
If you still talk to a MySQL database from Base you might try to enable the Direct SQL mode for your query. Base stops then trying to understand your SQL and does not parse it. You might also try to hide this SQL statement behind a view definition in MySQL. Use Base to query the view.
It's Microsoft marketing that tells you computers are qualified for non-technicians
W11 22H2 (build 22621), LO 7.4.2.3(x64)
Madlena
Posts: 3
Joined: Tue Mar 14, 2017 11:44 am

Re: How to use variables defined with @ in a base query

Post by Madlena »

hi eremmel,
thank you for your replay.
yes, to create a view was the first I tried to do, but in mysql it is not possible to use variables in a view...
well, the query is fare away from any standard SQL, but it did, what I want, and at the moment for me it is the most important
LibreOffice 5.1.6.2 on Ubuntu 16.04 LTS
eremmel
Posts: 1080
Joined: Tue Dec 30, 2008 1:15 am

Re: How to use variables defined with @ in a base query

Post by eremmel »

The only thing that you did not tried is to use 'Direct SQL'. When you edit your query in Base there is a icon that reads 'SQL', you can also select it via menu -> Edit -> 'Run SQL command directly'.

P.S.
Remove the flame (topic) icon from your first post for that is reserved (check the link for explanation).
It's Microsoft marketing that tells you computers are qualified for non-technicians
W11 22H2 (build 22621), LO 7.4.2.3(x64)
Madlena
Posts: 3
Joined: Tue Mar 14, 2017 11:44 am

Re: How to use variables defined with @ in a base query

Post by Madlena »

yes, thank you.
I just also found out this option.
LibreOffice 5.1.6.2 on Ubuntu 16.04 LTS
Post Reply