Re: Discrepencies between trace file and query ?

> 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.
>

Hi. Thanks for this information. Here are some more details:

I need v_period to be a character string for display purpose, and it's stored like this in the database (in .the table updated by this procedure) The columns CUSTOMER_PERFORMANCE.YEAR and CUSTOMER_PERFORMANCE.PERIOD are of type number.

Given this, how would you rephrase the 2 predicates?
> > and YEAR = to_number(substr(v_period,1,4))
> > and PERIOD = to_number(substr(v_period,5,2))
Must I do something like:
v_period_yyyy := to_number(substr(v_period,1,4)) v_period_mm := to_number(substr(v_period,5,2))

    then
and YEAR = v_period_yyyy
and PERIOD = v_period_mm

I still don't understand why the function appears not to be evaluated. I don't see how a to_char(v_period,'yyyy') would work any better in the circumstance. It's still a function, and as I understand it it would also need to be enclosed into a to_number().
If you can explain further.

Thanks so much.

--

Syltrem
http://pages.infinit.net/syltrem (OpenVMS related web site - en français)
To reply to myself directly, remove .spammenot from my address

"Sybrand Bakker" <postbus_at_sybrandb.demon.nl> a écrit dans le message de
news: ul19p12mvfof62_at_corp.supernews.com...


>

> "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

>

>

>