This discussion is archived
5 Replies Latest reply: Jan 5, 2013 10:11 PM by Moazam Shareef RSS

Date Calculating...

Moazam Shareef Explorer
Currently Being Moderated
Guyz,

I have two block on a single canvas.

(Ex: Block1 and Block2)

Block1 is Tubular (With Effective_date) Date Field - Database Block
Block2 is Form Type (Normal) CAL_DIS Number datatype , Non database block as well.

i am fetching the dates from database with the below cursor in Block1 on WNFI Trigger
DECLARE CURSOR C1 IS SELECT EFF_DATE  FROM MN_EMP_DEP_DET WHERE EMPID=:PARAMETER.EMPID AND CO_TKT=1 AND EFF_DATE IS NOT NULL; 
 BEGIN
      GO_BLOCK('BLOCK1');
      OPEN C1;
      LOOP
           FETCH C1 INTO :BLOCK1.EFFECTIVE_DATE; 
           EXIT WHEN C1%NOTFOUND;
           NEXT_RECORD;
      END LOOP;
      CLOSE C1;
      FIRST_RECORD;
 END;
So im getting records properly as below....and on Block2 item i gave the below formula in CAL_DIS item
01/01/2005
01/06/2007
01/08/2010
01/08/2012
FLOOR(MONTHS_BETWEEN(SYSDATE,:BLOCK1.EFFECTIVE_DATE))/12
when ever i'm putting the cursor on the above date field its giving different results in block2 cal_dis item, is there any other way that i can calculate all the above dates at once with the above formula into CAL_DIS item?

im using forms 6i Database 9i

Edited by: Moazam Shareef on Jan 2, 2013 5:12 AM

Edited by: Moazam Shareef on Jan 2, 2013 5:13 AM

Edited by: Moazam Shareef on Jan 2, 2013 5:13 AM
  • 1. Re: Date Calculating...
    user346369 Expert
    Currently Being Moderated
    First of all, if BLOCK1 is a database block, you should NOT populate it that way. Instead, all you need is:
    GO_BLOCK('BLOCK1'); EXECUTE_QUERY;

    Set the block's database table to: MN_EMP_DEP_DET
    Just set the Where Clause of BLOCK1 to:
    EMPID=:PARAMETER.EMPID AND CO_TKT=1 AND EFF_DATE IS NOT NULL
    ...and name the column in BLOCK1 the same as the table: EFF_DATE

    You can actually create the calculation column CAL_DIS in BLOCK1, complete with the formula you are currently using. Then when you do the Execute_Query, the CAL_DIS column would be automatically calculated while the block is being populated.
  • 2. Re: Date Calculating...
    Andreas Weiden Guru
    Currently Being Moderated
    I agree with Steve that you should not populate a DB-block using a cursor-for-loop.

    About your calculated item... I'm not sure what you expect as result. what is the Monthe_between over all rcords of your block1 ? Is it the difference from teh smallest value? Or fromthe largest value? Or something else?
  • 3. Re: Date Calculating...
    Moazam Shareef Explorer
    Currently Being Moderated
    Thanks for your help Steve.

    but here i need the total calculated result where the dates are different in tubular block i create two block here one database tubular block and another control block where i place two non database items DIS_ID and DIS_CAL i place the below formula in DIS_CAL and on :CTRL.DIS_ID i wrote the below code in on KEY-NEXT-ITEM
    GO_BLOCK('MN_EMP_DEP_DET');
    SET_BLOCK_PROPERTY('MN_EMP_DEP_DET', DEFAULT_WHERE, 'EMPID=:CTRL.DIS_ID AND CO_TKT=1');
    EXECUTE_QUERY;
    GO_ITEM('CTRL.DIS_ID');
    Formula
    FLOOR(MONTHS_BETWEEN(SYSDATE,:MN_EMP_DEP_DET.EFF_DATE))/12
    The query executed properly, but i need the total result from the tubular block, where it shows only single column result when i place the cursor in first record its shows *3.00* and in another record its shows *2.00* in the DIS_CAL, the result should display *5.00* in DIS_CAL using the above floor formula, you can see the below snaps.

    http://s1.postimage.org/y87qof4rv/res_1.jpg
    http://s1.postimage.org/rvslel1pn/res_2.jpg

    I tried the below query run sucessfully but cannot use in formula column
    SQL> SELECT FLOOR(MONTHS_BETWEEN(SYSDATE,EFF_DATE)/12) FROM MN_EMP_DEP_DET
      2  WHERE EMPID=1657 AND CO_TKT=1
      3  ;
    
    FLOOR(MONTHS_BETWEEN(SYSDATE,EFF_DATE)/12)                                                          
    ------------------------------------------                                                          
                                             3                                                          
                                             2                                                          
    
    SQL> SELECT COUNT(FLOOR(MONTHS_BETWEEN(SYSDATE,EFF_DATE)/12)) FROM MN_EMP_DEP_DET
      2  WHERE EMPID=1657 AND CO_TKT=1;
    
    COUNT(FLOOR(MONTHS_BETWEEN(SYSDATE,EFF_DATE)/12))                                                   
    -------------------------------------------------                                                   
                                                    2                                                   
    
    SQL> SELECT SUM(FLOOR(MONTHS_BETWEEN(SYSDATE,EFF_DATE)/12)) FROM MN_EMP_DEP_DET
      2  WHERE EMPID=1657 AND CO_TKT=1;
    
    SUM(FLOOR(MONTHS_BETWEEN(SYSDATE,EFF_DATE)/12))                                                     
    -----------------------------------------------                                                     
                                                  5                                                     
    I want the last SUM query into my DIS_CAL



    Regards

    Edited by: Moazam Shareef on Jan 4, 2013 11:25 PM
  • 4. Re: Date Calculating...
    Andreas Weiden Guru
    Currently Being Moderated
    Then you have to use two calculated items. The first one is the one you already have, but put the item in the same block where your date-items are. Then, create a second calculated tem of type summary and sum up the first calculated item.
  • 5. Re: Date Calculating...
    Moazam Shareef Explorer
    Currently Being Moderated
    thx Andreas it worked...

Legend

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