This discussion is archived
4 Replies Latest reply: Mar 4, 2012 6:42 AM by 920172 RSS

Using computation to insert a value in an existing item?

920172 Newbie
Currently Being Moderated
Hello.

I'm experimenting with computations. I'm quite new to APEX, but I really need to make sure this works.
So, this computation, it's supposed to take a value of an item (in which a user has entered a number), and then make a computation with that value. You'll see it in the code below.

Some extra info: when a user enters a certain value of weight (let's say 80 gram), and then presses enter in the text field, this computation runs. Now, it does run because I've seen so by checking the session. But I haven't managed out on how to display the results. So, I was thinking about placing the results in items, which are then again displayed in a region.

What I'm wondering is, how do I edit my source code in such a way that the return values will be inserted in already existing page items?

This is my computation source code:
DECLARE
a_unit varchar2(20);
a_old_kcal float;
a_new_kcal float;
a_old_carbs float;
a_new_carbs float;
a_old_weight float;
a_new_weight float;

BEGIN
SELECT standardamount INTO a_old_weight,
kcal INTO a_old_kcal,
carbs INTO a_old_carbs,
name INTO a_unit
FROM foodunit
WHERE foodunit.foodunitid = :P17_SET_UNIT;
IF a_unit := 'gram' THEN
a_new_kcal := a_old_kcal/100 * *:P17_SET_WEIGHT;*
a_new_carbs := a_old_carbs/100 * *:P17_SET_WEIGHT;*
a_new_weight := :P17_SET_WEIGHT;
ELSE
a_new_kcal := a_old_kcal;
a_new_carbs := a_old_carbs;
a_new_weight := a_old_weight;
END IF;

RETURN a_new_weight;
RETURN a_new_carbs;
RETURN a_new_kcal;
END;


I want this to return the a_new_weight, a_new_carbs and a_new_kcal as items. I've made items named P17_NEW_WEIGHT, P17_NEW_CARBS and P17_NEW_KCAL already, and my plan is to insert the values of a_new_weight into P17_NEW_WEIGHT, and so on.

How can I change my computation source code in such a manner that it will insert these values into the items mentioned above?


Thank you for your help!

PS: please do leave a reply, even if it's not a full answer. Any help is welcome, and greatly appreciated. ;)



Best regards,

Magali
  • 1. Re: Using computation to insert a value in an existing item?
    fac586 Guru
    Currently Being Moderated
    Magali wrote:
    Hello.

    I'm experimenting with computations. I'm quite new to APEX, but I really need to make sure this works.
    You'll get a faster, more effective response to your questions by including as much relevant information as possible upfront. This should include:

    <li>Full APEX version
    <li>Full DB/version/edition/host OS
    <li>Web server architecture (EPG, OHS or APEX listener/host OS)
    <li>Browser(s) and version(s) used
    <li>Theme
    <li>Template(s)
    <li>Region/item type(s)
    So, this computation, it's supposed to take a value of an item (in which a user has entered a number), and then make a computation with that value. You'll see it in the code below.
    So have you read the documentation on Computattions?
    Some extra info: when a user enters a certain value of weight (let's say 80 gram), and then presses enter in the text field, this computation runs. Now, it does run because I've seen so by checking the session.
    "Checking the session" how? The computation code below can't run as it contains syntax errors:
    SQL> var p17_set_unit varchar2(10)
    SQL> var p17_set_weight number
    SQL> DECLARE
      2  a_unit varchar2(20);
      3  a_old_kcal float;
      4  a_new_kcal float;
      5  a_old_carbs float;
      6  a_new_carbs float;
      7  a_old_weight float;
      8  a_new_weight float;
      9
     10  BEGIN
     11  SELECT standardamount INTO a_old_weight,
     12  kcal INTO a_old_kcal,
     13  carbs INTO a_old_carbs,
     14  name INTO a_unit
     15  FROM foodunit
     16  WHERE foodunit.foodunitid = :P17_SET_UNIT;
     17  IF a_unit := 'gram' THEN
     18  a_new_kcal := a_old_kcal/100 * :P17_SET_WEIGHT;
     19  a_new_carbs := a_old_carbs/100 * :P17_SET_WEIGHT;
     20  a_new_weight := :P17_SET_WEIGHT;
     21  ELSE
     22  a_new_kcal := a_old_kcal;
     23  a_new_carbs := a_old_carbs;
     24  a_new_weight := a_old_weight;
     25  END IF;
     26
     27  RETURN a_new_weight;
     28  RETURN a_new_carbs;
     29  return a_new_kcal;
     30* end;
    SQL> /
    kcal INTO a_old_kcal,
         *
    ERROR at line 12:
    ORA-06550: line 12, column 6:
    PL/SQL: ORA-00923: FROM keyword not found where expected
    ORA-06550: line 11, column 1:
    PL/SQL: SQL Statement ignored
    ORA-06550: line 17, column 11:
    PLS-00103: Encountered the symbol "=" when expecting one of the following:
    . ( * @ % & = - + < / > at in is mod remainder not rem then
    <an exponent (**)> <> or != or ~= >= <= <> and or like like2
    like4 likec between || multi set
    But I haven't managed out on how to display the results. So, I was thinking about placing the results in items, which are then again displayed in a region.

    What I'm wondering is, how do I edit my source code in such a way that the return values will be inserted in already existing page items?
    This is what computations are used for, but the docs say: +Use page computations to assign a value to an identified item when a page is submitted or displayed.+ i.e. a single value to a single page or application item.
    This is my computation source code:
    Always post code wrapped in tags<tt>\
    ...\
    </tt> tags
    to preserve formatting and special characters.
    DECLARE
    a_unit varchar2(20);
    a_old_kcal float;
    a_new_kcal float;
    a_old_carbs float;
    a_new_carbs float;
    a_old_weight float;
    a_new_weight float;

    BEGIN
    SELECT standardamount INTO a_old_weight,
    kcal INTO a_old_kcal,
    carbs INTO a_old_carbs,
    name INTO a_unit
    <tt>INTO</tt> only appears once in the <tt>SELECT...INTO</tt> statement.
    FROM foodunit
    WHERE foodunit.foodunitid = :P17_SET_UNIT;
    IF a_unit := 'gram' THEN
    a_new_kcal := a_old_kcal/100 * *:P17_SET_WEIGHT;*
    a_new_carbs := a_old_carbs/100 * *:P17_SET_WEIGHT;*
    a_new_weight := :P17_SET_WEIGHT;
    ELSE
    a_new_kcal := a_old_kcal;
    a_new_carbs := a_old_carbs;
    a_new_weight := a_old_weight;
    END IF;

    RETURN a_new_weight;
    RETURN a_new_carbs;
    RETURN a_new_kcal;
    END;
    Multiple <tt>RETURN</tt> statements like this won't work. Such code compiles but should generate a warning about unreachable code.
    I want this to return the a_new_weight, a_new_carbs and a_new_kcal as items. I've made items named P17_NEW_WEIGHT, P17_NEW_CARBS and P17_NEW_KCAL already, and my plan is to insert the values of a_new_weight into P17_NEW_WEIGHT, and so on.

    How can I change my computation source code in such a manner that it will insert these values into the items mentioned above?
    You're trying to to compute values for 3 items, so you could use 3 computations, but as the calculations are related and based on the results of a query that you don't want to repeat, so doing this using a process is a better idea.

    You need to remove the computation and create a page process, using correct <tt>SELECT...INTO</tt> syntax, that assigns values to the page items using standard bind variable syntax, e.g.
    :P17_NEW_KCAL := a_old_kcal/100 * :P17_SET_WEIGHT;
  • 2. Re: Using computation to insert a value in an existing item?
    920172 Newbie
    Currently Being Moderated
    Apex version: 4.1.1.00.23
    Host OS: At home it's Windows 7 32-bit, at work it's Oracle Linux.
    Browser: At home it's Google Chrome, at work it's Firefox.
    Theme: Theme 18, simplified gray
    Templates: Page 17 has 10 templates. The region the computation uses is a Reports region, alternative 1.
    Items: the items used are mostly named in the code I mentioned.


    With "checking the session", I mean running the application and then clicking on the "Session" button at the bottom of the page. It's located in a menu, which I assume is for developing purposes.

    I DID read the documentation, but it doesn't really makes sense to me.
    As I said before, I'm quite new to APEX so it will take me a while until I sort things out properly.
    I'm more of the type of person that has to learn things by doing them.

    I haven't tried running the code in "SQL commands", I just made a computation on my page (page 17) and it didn't gave any errors when I continued. If it would have had a mistake, then I would get a notification saying something is wrong with my code.


    Sorry about not having posted my code in the corresponding tags, I didn't knew. Sorry, I will do this from now on.

    About the INTO statement, I can only use that once?


    So, basically, I should make 3 computations: one for the weight, one for the carbs, and one for the kcal.



    I know that I make a lot of mistakes, but I'm really doing my best.
    I'm happy you answered my question, you're helping me and that's what I'm thankful for.
  • 3. Re: Using computation to insert a value in an existing item?
    fac586 Guru
    Currently Being Moderated
    Magali wrote:

    With "checking the session", I mean running the application and then clicking on the "Session" button at the bottom of the page. It's located in a menu, which I assume is for developing purposes.
    The developer menu also provides access to the Debug features, which will provide a detailed view of what's going on.
    I DID read the documentation, but it doesn't really makes sense to me.
    As I said before, I'm quite new to APEX so it will take me a while until I sort things out properly.
    I'm more of the type of person that has to learn things by doing them.

    I haven't tried running the code in "SQL commands", I just made a computation on my page (page 17) and it didn't gave any errors when I continued. > If it would have had a mistake, then I would get a notification saying something is wrong with my code.
    The fact you haven't suggests that you've created a Static Assignment computation. All this does is set the value of the item to be the "code" you entered. No PL/SQL code has actually been executed. It appears the intention was to use a PL/SQL Function Body computation, which will give an error message in the builder with the code posted.
    About the INTO statement, I can only use that once?
    Yes, as described in the linked PL/SQL documentation:
    select standardamount, kcal, carbs, name
    into   a_old_weight, a_old_kcal, a_old_carbs, a_unit
    from   foodunit
    where  foodunitid = :p17_set_unit;
    (You should also avoid using Oracle reserved words like <tt>name</tt> as identifiers for tables, columns, variables, programs etc.)
    So, basically, I should make 3 computations: one for the weight, one for the carbs, and one for the kcal.
    No&mdash;as suggested above&mdash;use a page process to avoid repeated database queries.
  • 4. Re: Using computation to insert a value in an existing item?
    920172 Newbie
    Currently Being Moderated
    Thank you, fac586! Because of you it now all works well.
    I got rid of the computations and made a process instead. This process starts on submit (that being when the user presses a submit button).
    I named it 'calculation' and the source of the process is as follows:
    DECLARE
    a_old_weight FLOAT;
    a_old_kcal FLOAT;
    a_old_carbs FLOAT;
    a_old_protein FLOAT;
    a_old_fat FLOAT;
    
    BEGIN
    SELECT standardamount, kcal, carbs, protein, fat
    INTO a_old_weight, a_old_kcal, a_old_carbs, a_old_protein, a_old_fat
    FROM foodunit
    WHERE foodunit.foodunitid = :P17_SET_UNIT;
    
    IF :P17_SELECT_UNIT2 = 'gram' THEN
    :P17_NEW_WEIGHT := a_old_weight/100 * :P17_SET_WEIGHT;
    :P17_NEW_KCAL := a_old_kcal/100 * :P17_SET_WEIGHT;
    :P17_NEW_CARBS := a_old_carbs/100 * :P17_SET_WEIGHT;
    :P17_NEW_PROTEIN := a_old_protein/100 * :P17_SET_WEIGHT;
    :P17_NEW_FAT := a_old_fat/100 * :P17_SET_WEIGHT;
    
    ELSE
    :P17_NEW_WEIGHT := a_old_weight * :P17_SET_WEIGHT;
    :P17_NEW_KCAL := a_old_kcal * :P17_SET_WEIGHT;
    :P17_NEW_CARBS := a_old_carbs * :P17_SET_WEIGHT;
    :P17_NEW_PROTEIN := a_old_protein * :P17_SET_WEIGHT;
    :P17_NEW_FAT := a_old_fat * :P17_SET_WEIGHT;
    END IF;
    
    END;
    You'll notice that I don't use a_unit anymore. Now I use the value of a page item, named P17_SELECT_UNIT2. This is a hidden item, it's source is like this:
    SELECT name
    FROM foodunit
    WHERE foodunitid = :P17_SET_UNIT
    P17_SET_UNIT is a select list, it's allows a user to pick a unit (gram or piece) for the corresponding piece of food. This piece of food is first selected out of a report with clickable row names.



    I'm just sharing my solution so that others can see the result. If, however, there still seems to be an error in my solution, or if you think this could be done in an easier or shorter way, do let me know.

Legend

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