Home » SQL & PL/SQL » SQL & PL/SQL » Query Giving Wrong Output
Query Giving Wrong Output [message #318710] Wed, 07 May 2008 12:54 Go to next message
hedonist123
Messages: 119
Registered: August 2007
Senior Member
Hi,

I have run into a maddening issue,

The following query

 UPDATE POB_VENDOR_CLAIM
   SET  POB_EMAIL_SENT = 0   
 WHERE POB_VENDOR_CLAIM_ID = piVendorClaimId AND
 EXISTS ( SELECT 
            1
          FROM
            POB_VENDOR_CLAIM
          WHERE
          (POB_VENDOR_SHIP_ADDRESS <> psPobVendorShipAddress OR
          NVL(POB_VENDOR_SHIP_ADDRESS_2,'0') <> NVL(psPobVendorShipAddress2,'0') OR
          VLD_COUNTRY_ID <> piVldCountryID OR
          NVL(POB_REGION,'0') <> NVL(psPobRegion,'0') OR
          POB_CITY <> psPobCity OR
          POB_SHIP_POSTAL <> psPobShipPostal OR
          NVL(POB_VENDOR_PAYS_FREIGHT,2) <> NVL(piPobVendorPaysFreight,2) OR
          POB_DC_END_DTTM <> pdDCEndDate) AND
          POB_VENDOR_CLAIM_ID = piVendorClaimId AND
          POB_VENDOR_CLAIM_STATUS = 20 AND
          INACTIVE = 0);


should not update the field to 0 if the conditions are not met.

However, when I test the stored procedure of which this is a part, the status gets updated.

But, at the same time, if I run this query separately after replacing the parameter variables with the parameters passed, it does not update.

All the variables in lower case are parameters.

From your experience, would you have an idea as to why this is happening?

Thanks,

Sharath
Re: Query Giving Wrong Output [message #318756 is a reply to message #318710] Wed, 07 May 2008 19:50 Go to previous messageGo to next message
harrysmall3
Messages: 109
Registered: April 2008
Location: Massachusetts
Senior Member
Hi Sharath,

I agree situations like that become maddening very easily!
Off-hand without seeing the data values as they are written
in replacement of the parms (and not knowing of a pl/sql vs sql
nuance in these regards) I can only suggest a quick way
to debug which I would take were i in the same situation.

After selecting the one record from POB_VENDOR_CLAIM
which you are working from I would put dbms_output displays
in the stored procedure for that vendor claim id to
see with my eyes that they are truly the same.

My hunch says its the dates. Now are you truly plugging in
the param literals or doing a DECLARE/BEGIN/END block and
setting the param values as local variables?

I wonder if there is a time-portion of the date coming into play in one scenario vs the other?

Just curious - why the 'EXISTS' clause where it is the same
table? I.e, instead of just adding those 'where' criteria to
the main statement?

In anycase, another verification method would to be to create
a one row table, say POB_VENDOR_CLAIM_X, with the record that
has the values that you want (selected from POB_VENDOR_CLAIM for that ID of course).

Then in the standalone SQL,
use POB_VENDOR_CLAIM_X table and values to reference in the
EXIST clause. If that works, then its how you are typing
the literals; If that does not work then a display in the proc
is needed to see what is different about one of those params.
Something must be - I know, that is the maddening part!.

If any of the params do not come from the POB_VENDOR_CLAIM then
the last method is moot and I'd jump right to getting displays
from the procedure and then executing the below as sql with careful consideration for the date literals.

Good luck - if you can provide the query as it is right before
you execute it standalone, i'd be glad to give it another once over.

Regards,
Harry
p.s. - Also, make sure a commit isnt being missed in the proc if
you run it for one rec if it is part of a larger process which
would commit down the line after x iterations or what not.
Re: Query Giving Wrong Output [message #318757 is a reply to message #318756] Wed, 07 May 2008 19:53 Go to previous messageGo to next message
harrysmall3
Messages: 109
Registered: April 2008
Location: Massachusetts
Senior Member
One more thing - keep vigilant of space padding. I would
add to the dbms_output displays the length of all strings
So you know if '1234' isnt really '1234 '.
-Harry
Re: Query Giving Wrong Output [message #318808 is a reply to message #318710] Thu, 08 May 2008 01:11 Go to previous message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
> From your experience, would you have an idea as to why this is happening?

The parameters or data are different.

> Just curious - why the 'EXISTS' clause where it is the same table? I.e, instead of just adding those 'where' criteria to the main statement?

Because it would behave differently (if it does what it is supposed to do). As the subquery in EXISTS clause does not have any join to the outer SELECT, it returns the same value (true or false) for all rows.
So I would start investigating that subquery - which condition (parameter) causes it does not return any row.

By the way, you want to update POB_EMAIL_SENT in all rows with (given) POB_VENDOR_CLAIM_ID, if there is one row among them with that specific condition in EXISTS clause. Not a good design storing POB_EMAIL_SENT in POB_VENDOR_CLAIM table - it breaks 2nd Normal Form. I would suggest you to normalize the model. Either compute POB_EMAIL_SENT dynamically (eg. in view) or create new table with columns POB_VENDOR_CLAIM_ID - primary key - and POB_EMAIL_SENT.
Previous Topic: ORA-22950: cannot ORDER objects without MAP or ORDER method
Next Topic: bulk collect
Goto Forum:
  


Current Time: Sun Dec 11 00:41:30 CST 2016

Total time taken to generate the page: 0.10393 seconds