Unanswered: Sequence Number Reset based on other key values
I am trying to assign the Check# to the Voucher# that were paid using the same check and save data in a separate table. As shown in example below all fields are key fields except the amount field and voucher# reassignment is done for every change in combination of Company and Check#. But since Line# is also key it will not allow to do this because of unique constraint violation. I want to add a sequence number in place of the line number that resets itself on every change in Company/Check# combination as shown in TABLE2 below.
Is this possible using SQL/PLSQL.... if so how? Thanks in advance for your help.