Sql Replace function problem

["Chris Payne" <CPayne@xxxxxxxxxxxxxxx>]

Hello all,

I am having an SQL problem on the ISeries. I have a 3rd party tool that
submits SQL queries via ODBC. In the SQL query there is a facility for
entering variables, and then the user is prompted for values to replace
said variables.  There is a bug involving quotes inside of quotes that
is making it impossible to use the "IN" operator with character strings.
I was thinking that I would have the user pass in their values without
quotes, and add quotes after the fact. I am picturing the user entering
123AB,678AC and my script turning it into '123AB','678AC'.

 

I decided to use the replace function to change [,] into [','] and then
concat a quote to the start and end.

 

SELECT 

  (concat(concat('''',replace('1022228,1022229' , ',' , ''',''')),''''))


FROM 

  sysibm.sysdummy1 

 

returns:  '1022228','1022229'

 

Which is exactly what I want. The [,] was replaced with a [','] and a
quote was added to the beginning and end. My function written, I tried
to use it with the "IN" operator to select records.

 

When I run this query:

 

select 

  * 

from 

  osld1f3.inp35 

where 

  pnum35 in ('1022228','1022229') 

 

it returns 2 records, so I should be able to replace '1022228','1022229'
with my function, because the function resolves to the EXACT same thing.

 

But when I run this query:

 

select 

  * 

from 

  osld1f3.inp35 

where 

  pnum35 in (concat(concat('''',replace('1022228,1022229',',',
''',''')),''''))     

 

It returns no records, which I am finding very hard to understand. Sorry
for the long question, but I could not think of a shorter way to ask it.

 

 

 

Christopher M. Payne

CPayne@xxxxxxxxxxxxxxx

System Administrator/Programmer

 

The Crown Group

Corporate Offices

2111 Walter Reuther Drive

Warren, MI 48091-6199

 

Phone:  (586) 575-9800

Direct:   (586) 558-5317

Fax:      (586) 575-9856