This discussion is archived
6 Replies Latest reply: Nov 13, 2012 9:18 AM by 894936 RSS

Rownum<900

894936 Newbie
Currently Being Moderated
Hi Team,
i have one requirement to my previous request "4 LAKHS "....

After updating 4 Lakhs  of records i need to send the updated data to another application ... where i will make use of below scripts.
      - We have table A  names as 
      -      here we  use the script   MAINT_01_PCK.Reloadaccountstoisell (or something
      similar). This takes care that not more than 900 records are inserted
      into Table b, as this could be a problem for the
      Business Service.
      -      This needs to be run several times until all accounts are
      transferred to Table A.
      -      It also takes care that, as it is run several times, does not
      send accounts duplicate to iSell, as it creates records in
      iSell.
      -      The call to MAINT_01_PCK.RELOADACCOUNTSTOISELL might look like
      this

begin
   MAINT_01_PCK.Reloadaccountstoisell (pi_str_accounttwhereclause =>
' comp_code = ''1206'''  ||
' AND ROWNUM < 900'||
' AND acct_no LIKE ''CU%'' '||
' AND (acct_no IN (select ''CU'' || TMP_RIMS_TO_BLOCK.AG_NO from
TMP_RIMS_TO_BLOCK)' ||
' AND NOT EXISTS (SELECT acct_no, comp_code' ||
' FROM ACCOUNTS_SENT_TO_ISELL_T isell' ||
' WHERE isell.acct_no =  ACCOUNT_V.acct_no' ||
' AND isell.comp_code = ACCOUNT_V.comp_code' ||
') )');
end;

Here we need  to send the updated records to other application that is isell but our business service is having capacity to handle rownum<900.
Then when we run the script second time... how the Ronum will be considered.

for example
sl
1
2
3
.
.
.
.
900 -----------------------------first time when we run the script it will update ans send 900 records to other application.
901
902
.
.
.
4000 Lakh records ---------second time when we run how does this rownum is considered... do we need to change rownum here ? 

could you please suggest me on the above script regarding rownum.
  • 1. Re: Rownum<900
    KeithJamieson Expert
    Currently Being Moderated
    The most sensible thing to do would be not to use rownum at all, as rownum is just a pseudo column.


    As an initial step , Copy all the rows into a staging table to be sent sent off to this business process to a staging table.
    Add a column to your staging table, e.g. counter.

    Once that table is populated then

    update table_name
    set counter = rownum;

    That will assign the rownum to the counter column.

    Now you just use the counter.

    When your process is finished just truncate the table.
  • 2. Re: Rownum<900
    myOra_help Journeyer
    Currently Being Moderated
    ROWNUM is generated by Oracle as when rows are selected, i.e. first row selected it is assign rownum 1, second row selected it is assign rownum 2 and so on. If you select from a table multiple time then you may get same row selected with different rownum. Thus using rownum as a logic would be riski as you may end up with duplicate rows.
  • 3. Re: Rownum<900
    942756 Newbie
    Currently Being Moderated
    yeah..
    i guess u have to
    create a column in 40 lack record table A...eg. s.no=0


    fetch these records in table B ,condition like that

    for 1st fetch s.no --> 900

    2nd fetch s.no --> 1800

    ans so on...
  • 4. Re: Rownum<900
    AlbertoFaenza Expert
    Currently Being Moderated
    Hi,

    One note about your messages: don't put all your message between lines starting with {noformat}
    {noformat}
    
    Just put only code between these lines.
    
    i.e.:
    
    I have a problem with the following query....
    {noformat}
    {noformat}
    SELECT ...
    {noformat}
    {noformat}
    is someone able to help?
    It will appear as:
    I have a problem with the following query....
    SELECT ...
    is someone able to help?
    Regards. Al                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
  • 5. Re: Rownum<900
    894936 Newbie
    Currently Being Moderated
    For example
    
    first we will truncate table a.
    we will send records to table a of rownum <10.
    second the records  will be inserted to table b.
    here we are checking some conditions.
    
    so next time when we run... do we need to truncate the table a again inorder to send 11 to 20th records.
    1:TMP_RIMS_TO_BLOCK ---TABLE A CONSISTS OF some records like 1 to 20.
    2: ACCOUNTS_SENT_TO_ISELL_T ---- before running the below script we will truncate this table ----now no records.
    3:below script will fetch rownum<10 from TMP_RIMS_TO_BLOCK and it will load into ACCOUNTS_SENT_TO_ISELL_T
    4: when we run business service our records will be sent to table BASE_WORK_ACCOUNT_CHANGES_T.
    
    5: we need to cross check whether that rownum <10 records  fetched from TMP_RIMS_TO_BLOCK to BASE_WORK_ACCOUNT_CHANGES_T.
    
    6: after the above steps do we need to follow the step 1 again.... or will it take the rownum into consideration with the conditions mentioned in the scripts.
    like if the account is already avialble then it wont consider that account and the new account will be taken into consideration with the "rownum" specified.
    
    can you guide me on the above scripts.---- i think i explained clearly as per my knowledge.
    
    *************************************************************
    begin
       MAINT_01_PCK.Reloadaccountstoisell (pi_str_accounttwhereclause =>
    ' comp_code = ''1206'''  ||
    ' AND ROWNUM < 10'||      --------------------------------------------------------------------------------------------taking the records where rownum < 10.
    ' AND acct_no LIKE ''CU%'' '||
    ' AND (acct_no IN (select ''CU'' || TMP_RIMS_TO_BLOCK.AG_NO from -------------------------------- fetching the records "TMP_RIMS_TO_BLOCK"
    TMP_RIMS_TO_BLOCK)' ||
    ' AND NOT EXISTS (SELECT acct_no, comp_code' ||  --------------------------------------------------------checking the conditions
    ' FROM ACCOUNTS_SENT_TO_ISELL_T isell' ||
    ' WHERE isell.acct_no =  ACCOUNT_V.acct_no' ||
    ' AND isell.comp_code = ACCOUNT_V.comp_code' ||
    ') )');
    end;
    *****************************************************************
    
    can you please suggest me on this.
  • 6. Re: Rownum<900
    894936 Newbie
    Currently Being Moderated
    thanks

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points