Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2004
    Posts
    78

    Question 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.

    TABLE1
    Company Check# Voucher# Line# Amt
    =================================
    C1 P1 V1 1 $100
    C1 P1 V2 1 $50
    C1 P1 V3 1 $25

    C1 P2 V4 1 $75
    C1 P2 V5 1 $25

    C1 P3 V6 1 $100

    =================================

    Should become >>>>>>

    TABLE2
    Company Check# Voucher# Line# Amt
    =================================
    C1 P1 P1 1 $100
    C1 P1 P1 2 $50
    C1 P1 P1 3 $25

    C1 P2 P2 1 $75
    C1 P2 P2 2 $25

    C1 P3 P3 1 $100

    =================================

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Something like this, perhaps?
    Code:
    SQL> SELECT * FROM test;
    
    COMPANY CHECK# VOUCHER#      LINE#        AMT
    ------- ------ -------- ---------- ----------
    C1      P1     V1                1        100
    C1      P1     V2                1         50
    C1      P1     V3                1         25
    C1      P2     V4                1         75
    C1      P2     V5                1         25
    C1      P3     V6                1        100
    
    6 rows selected.
    
    SQL> UPDATE TEST t SET
      2    voucher# = CHECK#,
      3    line# = (SELECT t2.rnk
      4            FROM (SELECT voucher#,
      5                         row_number() over (PARTITION BY CHECK# ORDER BY CHECK#) rnk
      6                   FROM TEST t1
      7                   WHERE t1.voucher# = t.voucher#
      8                  ) t2
      9             WHERE t2.voucher# = t.voucher#
     10            );
    
    6 rows updated.
    
    SQL> SELECT * FROM test;
    
    COMPANY CHECK# VOUCHER#      LINE#        AMT
    ------- ------ -------- ---------- ----------
    C1      P1     P1                1        100
    C1      P1     P1                2         50
    C1      P1     P1                3         25
    C1      P2     P2                1         75
    C1      P2     P2                2         25
    C1      P3     P3                1        100
    
    6 rows selected.
    
    SQL>

Posting Permissions

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