What I'd like to do is number the records the query returns, but reset the number to 1 on the change of a particular field. For instance, I would like the Row_Number count to reset back to 1 each time DCACCT changes but increment by 1 when there are multiple records for DCACCT.
My query is:
SELECT ROW_NUMBER() OVER (PARTITION BY COMM.DCACCT ORDER BY COMM.DCACCT) AS ROW_NUM,
COMM.DCACCT, COMM.DCATYP, COMM.DCCODE, COMM.DCDESC
FROM P50DATA.DCMTRNL5 COMM
JOIN P50DATA.PACNTSL1 PAT ON COMM.DCACCT=PAT.PACTNO
PAT.PLAST NOT IN ('SRC','WELL','EMERGENCY','CONFIRM')
AND COMM.DCCODE IN ('PAT1','PAT2','PAT3','PAT4','PINS','PTRX','MRGF')
ORDER BY COMM.DCACCT;
But when I execute this statement, I recieve an error message stating:
SQL Error 42999: [SQL0255] Function not supported by query
Seems that something in your query violates the restrictions specified here: Olap specifications
See the "An OLAP specification is not allowed if the query specifies" notes.
Or something routes your query to CQE instead of SQE.