Page 1 of 1

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

PostPosted: Tue Mar 14, 2017 11:57 am
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   Expand viewCollapse view
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

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

PostPosted: Tue Mar 14, 2017 12:41 pm
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.

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

PostPosted: Tue Mar 14, 2017 1:14 pm
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

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

PostPosted: Tue Mar 14, 2017 2:51 pm
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).

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

PostPosted: Tue Mar 14, 2017 4:11 pm
by Madlena
yes, thank you.
I just also found out this option.