How to merge those two queries into one? Subquery?

[Johannes Schneider]

Hi guys,

those statements work for me:

select 

set(@VAR_MILLIS_MIN ,min(millis)) AS MILLIS_MIN,

set(@VAR_MILLIS_DELTA ,max(MILLIS) - min(MILLIS)) as MILLIS_DELTA

from HISTORICAL.DATAPOINTS D 

where D.MILLIS between 100000000 and 101000000 AND PVID=25;

select PVID, floor(((MILLIS-@VAR_MILLIS_MIN)/(@VAR_MILLIS_DELTA+ 0.00000001))*3) AS flooredMillis, min(millis),max(millis),max(numbervalue), min(numbervalue)

from

HISTORICAL.DATAPOINTS 

where MILLIS between 100000000 and 101000000 AND PVID=25

group by flooredMillis

order by flooredMillis

But now I would like to merge this functionality into one statement that I can use as prepared statement. Any ideas how this could be done?

The main problem seems to be, that I am using calculated values from the first query in the column definition of the second one...

[Thomas Mueller]

Hi,

You could create a user defined function (a Java function) that runs both queries.

Regards,

Thomas

--
You received this message because you are subscribed to the Google Groups "H2 Database" group.
To view this discussion on the web visit https://groups.google.com/d/msg/h2-database/-/bUzGGgm98d0J.
To post to this group, send email to h2-da...@googlegroups.com.
To unsubscribe from this group, send email to h2-database...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.

[Johannes Schneider]

Thanks. Will give it a try.

Executing two queries can also be done without a user defined function. So what might be the benefit in that case?

To unsubscribe from this group, send email to h2-database+unsubscribe@googlegroups.com.

[Thomas Mueller]

Hi,

> Executing two queries can also be done without a user defined function. So what might be the benefit in that case?

It is simply the answer to your question. Your question was: "But now I would like to merge this functionality into one statement that I can use as prepared statement" - The user defined function can be called from *one* statement. 

Regards,

Thomas

On Wednesday, August 8, 2012, Johannes Schneider wrote:

Thanks. Will give it a try.

To view this discussion on the web visit https://groups.google.com/d/msg/h2-database/-/1fX21L8BTXMJ.

To post to this group, send email to h2-da...@googlegroups.com.

To unsubscribe from this group, send email to h2-database...@googlegroups.com.

[Johannes Schneider]

Ok thanks...