function usage in dynamic SQL

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

lizayica@yahoo.ca - 29 Aug 2005 14:25 GMT

I am using dynamic SQL in my Procedure that has one parameter named
Dep_ID varchar(50).
Inside procedure, I need one dynamic SQL to fulfill the same function
like:
select first_name || ','||last_name from employee where dep_id in
(Dep_id)

How to express || in dynamic sql? Or can I use concat function and how
to use it in dynamic sql ? I got error by using CONCAT(p1,p2).

Thanks a million for any help!

Reply to this Message

Serge Rielau - 29 Aug 2005 14:40 GMT

> I am using dynamic SQL in my Procedure that has one parameter named
> Dep_ID varchar(50).
[quoted text clipped - 7 lines]
>
> Thanks a million for any help!

I go out on a lib here and supect that you rproblem is really teh ','....
Try this:

-- note the 2 single quotes to escape '
SET stmttxt = 'SELECT first_name || '','' || last_name from employee
where dep_id in (' || Dep_id -- I assume this is a local variable?
|| ')';
PREPARE stmt FROM stmttxt;

Cheers
Serge

Signature

Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

Reply to this Message

lizayica@yahoo.ca - 29 Aug 2005 20:51 GMT

But, I'd like to put firstname and last name with ',' in one column.

that is:

select first_name || ',' || last_name as full_name from employee where
dep_id in
(Dep_id)

Sorry for not clear.

Reply to this Message

Serge Rielau - 30 Aug 2005 03:10 GMT

> But, I'd like to put firstname and last name with ',' in one column.

Yes, you want: Names like: 'Rielau, Serge' as a result. Check my
proposal and you should be pleasantly surprised ....

Cheers
Serge

Signature

Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

Reply to this Message