#1
  1. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2008
    Location
    Texas
    Posts
    360
    Rep Power
    10

    Update LOOP and Increment


    This is a Peoplesoft environment. I need to insert a step in an App Engine using App Designer.

    I need to generate enrollment ID's for a couple 1000 students.

    The table currently looks like this.

    REQUEST_ID,REQ_DETL_SEQ,STUDENT_ID
    ,0,10001209
    ,0,10001209
    ,0,10002303
    ,0,10002303
    ,0,10002504
    ,0,10002504
    ,0,10006299
    ,0,10006299
    ,0,10018424
    ,0,10018424
    ,0,10013769
    ,0,10013769
    ,0,10013769

    I need to to look like this

    REQUEST_ID,REQ_DETL_SEQ,STUDENT_ID
    0008319105,000001,10001209
    0008319105,000002,10001209
    0008319106,000001,10002303
    0008319106,000002,10002303
    0008319107,000001,10002504
    0008319107,000002,10002504
    0008319108,000001,10006299
    0008319108,000002,10006299
    0008319109,000001,10018424
    0008319109,000002,10018424
    0008319110,000001,10013769
    0008319110,000002,10013769
    0008319110,000003,10013769
    0008319110,000004,10013769

    I would grab the last REQUEST_ID used and increment that.

    SELECT
    EID.ENRL_REQ_ID_LAST
    FROM PS_ENRL_REQ_ID_TBL EID


    I don't know if I should be researching a cursor, or can I get away with designing a merge statement.
  2. #2
  3. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2008
    Location
    Texas
    Posts
    360
    Rep Power
    10
    Correction.. I'd grab the latest enroll_id like so

    with get_last_enroll_id as
    (select enrl_req_id_last from ps_enrl_req_id_tbl)

    then increment

    select (enrl_req_id_last + row_number() over (partition by 1 order by student_id)) next_enroll_id

    Now I need to figure out how to loop and update.
  4. #3
  5. Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Sep 2006
    Posts
    867
    Rep Power
    391

    Talking


    Try this:
    Code:
    SQL> l
      1  WITH My_Tab ( Request_Id, Req_Detl_Seq, Student_Id )
      2      AS (SELECT NULL, 0, 10001209 FROM DUAL UNION ALL
      3     SELECT NULL, 0, 10001209 FROM DUAL UNION ALL
      4     SELECT NULL, 0, 10002303 FROM DUAL UNION ALL
      5     SELECT NULL, 0, 10002303 FROM DUAL UNION ALL
      6     SELECT NULL, 0, 10002504 FROM DUAL UNION ALL
      7     SELECT NULL, 0, 10002504 FROM DUAL UNION ALL
      8     SELECT NULL, 0, 10006299 FROM DUAL UNION ALL
      9     SELECT NULL, 0, 10006299 FROM DUAL UNION ALL
     10     SELECT NULL, 0, 10018424 FROM DUAL UNION ALL
     11     SELECT NULL, 0, 10018424 FROM DUAL UNION ALL
     12     SELECT NULL, 0, 10013769 FROM DUAL UNION ALL
     13     SELECT NULL, 0, 10013769 FROM DUAL UNION ALL
     14     SELECT NULL, 0, 10013769 FROM DUAL)
     15  SELECT Req_Id + Rnk Request_Id, TO_CHAR ( Idx, 'FM000000' ) Req_Detl_Seq, Student_Id
     16    FROM ( SELECT 0008319104 Req_Id
     17                , Student_Id
     18                , ROW_NUMBER ( )
     19                     OVER ( PARTITION BY Student_Id ORDER BY Student_Id )
     20                     Idx
     21                , DENSE_RANK ( ) OVER (ORDER BY Student_Id) Rnk
     22*            FROM My_Tab )
    SQL>/
    REQUEST_ID REQ_DETL_SEQ STUDENT_ID
    ---------- ------------ ----------
       8319105 000001         10001209
       8319105 000002         10001209
       8319106 000001         10002303
       8319106 000002         10002303
       8319107 000001         10002504
       8319107 000002         10002504
       8319108 000001         10006299
       8319108 000002         10006299
       8319109 000001         10013769
       8319109 000002         10013769
       8319109 000003         10013769
       8319110 000001         10018424
       8319110 000002         10018424
    
    13 rows selected.

IMN logo majestic logo threadwatch logo seochat tools logo