Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2015
    Posts
    3

    Unanswered: Using Row_Number

    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:

    Code:
    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
    WHERE
    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:
    Code:
    SQL Error 42999: [SQL0255] Function not supported by query
    Is there a way I can resolve this?

  2. #2
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    What's your DB2 version and release number?
    Regards,
    Mark.

  3. #3
    Join Date
    Mar 2015
    Posts
    3
    Hey Mark,
    I had to reach out to someone, but this should be the information:
    V5R4M0

    Thanks,

  4. #4
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    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.
    Regards,
    Mark.

  5. #5
    Join Date
    Mar 2015
    Posts
    3
    Thanks,
    I ended up using this function in SQL Server as I am much more familiar with it and was able to accomplish my goal.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •