| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Peculiar results from a simple looking query.
"Mark C. Stock" <mcstockX_at_Xenquery .com> wrote:
>
>"Turkbear" <john.g_at_dot.spamfree.com> wrote in message
>news:1109341827.35efa72056ec7eba407c1bd950bb5643_at_teranews...
>> "Mark C. Stock" <mcstockX_at_Xenquery .com> wrote:
>>
>>>
>>>"Pedro Lopes" <pedro.lopes_at_netvisao.pt> wrote in message
>>>news:newscache$xoyfci$yyb$1_at_newsfront4.netvisao.pt...
>>>> Hi,
>>>> Why don't you stop hunting bugs and
>>>> focus on the resolution of the business problems
>>>> the simplest way possible ?
>>>>
>>>> Every piece of software have bugs. Live with it !
>>>> And if this is really a problem for you (if it's stopping
>>>> you from doing your work) open a TAR on metalink.
>>>>
>>>>
>>>> :)
>>>>
>>>> pedro
>>>>
>>>nice addition, pedro!
>>>
>>>yes, the OP should open a TAR. but I for one appreciate knowing that there
>>>is a problem with the syntax that he's trying and would welcome more
>>>'heads-up' like this. plus, i'm quite sure we are all aware that oracle
>>>support isn't always the best at trying out alleged bugs, so i don't think
>>>it's too bad an idea to post a test case here. and, as an instructor and
>>>consultant, i certainly want to increase my knowledge of what things don't
>>>quite work, whether or not its a feature or syntax that i generally use
>>>
>>>all in all, the OP's issue was not really all that different from your
>>>recent attempts to obfuscate number columns -- from which we all learned
>>>that 10g release 2 will have it built in
>>>
>>>++ mcs
>>>
>>
>>
>> Let's reexamine what CAST does in the context of a query:
>>
>> The Conversion requested by the CAST statement is in effect throughout the
>> life of that query and applies to that affected
>> column however many times after the cast it is referenced. In effect you
>> have created a virtual field named the same as the
>> original, uncasted, one and the query is accessing that virtual one -
>> hence 2 characters long, like it was redefined.
>>
>>
>>
>
>That is the observed behavior, but where is that documented? the docs (SQL
>Reference, function description) use the same description for CAST behavior
>as for TO_DATE, TO_CHAR, and TO_ETC..., behavior which to not create virtual
>columns. What am I overlooking? Where is the exact scope of the CAST
>*function* documented?
>
>++ mcs
>
>
The docs do not seem to indicate scoping info for ANY functions - Oracle docs are not known for completeness..
I did discover that it affects the field regardless of whether it is referenced before or after the use of CAST on it..
DEPT_NM CNAME ------------------------------ ----- Real Real (I) S (I) S
SQL> select dept_nm from employee
2 where rownum < 3;
DEPT_NM
SQL>
 |
 |