Re: simple things
Date: 1996/11/11
Message-ID: <328532c4.1202378_at_dcsun4>#1/1
On Sat, 09 Nov 1996 09:59:51 GMT, sdv_at_xs4all.nl (Simon Verzijl) wrote:
>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.
>
Because you used the char datatype in the create table.
A char(20) will always be 20 bytes long. the name 'simon' is really
'simon '.
When you compare to a constant, the constant is promoted to the underlying database type (since the constant is untyped). Therefore:
where name = 'simon'
is executed as:
where name = 'simon '
When you used the substr function, you used a typed comparision and compared a CHAR to a VARCHAR. VARCHARS don't have trailing blanks on them. So your comparision was:
where 'simon ' = 'simon'
which evaluated to false. You could either:
- where name = rpad( substr( 'simonfdafdafdas', 1, 5 ), 20 )
or
- where rtrim(name) = substr( 'simonfdafdadfa', 1, 5 ) /* precludes an index on name tho */
- use the varchar or varchar2 datatype in the create table instead of char.
Using char will lead to all types of problems when you goto search tho.... Would suggest varchar2.
>
>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
>
Thomas Kyte
Oracle Government
tkyte_at_us.oracle.com
- Check out Oracle Governments web site! -----
Follow the link to "Tech Center"
and then downloadable Utilities for some free software...
statements and opinions are mine and do not necessarily reflect the opinions of Oracle Corporation Received on Mon Nov 11 1996 - 00:00:00 CET