14 Replies Latest reply: Dec 19, 2013 2:18 AM by 994122 RSS

    how to do a sum of text_field or a column with where condition?

    Vijetha

      Hi all,

      In Oracle forms 6i, i have created a form in which there are 5 text_Items (with 20 no of fields displayed) namely ACCOUNT FD NO, AMOUNT, INTEREST RATE, STATUS and INTEREST_YEAR.

       

      ACCOUNT FD NO

      AMOUNT

      INTEREST RATE

      STATUS

      INTEREST_YEAR

      47665

      50000

      1. 11.5

      E

      5750

      37463

      60000

      12

      D

      7200

      47651

      100000

      1. 12.5

      D

      12500

      34766

      70000

      11

      E

      7700

       

      I want to make sum of INTEREST_YEAR where status='E'.

      I have created a field by name TOTAL_INTEREST_YEAR in which i want to display the sum.

      How do i do sum with where condition ??

      Thank You.

       

      Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production

      Oracle Form Builder 6i.

        • 1. Re: how to do a sum of text_field or a column with where condition?
          Priyasagi

          Hi Vijetha,  The following discussion will help:

          Re: Help in calculation code

          • 2. Re: how to do a sum of text_field or a column with where condition?
            Andreas Weiden

            create an additional item in your tabular block. Set it as formula-item and in the formula write something like

             

            CASE WHEN :BLOCK.STATUS='E' THEN :BLOCK.INTEREST_YEAR ELSE 0 END
            

             

            Then create a summaryitem which sums that new formula-item

            • 3. Re: how to do a sum of text_field or a column with where condition?
              Vijetha

              Hi AW, actually i have already tried doing this before, but i was getting error for the formula i had created.

              Even now when i tried with your code , i 'm getting below error.

               

              PL/SQL ERROR 103 at line 1, column 1

              Encountered the symbol "CASE" when expecting one of the following:

              ( - + mode not null others <an identifier>

              <a double-quoted delimited-identifier> <a bind variable>

              avg

              count current exists max min prior sql stddev sum variance

              cast <a string literal with character set specification>

              <a number> <a single-quoted SQL string>

               

              HOW TO ADD FORMULA for a text_field IN ORACLE FORMS BUILDER 6I ???

              Can you provide me a link where i can learn how to code for formula??

              • 4. Re: how to do a sum of text_field or a column with where condition?
                Priyasagi

                Case when not work in forms 6i try decode it will help.

                • 5. Re: how to do a sum of text_field or a column with where condition?
                  Priyasagi

                  Hi Vijetha please try this,


                  when validate item trigger of :block.status


                  if :block.status='E' then

                       :block.<newly created column>:=:block.interest_year;

                  else

                       :block.<newly created column>:=0;

                  end if;


                  Then create a new summary item which sums the above mentioned <newly created column>.

                  Hope this will help you.

                  • 7. Re: how to do a sum of text_field or a column with where condition?
                    Priyasagi

                    Vijetha wrote:

                     

                    not working

                    Not working means?

                    Is it give any error?  or Is it give wrong value?

                    I am not able to see your desktop from here.  Please post some essential details.

                    There is no need for calculation field. 

                    On my previous post I suggested simple if command to store value into :<newly created column>.  That is working or not.

                    On which field you are stucking?  :status? :<newly created column>? or on your :<summary column>?

                    • 8. Re: how to do a sum of text_field or a column with where condition?
                      Andreas Weiden

                      Priyasagi is right, CASE won't work in 6i, try this as formaula instead

                       

                      DECODE(:BLOCK.STATUS, 'E', :BLOCK.INTEREST_YEAR, 0)
                      



                      • 9. Re: how to do a sum of text_field or a column with where condition?
                        CraigB

                        Vijetha,

                        When you write the formula for a calculated item, it does not allow you to use any PL/SQL expressions (to include the DECODE built-in) in Forms 6i.  If there was no conditional checks on your calculation, you could simply make your item a summary item and perform the summation on the interest_rate column.  However, since your calculation is dependent on the value of the STATUS column you will need to use a combination of a Calculated item and a Summarized item because you can't use an IF statement, DECODE or any other PL/SQL in the calculated item's formula.  Consequently, you will need to create a Function in the Program Units node and call the function in your formula.  I tested this using the following code and it worked correctly.

                         

                         

                        First, create the following Function in the Program Units node of the object navigator.

                         

                        FUNCTION calc_interest RETURN NUMBER IS
                             n_ret_val  NUMBER := 0;
                         BEGIN
                           IF ( :YOUR_BLOCK.STATUS = 'E' ) THEN 
                               n_ret_val := :YOUR_BLOCK.interest_rate;
                           END IF;
                           RETURN n_ret_val;
                        END calc_interest;
                        

                        First, you will need to change the BLOCK Property Query All Record = Yes

                        Then, open the property pallet of your calculated item and set the following properties:

                        1.  Calculation Mode = Formula

                        2.  Forumla property = CALC_INTEREST

                        3.  Database Item = No

                         

                        Now, create a second non-based table item in your block which will display the summarized interest amount.  Open the property pallet for this item and set the following properties:

                        1.  Data Type  = Number

                        2.  Calculation Mode = Summary

                        3.  Summary Function = Sum

                        4. Summarized Item = "Name of your Calculated Item"

                        5. Database Item = No

                        6. Canvas = "Your Canvas"

                         

                        When you query your block, you should see the sum of all records where the STATUS = 'E'.

                         

                        This worked for me in my sample form I created so it should work for you.

                         

                        Craig...

                        • 10. Re: how to do a sum of text_field or a column with where condition?
                          Vijetha

                          Thank You very much Craig, it works fine.

                           

                          But i have a small problem that is , when i press the button 'ENTER_QUERY' (push button) for the second time to query,

                          I'm getting the following error.

                           

                          FRM-41370: cannot modify calculated item KEC_FDACCT_MSTR.TOTAL_AMOUNT

                           

                          I browsed on net for the above error & it says the following.

                          If the calculated item is a formula item, then its formula determines its value at all times.

                          It may be appropriate to modify the formula. Or it may be appropriate to change the calculated item to

                          a non-calculated control item whose value is set in various triggers. Level: 99 Type: Error

                           

                          How do i suppress this error ??


                          • 11. Re: how to do a sum of text_field or a column with where condition?
                            CraigB

                            Interesting...I did not get this error in my test form, however, I was not calling the ENTER_QUERY() built-in, I was just using the Toolbar button to place the form in "Enter Query" mode.  To be honest, I'm not fond of supressing errors...I would rather eliminate the cause of the error.  I'll need to play with this a little more to see if I can replicate the error and solve it.  I'll have to get back with you on this.

                             

                            If you really want to just supress the error you can trap it in the On-Error trigger.

                             

                            Craig...

                            • 12. Re: how to do a sum of text_field or a column with where condition?
                              CraigB

                              Well, I've played around with my sample form and I can't replicate the error you are getting.  I can "Enter Query" mode through the toolbar as well as a button without any errors.  Double-check your calculated items to ensure the Database Item property is No.

                               

                              Craig...

                              • 13. Re: how to do a sum of text_field or a column with where condition?
                                Vijetha

                                I resolved that problem,

                                Actually , the problem is when I press 'EXIT' (PUSH-BUUTON) in Enter_Query mode; I'M GETTING THE FOLLOWING ERROR:

                                 

                                FRM-41370: CANNOT MODIFY CALCULATED ITEM

                                KEC_FDACCT_MSTR.TOTAL_AMOUNT.

                                 

                                Anyways , i will figure it out myself.

                                 

                                Thank You so much Craig..

                                • 14. Re: how to do a sum of text_field or a column with where condition?
                                  994122

                                  Hi Vijetha,

                                   

                                                     How can you solve your  "How to do a sum of text_filed with where condition?" Could you please let me know?

                                   

                                  Thank You