Prev: CURSOR WITH RETURN AND UPDATE?
Next: How to change Enterprise server edition to Workgroup server edition ?
From: Michael B on 30 Sep 2009 05:54
Le Tue, 29 Sep 2009 21:21:53 -0700 (PDT), Lennart a �crit :

> On 29 Sep, 22:40, Michael B <m74649...(a)spambob.com> wrote:
>> Le Tue, 29 Sep 2009 08:04:53 -0700 (PDT), Lennart a �crit :
>>
>>
>>
>>> On Sep 29, 4:27�pm, Michael B <m74649...(a)spambob.com> wrote:
>>>> Hello,
>>
>>>> In a previous thread, I asked you why my code in a prepared statement
>>>> didn't work.
>>
>>>> I've again several bugs. Here's an example with the table :
>>
>>>> db2inst1(a)dev01:~$ db2 "describe select * from rsskeyword" � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � �
>>>> �Column Information
>>>> �Number of columns: 2
>>>> �SQL type � � Type length �Column name � �Name length
>>>> �496 � INTEGER � � � � �4 �FEED_ID � � � �7
>>>> �448 � VARCHAR � � � �255 �KEYWORD � � � �7
>>
>>>> According what you've said (Lennart and Serge) my SQL simplified Prepared
>>>> statement is:
>>>> select feed_id, position(cast(cast(? as char(100)) as varchar(100)) in
>>>> keyword using OCTETS) as position from rsskeyword
>>
>>> Why are you casting ? as char(100)? Is there some reason that you are
>>> not using one cast alone, as in:
>>
>>> select feed_id, position( cast(? as varchar(100)) in keyword using
>>> OCTETS) as position from rsskeyword
>>
>>> /Lennart
>>
>>> [...]
>>
>> Because of a "memory" of an other class I've in the code where an integer
>> had to be compare with a string. (aid = integer). I don't know other
>> solution than make a double cast to turn the integer into varchar:
>>
>> db2 "select cast(aid as varchar(10)) from answers" � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � �
>> SQL0461N �A value with data type "SYSIBM.INTEGER" cannot be CAST to type
>> "SYSIBM.VARCHAR". �SQLSTATE=42846
>>
>> db2 "select cast(cast(aid as char (10)) as varchar(10)) from answers" � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � �
>>
>> 1 � � � �
>> ----------
>> 1 � � � �
>>
>> � 1 record(s) selected.
>
> Ok, I think I understand. I think the problem is that the cast to char
> (100) results in a 100 char long string padded with spaces. Does it
> help to trim it as in:
>
> select feed_id, position(cast(trim(cast(? as char(100))) as varchar
> (100)) in
> keyword using OCTETS) as position from rsskeyword
>
> db2 "with rsskeyword (feed_id, keyword) as (values (3, 'Whatabout this
> 123456789')) select feed_id, position(cast(trim(cast(1 as char(100)))
> as varchar(100)) in keyword using OCTETS) as position from rsskeyword
> "
>
> FEED_ID POSITION
> ----------- -----------
> 3 16
>
> 1 record(s) selected.
>
> db2 "with rsskeyword (feed_id, keyword) as (values (3, 'Whatabout this
> 123456789')) select feed_id, position(cast(trim(cast('' as char(100)))
> as varchar(100)) in keyword using OCTETS) as position from rsskeyword
> "
>
> FEED_ID POSITION
> ----------- -----------
> 3 1
>
> 1 record(s) selected.
>
>
> However, I don't really like the idea of sql with generic types as
> arguments. Say that 1 out of a 1000 arguments is an int, and the rest
> is chars. That means a lot of unnecessary functions calls ( since the
> day I found a query that did T1 join T2 on ltrim(T1.x) = ltrim(T2.x)
> I'm a bit sceptic about these :-). One possibility to avoid this is to
> write an sql function for each type:
>
> create function my_position ( p int , ...) ...
> create function my_position ( p char(100), ...) ...
>
> DB2 will resolve what function to use via the type of the parameter.
> Just a thought
>
> /Lennart

Hi,

OK, I understand now!
Thank you.

Michael
First  |  Prev  | 
Pages: 1 2
Prev: CURSOR WITH RETURN AND UPDATE?
Next: How to change Enterprise server edition to Workgroup server edition ?