samedi 25 avril 2015

Filtering by analytic function results without subquery or subtable


I'm working on Netsuite project which has limited SQL capabilites. It's difficult to test as I am basically guessing the SQL they are building in their GUI.

I'd like to filter the results of a query to the results with a negative value of a culmative sum.

Is the following a valid PL/SQL construct (barring any small syntactical errors)?

 SELECT SUM(amount) OVER(PARTITION BY itemid ORDER BY date ROWS UNBOUNDED
    PRECEDING) AS "sum" FROM table WHERE sum < 0

Secondly, due to limitations in Netsuite, is the following a valid construct?

 SELECT SUM(amount) OVER(PARTITION BY itemid ORDER BY date ROWS UNBOUNDED 
    PRECEDING) AS "sum" FROM table WHERE SUM(amount) OVER(PARTITION BY itemid
    ORDER BY date ROWS UNBOUNDED PRECEDING) < 0

Oracle's documentation suggests that neither of these are valid and filtering an analytic function should be done via subquery but some google groups and other websites suggest otherwise. Most however are using RANK() and DENSE_RANK() functions in their examples which may function differently.


Aucun commentaire:

Enregistrer un commentaire