[Solved] LibreOffice 4.3 Base query calculation

[harritapio]

hello,
as a beginner with libreoffice base I have not been able to solve a simple problem:

First question:
In my queries I have this calculation on several fields and I would like to use some variable name for this sentence - but I have not been able to figure out how to do it. The calculation itself is working correctly but to use it as it is now is not very practical

Code: Select all

 CASE WHEN ( ROUND( ( 10 - ABS( DATEDIFF( 'ss', "lahto_t", "tulo_t" ) - 9000 ) / 211.5 ), 0 ) ) < 0 THEN 0 ELSE ROUND( ( 10 - ABS( DATEDIFF( 'ss', "lahto_t", "tulo_t" ) - 9000 ) / 211.5 ), 0 ) END "aikapist"

Second question:
Can I replace the constants "9000" and "211.52" on the sentence with some name and if how?
These two values must be changed depending on application environment.

looking forward to your reply

regards

harritapio

[MTP]

In Base queries you can include table columns. If you need your value to come from something besides a table column, a query may not be the correct tool - some kind of programming language may be required. You can write a program to build an SQL string differently depending on whatever variable (like application environment) and then execute the custom-built SQL.

[harritapio]

thanks for your advice!

Maybe I will continue to use the current solution until I have learned some more of libreoffice use. At the moment I'm not able to write a custom build SQL string to libreoffice.

[eremmel]

You wrote "These two values must be changed depending on application environment." that sounds to me hardly changing data when application environment is setup. So you might use the following:
Define a parameter table 'Application' that will have one row only and the fields: { ID (PK), val1, val2 } with values { 0, 9000, 211.52 }
Now the skeleton for your query becomes:

Code: Select all

SELECT ...
CASE WHEN ( ROUND( ( 10 - ABS( DATEDIFF( 'ss', "lahto_t", "tulo_t" ) - val1 ) / val2 ), 0 ) ) < 0 THEN 0 ELSE ROUND( ( 10 - ABS( DATEDIFF( 'ss', "lahto_t", "tulo_t" ) - val1 ) / val2 ), 0 ) END "aikapist"
FROM table1, Application ....
WHERE Application.ID = 0 ....
....

The filter 'Application.ID = 0' is not strictly needed. You might get more complex queries when you start using aggregates.

[harritapio]

thank you very much "eremmel",
this was the solution for my question.

As a beginner with libreoffice I had not realised to use second table for predefined variables.