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

Discuss the database features

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

Postby Madlena » Tue Mar 14, 2017 11:57 am

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
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
Madlena
 
Posts: 3
Joined: Tue Mar 14, 2017 11:44 am

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

Postby eremmel » Tue Mar 14, 2017 12:41 pm

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
eremmel
 
Posts: 1037
Joined: Tue Dec 30, 2008 1:15 am

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

Postby Madlena » Tue Mar 14, 2017 1:14 pm

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
Madlena
 
Posts: 3
Joined: Tue Mar 14, 2017 11:44 am

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

Postby eremmel » Tue Mar 14, 2017 2:51 pm

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
eremmel
 
Posts: 1037
Joined: Tue Dec 30, 2008 1:15 am

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

Postby Madlena » Tue Mar 14, 2017 4:11 pm

yes, thank you.
I just also found out this option.
LibreOffice 5.1.6.2 on Ubuntu 16.04 LTS
Madlena
 
Posts: 3
Joined: Tue Mar 14, 2017 11:44 am


Return to Base

Who is online

Users browsing this forum: No registered users and 4 guests