Ask Your Question

base calculation in query [closed]

asked 2015-08-20 21:23:08 +0200

harritapio gravatar image

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.



edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2020-08-23 18:44:12.808916

1 Answer

Sort by » oldest newest most voted

answered 2015-08-21 00:39:31 +0200

doug gravatar image

updated 2015-08-21 00:41:58 +0200

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 (image description) to the left)

edit flag offensive delete link more


Thank you for this example, this is that kind of solution I was looking for!

harritapio gravatar imageharritapio ( 2015-08-21 05:23:36 +0200 )edit

Question Tools

1 follower


Asked: 2015-08-20 21:23:08 +0200

Seen: 136 times

Last updated: Aug 21 '15