This discussion is archived
3 Replies Latest reply: Dec 13, 2012 11:52 AM by Ewaver RSS

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

Ewaver Newbie
Currently Being Moderated
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 Explorer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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

Legend

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