Re: simple things

In <5689if$cms_at_news.xs4all.nl>, sdv_at_xs4all.nl (Simon Verzijl) writes:
>mlanda_at_vnet.ibm.com wrote:
>
>>In <561kes$rin_at_news.xs4all.nl>, sdv_at_xs4all.nl (Simon Verzijl) writes:
>>>Can anyone tell me why this simplest query doesn't work ? :
>>>
>>>
>>>create table test (name char(20), number decimal(3) );
>>>insert into test values ('simon', 100);
>>>
>>>select number from test where name='simon';
>>>(so far so good)
>>>
>>>
>>>but this :
>>>
>>>select number from test where name=substr('simonblablablabla',1,5);
>>>
>>>doesn't return any rows.
>>>
>>>
>>>same goes for the following PL/SQL script (which was in fact the
>>>actual reason I started testing the above)
>>>
>>>create or replace
>>> procedure get_number(person in varchar2)
>>> is d_number integer;
>>> begin
>>> select number into d_number
>>> from test
>>> where name =person;
>>> end if;
>>> end;
>>>
>>>
>>>(Oracle version: Personal Oracle 7.2/ Oracle Workgroup Server 7.2)
>>>
>>>
>>>
>>>Simon
>>>
 

>>Interesting...
 

>>I surprised that your create table statement did not fail with an
>>error. 'Number' is a reserved word. I get an error (invalid column
>>name) using sqlplus 3.3 (7.3 RDBMS) when trying to create a table with
>>a column name of number defined.
>
>My mistake (that's what you get when you start renaming columns while
>writing a problem description <g>)
>
>
>>As far as I can tell there is not anything wrong with the substr
>>statement. I just tried it and it works just fine.
>
>
>Hm. now that's interesting I got 5 or 6 e-mails explaining me how
>Oracle handles CHAR vs VARCHAR2 and why it wasn't even supposed to
>work. Now I'm really confused...
>
>
>Simon

Simon, they are correct. I instinctively used varchar2 in my test (I almost never use char datatype unless a field is a 1 character field which is very uncommon in the applications I work on). Sorry about the confusion.

M.Landa Received on Wed Nov 13 1996 - 00:00:00 CET