5 Replies Latest reply: Jan 6, 2013 12:11 AM by Moazam Shareef RSS

    Date Calculating...

    Moazam Shareef
      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
          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
            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
              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
                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
                  thx Andreas it worked...