Results 1 to 4 of 4

Thread: coding help

  1. #1
    Join Date
    Jul 2005
    Posts
    276

    Unanswered: coding help

    I have a table with the following fields:


    ID BU_UNIT EDATE ESEQ EXP_DATE

    30 002 31-OCT-06 1 04-DEC-9999
    30 002 5-DEC-06 1 31-DEC-9999
    10 001 12-SEP-06 1 ?? its displayed as 11-sep-06 which is wrong.Instead it should be 12-sep-06
    10 001 12-SEP-06 2 31-DEC-9999
    20 001 30-OCT-06 1 31-DEC-9999
    If there is a transaction on the same day more than once for the same ID and BU_UNIT then the ESEQ gets populated as 1,2 and so on...

    EXP_DATE is the expiry date for the ID, BU-UNIT

    I have a condn which checks this:
    if ((curr_bu_unit = last_bu_unit) and
    (curr_id = last_id) ) then
    UPDATE tab1
    SET exp_date=curr_edate - 1
    where rowid =last_rowid;
    else
    update tab1
    set edate = highdate ---where highdate is 31-dec-9999
    where rowid = last_rowid;
    Now I need to include the condition where it checks for the max(eseq) relative to effdt and print the exp_date accordingly. Hope I'm clear.

    Can anyone please help me with this condition where it checks the for the eseq group effdt.
    Last edited by nandinir; 12-15-06 at 16:52.

  2. #2
    Join Date
    Sep 2004
    Posts
    60
    If I under stood it correctly, you need to group by 1d & effective date & not only on effective date.
    SQL> select * from m1 ;

    ID BU_UNIT EDATE ESEQ EXP_DATE
    ---------- ---------- --------------- ---------- ---------------
    30 2 31-OCT-06 1 04-DEC-99
    30 2 05-DEC-06 1 31-DEC-99
    10 1 30-OCT-99 1 31-DEC-99
    10 1 30-OCT-99 2 31-DEC-99

    You can simply do this:
    SQL> select id, max(eseq) from m1 group by edate, id ;

    ID MAX(ESEQ)
    ---------- ----------
    30 1
    30 1
    10 2



    please correct, if I am wrong in understanding.

  3. #3
    Join Date
    Jul 2005
    Posts
    276
    Yeah, thats right. I can use that query to select the max(eseq) but I would like to know how to include that condition in my code where I'm checking for this condition. I'm using one cursor to get edate, ID,bu_unit and open the cursor and then check the condition what I mentioned above.
    Now if I want to check the max(eseq) condition too, should I open another cursor for this?

    I havent used two cursors in a program previously. Can anyone please give me some idea?

  4. #4
    Join Date
    Jul 2003
    Posts
    2,296
    you can do all of that using one query and analytics with no greouping needed.

    query which includes max eseq for all rows:
    PHP Code:
    select
        ID
    BU_UNITEDATEESEQEXP_DATE
        
    max(eseqpartition by (idedatemax_eseq
    from m1

    query which also includes the corrected date (might need to tweak this since I was unsure on the rules):
    PHP Code:
    select 
      a
    .IDa.BU_UNITa.EDATEa.ESEQa.EXP_DATE,
      
    a.max_eseq
      (
    select b.exp_date 
       from m1 b 
       where a
    .id b.id and 
             
    a.edate b.edate and
             
    a.max_eseq b.eseq)
    from (
      
    select
        ID
    BU_UNITEDATEESEQEXP_DATE
        
    max(eseqpartition by (idedatemax_eseq
      from m1
    a
    - The_Duck
    you can lead someone to something but they will never learn anything ...

Posting Permissions

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