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