Re: Peculiar results from a simple looking query.

"Turkbear" <john.g_at_dot.spamfree.com> wrote in message news:1109275195.7199aaac62c59c7f38ddabcf5312bb40_at_teranews...
> ak_tiredofspam_at_yahoo.com wrote:
>
>>Problem reproduced on 9.2.0.1.0
>>Steve, you have build a very good case
>
>
> Cast CONVERTS the given field ( on the fly) to the type specified, then
> queries it, so it will only have 2 chars in its
> converted form.
>
> The docs are a nice thing..try them ..
>
>

this looks odd (using 10.1.0.2.0)

  1 select ename, cast(ename as varchar2(2)), lower(ename)   2* from emp
SQL> / ENAME CA LOWER(ENAM
---------- -- ----------

sm         sm sm
AL         AL al
WA         WA wa

docs do say:

Purpose
CAST converts one built-in datatype or collection-typed value into another built-in
datatype or collection-typed value.

however, docs also say:

Purpose
TO_CHAR (datetime) converts a datetime or interval value of DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, or TIMESTAMP WITH LOCAL TIME ZONE datatype to a value of VARCHAR2 datatype in the format specified by the date format fmt. If
you omit fmt, then date is converted to a VARCHAR2 value as follows:

and I've never seen a TO_CHAR in the select list applied globally to the query... multiple expressions on the same column have always been independently evaluated

so, although maybe substr is the preferred (traditional) way, is say, if it parses, it should give correct results

BUG! ++ mcs Received on Thu Feb 24 2005 - 16:21:14 CST