I'm new to querying from a DB2 but have a scenario where I need to select vendor payments that are divisible by 5 with no remainder, over 1k, and three or more payments of the identical amount.
SELECT T.VendorID, T.Amount, T.Date, ROW_NUMBER ()
OVER (PARTITION BY T.VendorID, T.Amount ORDER BY T.Date) as "Order"
FROM TABLE T
WHERE T.Amount % 5 = 0 AND T.Amount >= 1000;
So I would write this into a subquery and then just put a parameter on the main query for (Where Order >= 3), however, it appears that the % Modulus Operator is throwing off errors. It gets a little dicey for me as I don't have access to the production DB and querying the table thorough a Cognos 11 SQL pass-through. Any assistance on how I could achieve the same results?
Thank you, BStinson
You wouldn't use the % operator, SQL is not a full programming language. You will want to use the MOD(); function and this is how:
MOD( dividend, divider )
that is:
SELECT T.VendorID, T.Amount, T.Date, ROW_NUMBER () OVER (PARTITION BY T.VendorID, T.Amount ORDER BY T.Date) as "Order"
FROM TABLE T
WHERE T.Amount >= 1000 AND MOD(T.Amount,5) = 0;
Hope this helps get you on the right path!