Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Concatenation of strings in SQL Stored Procedure

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
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    Post subject: Concatenation of strings in SQL Stored Procedure
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    Post subject:
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    Post subject:
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    Post subject:
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    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Occurrence of various strings within ... TS70363 JCL & VSAM 1 Thu Apr 27, 2017 8:18 pm
No new posts Strings with double quotes having pro... raja Arumugam All Other Mainframe Topics 11 Thu Mar 30, 2017 10:34 am
No new posts Query on DB2 WLM stored procedures ashek15 DB2 8 Sun Feb 12, 2017 11:13 pm
No new posts Calling procedure with multiple entri... steve-myers PL/I & Assembler 5 Fri Jan 27, 2017 3:33 pm
No new posts Is the 'prompt' data for the DSLIST p... Willy Jensen TSO/ISPF 2 Tue Dec 06, 2016 4:38 am


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us