IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Concatenation of strings in SQL Stored Procedure


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
gvshruthi

New User


Joined: 05 Jan 2009
Posts: 4
Location: bangalore

PostPosted: Fri Nov 25, 2011 12:13 pm
Reply with quote

Hi All,

I am creating an SQL stored procedure. I am stuck with the concatenation of strings and variables used in the stored procedure.
The concatenation is as follows:

SET SQL_QUERY =
'SELECT ' || <var1> || ' AS VAR1,' || ''' || <var2> || '''' ||| ' AS VAR2'
|| ' FROM ' || <var3> || ' WHERE ' || <var4> || ';'

var1, var 2, var 3 and var 4 are variables that contain values fetched from other tables in the stored procedure.

I need the result in the SQL_QUERY variable as follows:

SELECT 1 AS VAR1,'server' AS VAR2 FROM table WHERE condition;

But I get the result as follows:

SELECT 1 AS VAR1,' || <var2> || ' AS VAR2 FROM table WHERE condition;

Can anyone let me know how would I have to concatenate apostrophes (') in the final string.
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Fri Nov 25, 2011 2:02 pm
Reply with quote

It's pretty simple:
a string starts with a single quote '
and ends at the next single quote UNLESS
there are two single quotes.
In that case the string continues (the two single quotes become one single quote within the string) .

This is yours :
SET SQL_QUERY = 'SELECT ' || <var1> || ' AS VAR1,' || ''' || <var2> || ''' || ' AS VAR2' || ' FROM ' || <var3> || ' WHERE ' || <var4> || ';'

What you want is
SELECT ' || <var1> || ' AS VAR1,''' || <var2> || ''' AS VAR2' || ' FROM ' || <var3> || ' WHERE ' || <var4> || ';'
Back to top
View user's profile Send private message
gvshruthi

New User


Joined: 05 Jan 2009
Posts: 4
Location: bangalore

PostPosted: Fri Nov 25, 2011 6:38 pm
Reply with quote

Thanks a lot GuyC.

I wanted the result as:

SELECT 1 AS VAR1,'server' AS VAR2 FROM table WHERE condition;

To string one apostrophe, we need to concatenate it the following way:

''''|| <var2> || '''' || ' AS VAR2

This is how it worked for me.
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Fri Nov 25, 2011 7:14 pm
Reply with quote

|| '''' || ' AS VAR2 ' is equivalent to || ''' AS VAR2 '
like
|| 'A' || 'B' is equivalent to || 'AB'
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Invoke stored procedure via batch JCL. DB2 2
No new posts Calling COBOL DB2 program from a COBO... COBOL Programming 2
No new posts Cond parameter and Rc code of a proce... JCL & VSAM 5
This topic is locked: you cannot edit posts or make replies. Internal Autonomous Stored Procedure ... DB2 6
This topic is locked: you cannot edit posts or make replies. How to search multiple strings in a PDS IBM Tools 3
Search our Forums:

Back to Top