Re: Discrepencies between trace file and query ?

"Syltrem" <syltremspammenot_at_videotron.com> wrote in message news:LCV39.7917$H67.42452_at_tor-nn1.netcom.ca...
> Hi
>
> Oracle 8.1.6.0.0 On OpenVMS 7.2-1.
>
> In a query, I have:
> and YEAR = to_number(substr(v_period,1,4))
> and PERIOD = to_number(substr(v_period,5,2))
>
> And in the trace file I see (event 10046 level 4)
> bind 3: dty=96 mxl=32(06) mal=00 scl=00 pre=00 oacflg=13 oacfl2=1 size=0
> offset=96
> bfp=01caed58 bln=32 avl=06 flg=01
> value="200205"
> bind 4: dty=96 mxl=32(06) mal=00 scl=00 pre=00 oacflg=13 oacfl2=1 size=0
> offset=128
> bfp=01caed78 bln=32 avl=06 flg=01
> value="200205"
>
> v_period has a value of '200205'.
> It looks as though Oracle does not evaluate my functions (to_number and
> substr)
> It also don't find the rows like it would if the bind variables would hold
> the expected values.
>
> Why?
>
> Thanks!
>
> The whole query goes like this:
> Cursor Avg_Pay_Days_Cur
> Is Select AR_AVERAGE_PAYMENT_DAYS
> From FINANCE.CUSTOMER_PERFORMANCE
> Where COMPANY_CODE = v_Company_Code
> and DIVISION = Aging_Hdr_Rec.Division
> and CUSTOMER_NUMBER = Aging_Hdr_Rec.Cust_no
> and YEAR = to_number(substr(v_period,1,4))
> and PERIOD = to_number(substr(v_period,5,2))
> and SYS_CURRENCY_CODE = Aging_Hdr_Rec.Currency;
>
> v_Period char(6);
>
> --
>
> Syltrem
> http://pages.infinit.net/syltrem (OpenVMS related web site - en français)
> To reply to myself directly, remove .spammenot from my address
>
>
>

No discrepancies for sure. The bind variable is v_period, not the complete expression. So it is actually doing what it should do. Questions are of course: what is the datatype of year and period, and is there no reason there is another implicit conversion. Personally I would have used a date variable for v_period and avoided the intricacies of substr and to_number, a to_char(v_period,'yyyy') definitely being more straightforward.

Hth

--
Sybrand Bakker
Senior Oracle DBA

to reply remove '-verwijderdit' from my e-mail address