This discussion is archived
2 Replies Latest reply: Feb 5, 2013 11:00 AM by user757151 RSS

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

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

Legend

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