Re: Table and column with same name, fails only if using bind parameter

Hi,

[Quoted] Without meaning to be patronising, have you checked all the data types being used. In first query you are using a number inside a set of quotes. What datatype is p_price_lev (if it is fixed length comprisons may not work) What datatype is the column pricing_level, NUMBER, CHAR, VARCHAR2 etc.

The names shouldn't be causing a problem, if you think they are alias the table name.

Regards.

Nick Havard

"Larry Leonard" <Spam_at_DefinitiveSolutions.com> wrote in message news:27270a53.0302040515.1667e314_at_posting.google.com...
> I have a table called PRICING_LEVEL, which also has a column called
> PRICING_LEVEL. (No, I'm not the one that did this.)
>
> The problem is, while this correctly returns the one row I expect:
>
> SELECT description
> FROM pricing_level
> WHERE pricing_level.pricing_level = '41';
>
> ... this always incorrectly returns zero rows:
>
> SELECT description
> FROM pricing_level
> WHERE pricing_level.pricing_level = :p_price_lev;
>
> That is, when I use bind parameters, it fails to return the correct
> row; no error is generated. Interestingly, it fails using either a
> CRecordset-derived class (ODBC), or an ODynaset-derived class (oo4o).
>
> The workaround is, of course, to not use bind parameters for this
> particular query, but I'm wondering if anyone could help me understand
> exactly *why* this is failing. Obviously Oracle is getting confused
> (understandably) by the naming confusion, but I'm looking for a way to
> understand how and why. Any input is appreciated. TIA.
Received on Thu Feb 06 2003 - 15:01:01 CET