Stored procedure/host variable/substring

Perry O'Connell

Stored procedure/host variable/substring
Does anyone know why this doesn't work?

its in a SQL Stored Procedure , we run DB2 V7 Zos 1.4



e.g.



create procedure sysproc.foo (in partkey char(5))

-- - - - - - -

gubbins


- - - - - - -- -- - -


Select foobar1 from foobar
where

foobar1 like concat ( substr(partkey,1,5), '%' )

--more gubbins ---



DSNT408I SQLCODE = -132, ERROR: AN OPERAND OF LIKE IS NOT VALID


but if i remove substr , i.e.

foobar1 like concat (partkey, '%' )

its OK



---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Ava Collins

Re: Stored procedure/host variable/substring
(in response to Perry O'Connell)





Have you tried adding additional parenthesis?:

foobar1 like concat ((substr(partkey,1,5)), '%' )

Jacquie

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Agus Kwee

Re: Stored procedure/host variable/substring
(in response to Ava Collins)
Perry,

According to the SQL Reference Manual about LIKE predicate,
you cannot use nested function in the predicate:
LIKE concat(substr(partkey,1,5),'%')
try to change it to:
LIKE substr(partkey,1,5) concat '%'
It worked for me when I tested it using SPUFI.

Regards,
Agus Kwee
Themis Training
http://www.themisinc.com



----- Original Message -----
From: Perry O'Connell
Date: Friday, November 24, 2006 10:06 am
Subject: [DB2-L] Stored procedure/host variable/substring
To: [login to unmask email]

> Does anyone know why this doesn't work?
>
> its in a SQL Stored Procedure , we run DB2 V7 Zos 1.4
>
>
>
> e.g.
>
>
>
> create procedure sysproc.foo (in partkey char(5))
>
> -- - - - - - -
>
> gubbins
>
>
> - - - - - - -- -- - -
>
>
> Select foobar1 from foobar
> where
>
> foobar1 like concat ( substr(partkey,1,5), '%' )
>
> --more gubbins ---
>
>
>
> DSNT408I SQLCODE = -132, ERROR: AN OPERAND OF LIKE IS NOT VALID
>
>
> but if i remove substr , i.e.
>
> foobar1 like concat (partkey, '%' )
>
> its OK
>
>
>
> -----------------------------------------------------------------
> ----------------
> Welcome to the IDUG DB2-L list. To unsubscribe, go to the
> archives and home page at http://www.idugdb2-l.org/archives/db2-
> l.html. From that page select "Join or Leave the list". The IDUG
> DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins
> can be reached at [login to unmask email] Find out the
> latest on IDUG conferences at http://conferences.idug.org/index.cfm
>

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Perry O'Connell

Re: Stored procedure/host variable/substring
(in response to Agus Kwee)
Thanks for you help Jackie and Agus

I did spot the reference in the SQL manual to the LIKE predicate,
in that you cannot use nested function in the predicate, but the original
SQL works ok when run on a Windows DB2 environment.

Agus, your solution works fine, but Jackie I cant get yours to work, same
problem :(

Is it unusal that SQL written in a Windows implementation of DB2 will not
run on Z/Os?

Perry










[login to unmask email]
Sent by: DB2 Data Base Discussion List <[login to unmask email]>
24/11/2006 15:25
Please respond to
DB2 Database Discussion list at IDUG <[login to unmask email]>


To
[login to unmask email]
cc

Subject
Re: [DB2-L] Stored procedure/host variable/substring








Have you tried adding additional parenthesis?:

foobar1 like concat ((substr(partkey,1,5)), '%' )
Jacquie
---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page
select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences
at http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm