This forum is currently locked. You can't register or post questions at this time. (read more)

Update stms works diff manner when Func used in Where clause

All posts relating to Oracle database administration.

Moderator: Tim...

mail.madhankumar
Senior Member
Posts: 87
Joined: Tue May 15, 2012 2:47 pm

Update stms works diff manner when Func used in Where clause

Postby mail.madhankumar » Wed Jul 03, 2013 11:30 am

Hi Tim,

I hope you are well.. We are facing problem in Update statement when Function used in Where clause.

Below is the Query.

Code: Select all

UPDATE FS_FR_HOUSEDOCCHARGES HC
   SET HC.IS_VOID = 'Y'
WHERE HC.SLNO = getchgorginalseq('FUSSLC1818475', 9)
   AND HC.HOUSEDOCID = 'SLA000000205'
   AND ischginvoiced(HC.HOUSEDOCID, HC.SLNO, NULL) = 'N';

Problem with ischginvoiced(HC.HOUSEDOCID, HC.SLNO, NULL) fuction.
Samething works fine for select statement, only works different manner when we use update statement.

Function returns value 'Y' , there is no record with 'N'. but still updates 1 record.
if i change value 'Y' instead of 'N'. it updates no record. but it should update 1 recod..
it works opposite way. but with same where clause condition with select statement it works fine , what we expect.

Please tell me your suggestion.

Regards,
Madhan

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Update stms works diff manner when Func used in Where cl

Postby Tim... » Wed Jul 03, 2013 12:13 pm

Hi.

Not really much I can say about this, since it is really all about your function.

My guess would be it really isn't returning "Y" as you believe. The only way you will know is to add some tracing to the function you you can see the value returned each time.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

mail.madhankumar
Senior Member
Posts: 87
Joined: Tue May 15, 2012 2:47 pm

Re: Update stms works diff manner when Func used in Where cl

Postby mail.madhankumar » Wed Jul 03, 2013 3:01 pm

Hi Tim,
I verified that function by putting in select statement. it returns 'Y' only. also it works in select statement with same where condition, but not works for Update statement.

Regards,
Madhan

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Update stms works diff manner when Func used in Where cl

Postby Tim... » Wed Jul 03, 2013 4:42 pm

Hi.

But during the update you are, by definition updating data. That and the possibility of triggers firing mean the data this function sees may be different to the data a straight query sees. The only way to be sure is to add some tracing code to the function and see what it is returning in-situ (when called from within the update). That way you will *know* what is returning!

The point is, only one of two things can be happening:

1) The function does not return what you think it does.

2) The equality operator you use everythere in your SQL is not working.

Faced with these two choices, I know which I would investigate first. :) Instrumenting your code is the easiest way to do that.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog


Return to “Oracle Database Administration”

Who is online

Users browsing this forum: No registered users and 0 guests

cron