Select Range Query

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

anilcool@gmail.com - 10 Feb 2006 00:53 GMT

Hi all,

This is probably a simple problem for most of you.. Let me know if you
have any pointers for me. I am new to DB2.

In my stored procedure I want to select records that match a range of
values.

=========================================================

create PROCEDURE DB2. sp_gettotry_logs (
IN myid
CHARACTER(36)
)
RESULT SETS 1
LANGUAGE SQL
BEGIN
DECLARE c1 CURSOR WITH RETURN FOR
select * from db2.playlogs_ where playerid_ in
( myid ) ;

OPEN c1;
END

===========================================================

How do I call this procedure and pass a range of values ?

I've tried:
call db2.sp_gettotry_logs( '19','01')

This doesn't work because the function is expecting only one parameter
... but I want to get record results where playerid is either '19' or
'01'.

Thanks in advance.

-Anil

Reply to this Message

Serge Rielau - 10 Feb 2006 01:06 GMT

> Hi all,
>
[quoted text clipped - 34 lines]
>
> -Anil

CREATE PROCEDURE DB2. sp_gettotry_logs (
IN myid CHARACTER(36)
)
RESULT SETS 1
LANGUAGE SQL
BEGIN
DECLARE txt VARCHAR(1000);
DECLARE stmt STATEMENT;
DECLARE c1 CURSOR WITH RETURN FOR stmt;
SET txt = 'select * from db2.playlogs_ where playerid_ in ('
|| myid || ')';
PREPARE stmt FROM txt;
OPEN c1;
END

CALL DB2.sp_gettotry_logs('19,01');

I assume that playerid_ is a numeric, if it's a string you need to
escape the quotes when passing a constant:
CALL DB2.sp_gettotry_logs('''19'',''01''');

Cheers
Serge

Signature

Serge Rielau
DB2 Solutions Development
DB2 UDB for Linux, Unix, Windows
IBM Toronto Lab

Reply to this Message

Tonkuma - 10 Feb 2006 01:20 GMT

> CREATE PROCEDURE DB2. sp_gettotry_logs (
> IN myid CHARACTER(36)
> )

(SNIP)

> CALL DB2.sp_gettotry_logs('19,01');

Because, data type of string constant('19,01') is VARCHAR,
Perhaps, you need to change data type of argument like this:

CALL DB2.sp_gettotry_logs(CHAR('19,01'))

So, it's better to define input parameter as VARCHAR.

CREATE PROCEDURE DB2. sp_gettotry_logs (
IN myid VARCHAR(36)

Reply to this Message

anilcool@gmail.com - 10 Feb 2006 01:23 GMT

Worked.. thanks :)

-Anil

Reply to this Message