Posted: Fri Aug 11, 2006 1:11 pm Post subject: Passing data with apostrophe
Hi,
Just wondering if anyone could help me out with this...
I have an SQL executing as part of the PROC and SQL accepts parameters from the JCL. These parameters are actually state codes
Ex:
and in the proc, I have the following SQL as SYSIN data
SYSIN DD *
SELECT * FROM XAMSTS
WHERE
STATE_CD IN
('&STCD1', '&STCD2','&STCD3','&ST4','&STCD5')
Now, I need to update the above JCL, so that I can pass pass more state codes in a single parameter.
Basically I should be able to pass the following value using a single parameter (STCD1) to the SQL.
''CA','FL','IA','GA','OR','OK''
But I am not getting a clue how to include the apostrophes while passing value as a parameter. Could any one help?
Thanks
JA
kolusu,
Sorry, I wont be able to use that solution, because Operator may not like that complicated coding. I found another work around as follows.
STCD1='PA AL AR AK GA AZ AB AC' as a single string and in the actual sql,
I used substring function as follows
STATE_CD = SUBSTR(''&STCD1'',1,2)
STATE_CD = SUBSTR(''&STCD1'',4,2)
STATE_CD = SUBSTR(''&STCD1'',7,2)
STATE_CD = SUBSTR(''&STCD1'',10,2)
Etc..
Looks like the maximum I can pass is 8 state codes as it exceeds the maximum length.
SELECT *
FROM TABLE
WHERE COLUMN IN ('start CONSTANT',
PARM2 will have the actual list of parms you want to pass
Code:
'CA',
'IL',
'FL',
'MN',
'AZ',
'NY',
...
Parm3 will have the closing parenthisis
Code:
'end constant');
when concatenated the 3 parms will be 1 sql and will be treated
Code:
SELECT *
FROM TABLE
WHERE COLUMN IN ('start CONSTANT',
'CA',
'IL',
'FL',
'MN',
'AZ',
'NY',
'end constant');
Start constant and end constant can be a value which does not exist in the table. For sql you can pass a total of 32760 bytes in total . So you each line in the sysin is 80 bytes, so you can have a total of 400 lines.
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
