I have this sentence ROUND( ( 10 - ABS( DATEDIFF( 'ss', "lahto_t", "tulo_t" ) - 9000 ) / 211.5 ), 0 ) several times in my queries.
The calculation is working correctly but I’m looking for a user friendlier solution to do it. Can the result of this calculation be somehow stored in base so that it can be called when needed.
regards
harritapio
Yes, of course the statement can be saved or called as its own column in a subquery. So you would write that statement alone with a primary key, i.e., SELECT "thePrimaryKeyHere", ROUND( ( 10 - ABS( DATEDIFF( 'ss', "lahto_t", "tulo_t" ) - 9000 ) / 211.5 ), 0 ) AS "colDiffSecs" FROM "tbl1".
Then you would save that as a view, a view would be simplest. Go to Tables and press Create View. In the menu View → Switch off Design View you will untoggle that and then you will paste in your query above, with the corrected name for tbl1 and the name of the PRIMARY KEY conformed to your actual data source. We’ll save this query as viewDiffSecs.
Now, go back to your original query. Where your statement was previously in the query use the column colDiffSecs and you will JOIN in the view you just created thus:
SELECT *, "colDiffSecs"
FROM "tbl1" AS "a"
JOIN "viewDiffSecs" AS "b" ON ("a"."thePrimaryKeyHere" = "b"."thePrimaryKeyHere")
The VIEW will be run off the back-end database engine no matter your setup. This also should work if you saved the subquery in the normal query space, but I have found that to be a less reliable solution.
As you see, it cuts clutter in the columns but does require a JOIN and a new table.
(if this answers your question, please accept the answer by clicking the check mark (
) to the left)
Thank you for this example, this is that kind of solution I was looking for!