Results 1 to 6 of 6

Thread: query

  1. #1
    Join Date
    Nov 2003
    Posts
    33

    Unanswered: query

    Hello

    I have difficulty in writting a query. i have table with onle column and i have to populate a psudo column by updating the value in first column. follwing is the table

    invoice no psudocolumn
    1000 10001
    1000 10002
    1000 10003
    2000 20001
    2000 20002
    2000 20003
    3000 30001
    3000 30002
    3000 30003

    that is i have 3 entry for same invoice no say 1000 and i want my psudo column will show entry 10001, 10002,1003 for that corrosponding invoice no entry.

    thnax in advance

  2. #2
    Join Date
    Apr 2003
    Location
    NY
    Posts
    208
    Code:
    UPDATE table
    SET psudocolumn = invoice+no + rownum
    HIH

  3. #3
    Join Date
    Nov 2003
    Posts
    33
    hey carlo it wil work for first invoice no 1000 but for invoice no 2000 my psudo column would be 20004 instead of 20001.

  4. #4
    Join Date
    Apr 2004
    Posts
    246
    select invoice,
    invoice || to_char( rank() over (partition by invoice order by rownum) ) psuedo
    from table

  5. #5
    Join Date
    Apr 2003
    Location
    NY
    Posts
    208
    Actually your gonna need to wrap that update to reset the rownum counter.
    Try this.

    Code:
    DECLARE
    
    CURSOR c_cur IS
       SELECT DISTINCT invoice_no 
       FROM tablename;
       
    BEGIN
    
       FOR lv_rec in c_cur LOOP
       
          UPDATE tablename
          SET psudocolumn = invoice_no +rownum
          WHERE invoice_no  = lv_rec.invoice_no ;
       
       END LOOP;
    
    END;
    HIH

  6. #6
    Join Date
    Nov 2003
    Posts
    33
    Thank you very much

    I really appriciate your prompt reply

Posting Permissions

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