3 Replies Latest reply: Dec 13, 2012 1:52 PM by Ewaver RSS

    Update all rows in a View Column via one Apex Form field

    Ewaver
      Appreciate any helpful answers to this query.

      I have an Apex App (4.x) where I have a view: PRICE_MODEL_CRA_V

      In the view, I have the following applicable columns:

      COUNTRY
      GRADE
      SALARY
      In the form I have the following fields

      :P16_COUNTRY (DROPDOWN)
      :P16_GRADE (DROPDOWN)
      :P16_SALARY (Number)
      In my app, I have a form with an Update button. I am simply trying to get Apex to update all rows in the salaray column in the view, where the country and grade match.

      I've the following code for the Update button:

      UPDATE PRICE_MODEL_CRA_V
      SET SALARY = :P16_SALARY
      WHERE COUNTRY = :P16_COUNTRY
      AND GRADE = :P16_GRADE

      The option I have chosen in condition type is:

      Value of Item / Column expression is NOT Null (as there are values in the column)

      Also tried SQL Expression and it does not like..says that there is No Expression.

      Thanks again if anyone can help :)
        • 1. Re: Update all rows in a View Column via one Apex Form field
          Andreas Wismann
          Ewaver wrote:
          Also tried SQL Expression and it does not like..says that there is No Expression.
          Not sure what you mean with that.

          Did you ensure that the item name in the Expression 1 field has no leading :COLON?
          What if you just apply a division by zero to your Conditions and Updates to see whether they run at all?

          Condition Type:
          SQL Expression

          Expression 1:
          1=1/0

          If there is no exception then you know the process is not being called at all.

          Or switch on Debugging and just have a look at the session debug report.
          • 2. Re: Update all rows in a View Column via one Apex Form field
            Ewaver
            Hi,

            Sorry I realize now that piece of information was not well supported.

            If I choose SQL Expression for Condition Type in my Form Page and I try to Apply Changes
            Apex says this..

            •Failed to parse SQL query!
            ORA-00936: missing expression

            I was assuming that I need to have the colon for the items in the form where I am wanting Apex to grab the values entered by the user. I tried them without the colons with the SQL Expression condition and same result.
            UPDATE PRICE_MODEL_CRA_V
            SET SALARY = P16_SALARY
            WHERE COUNTRY = P16_COUNTRY
            AND GRADE = P16_GRADE

            I did put 1-1/0 in my expression field and applied changes and then tried ot submit the form and it does not produce an error, so perhaps it is not being called? Where would I start with troubleshooting that?

            Thanks,
            • 3. Re: Update all rows in a View Column via one Apex Form field
              Ewaver
              Maybe I could post this question differently.

              How would an Apex developer go about creating a report and form that his/her users access and when they enter data into the Salary form field, it updates ALL of the data in that column?

              Creating reports and forms are not a concern for me, what i am after is, how can you have someone say, I want the Salary for all people in this country to be 10000. Submit..and Apex processes 10000 for all rows in the table.

              Does the SQL or PL/SQL code reside as an expression in the Submit (update) button, Row Processing expression, etc..?

              Hope this is more clear and really appreciate your help as always.

              Edited by: Ewaver on Dec 13, 2012 11:51 AM