How to calculate multiple records using post-query in oracle forms

Question

I have multiple number datatype records sorted in Oracle database. I am trying to calculate them to get the result, where I want to display it on DISPLAY_ITEM.

I did database item property: no; and datatype: number

I tried post-query trigger on datablock, then i wrote the trigger:

BEGIN
    SELECT sal + allow1 + allow2
    INTO   :display_item1
    FROM   employees
EXCEPTION
    WHEN no_data_found THEN
      :all_allow := 0;;
END;

I got the error

frm-40735; POST-QUERY trigger raised unhandled exception ORA-01422

Answer 1

You can only select 1 row into items. In this case ora-01422 means you got more then 1 row.

The trigger post-query fires for every row, so if you want this to happen for every row just do the count for that row only instead of all rows together.

If you really need to make the count for all those rows you could use the SUM function or so.

Answer 2

If I'm understand you correctly and you want to have one item displaying the total of three different columns in the table this is what I should do. Create three hidden items for columns sal, allow1 and allow2. With hidden items I mean database items that have no canvas specified (a.k.a. NULL canvas items). Then I create a none database item to be be displayed to the user. For this item I then set Calculation Mode property to Formula and the Formula property to ':my_block.sal + :my_block.allow1 and my_block.allow2'.

Alternatively you can use the Post-Query trigger to populate the if we can call it 'formula' item but still you need to return all three columns into separate items. I don't like the use of summary items because that has a slightly different meaning inside forms.

If you don't want to do that and you could possible also built your block on a from clause and there directly in the sql do the computation.

Yet another solution should be to create a database side view to do the computation and create the form block based on the view instead of the table. This is by the way not a bad way of creating forms to separate the form itself from the underlying tables. This giving you freedom to change database structure without invalidating the form.

Many possibilities depending what your needs are but personally I should go for the formula item.