If I see this right, what you want to do is to project an iterative task (while [condition]: collect row; select next row) in a data set oriented language SQL, which works more like (select all rows where [condition] applies). This is no terrible mistake or so, very often someone wants/has to do that, but it's difficult and often very inperformant. Btw. this has nothing to do with H2 specifically but applies to SQL in general.
You could / would have to do something like aggregating all Qty per row and then select those rows where the aggregated value is >= 140. Tricky stuff with GROUP BY, HAVING and subSELECTs. I experimented a little with such queries (on another DBMS). It works, but it takes extremely long in comparison to complete the query, due to the very different iteration strategy of the data set oriented nature. I'd suggest to solve the task iteratively instead of data set oriented, meaning in Java instead of in SQL. Depending on your other conditions (e.g. how much data has to be shuffled through, how much libs shall be available, etc.) this can either be done on the application side after loading the raw data or in a java method mapped as a FUNCTION alias in H2. (On a side note: You example of taking only a QTY portion of one row but all of its RATE value sounds a little like your data is in the wrong form for that task. You could ease your life a lot by getting/ creating data in a better suited resolution first.) On Dec 29, 2:55 pm, Prakash Krishnan <[email protected]> wrote: > Hi > > i would like to know hw to get rows based on quantity. pls refer to > attached file > > -- > Keep in Touch > Prakash > [email protected] > > H2_Question_1.doc > 51KViewDownload -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.
