Ask Your Question

base calculation in query

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 close merge delete

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
Login/Signup to Answer

Question Tools

1 follower


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

Seen: 127 times

Last updated: Aug 21 '15