Re: Problem trying to update a record with a value returned by a function

rjm2004_at_shaw.ca (Bob M) wrote in message news:<e151b418.0402111436.117a9e19_at_posting.google.com>...
> Hi all,
>
> I am trying to update a record with a function value. Here is the
> function:
>
> update ITSM_SER_CUSTOM_FIELDS
> set SCF_SCDATE2 = SLA_PENDING_DATE.GET_DL(2292,'servicecall')
> where SCF_SER_OID = (select SER_OID from ITSM_SERVICECALLS where
> SER_ID = 2292);
>
> When I run this statement, I get the message "1 row updated". After
> doing a commit, I look at the record in question and the SCF_SCDATE2
> field is blank.
>
> When I run the statement:
>
> select SLA_PENDING_DATE.GET_DL(2292,'servicecall') from dual;
>
> I get a valid date returned.
>
> If I modify my SQL statement to substitute "sysdate" for the function
> value, the current date appears in the field.
>
> Any ideas why is the date field not being updated in the db when I use
> the function?
>
> Thanks for any help,
> Bob

Bob, verify that the table column type and the function return type are the same or add the necessary explicit conversion.

You show that the function returns the expected value, but does the subquery return the expected value.

Also by blank, do you mean NULL?

HTH -- Mark D Powell -- Received on Thu Feb 12 2004 - 18:36:56 CET