2 Replies Latest reply: Feb 5, 2013 1:00 PM by User757151-Oracle RSS

    View Object query not working with a negative number passed to the bind var

    User757151-Oracle
      The View Object query below does not work as expected in one case. Forcontext, the VO is used in an EO business rule method validation. What happens is that when the user attempts to insert a new or update an existing record, the number returned by the EntityImpl function that gets the value of the MessageDetails.MESSAGE_NUMBER attribute is passed to the :v_MsgNum bind variable.

      The VO query works fine when updating existing records, but it does not when a new record is added.

      The MessageDetails.MESSAGE_NUMBER attribute is a foreign key to a header record, and the header record's attribute value is set with a Sequence. Before the record is submitted, it appears ADF uses a negative number temporarily as the attribute value.

      For example, if the EO Impl function to get the MessageDetails.MESSAGE_NUMBER returns -4, -4 is passed to the :v_MsgNum bind variable. I'd expect this query to filter out the new record that has the ADF-assigned -4 value (MESSAGE_NUMBER != :v_MsgNum). However, that record is being returned by the query.

      Is there anything I'm doing wrong here - is there some way to cause the query to work as expected.
      SELECT MessageDetails.MESSAGE_NUMBER, 
             MessageDetails.RELEASE_ID, 
             MessageDetails.RELEASE_ID AS RELEASE_ID1, 
             ReleaseCodes.RELEASE_ID AS RELEASE_ID2, 
             ReleaseCodes.ESR_REL_NUM, 
             MessageDetails.MESSAGE
      FROM MESSAGE_DETAILS MessageDetails, RELEASE_CODES ReleaseCodes
      WHERE MessageDetails.RELEASE_ID = ReleaseCodes.RELEASE_ID
       AND 
       MESSAGE_NUMBER IN
       (
        SELECT DISTINCT MESSAGE_NUMBER
        FROM MESSAGE_DETAILS
        WHERE LOWER(MESSAGE) = LOWER(:v_Msg)
        AND (MESSAGE_NUMBER != :v_MsgNum)
       )
      Am using 11g.

      Thanks for your help!

      Edited by: user757151 on Feb 4, 2013 10:20 AM