By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,756 Members | 1,745 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,756 IT Pros & Developers. It's quick & easy.

function usage in dynamic SQL

P: n/a
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!

Nov 12 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
li******@yahoo.ca wrote:
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!

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
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Nov 12 '05 #2

P: n/a
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.

Nov 12 '05 #3

P: n/a
li******@yahoo.ca wrote:
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

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.