Hi,
I got a problem in forming the value of type String singleoute-comma-singlequote separated in a WHERE clause.

I have used the QUOTEDSTRING attribute it is working perfectly for me below I have some
things to mention which is working properly.

SELECT * FROM TABLENAME
WHERE FieldName IN (&WFVARIABLE.QUOTEDSTRING)

Ex:
&WFVARIABLE='LIFE'S GOOD';

SELECT * FROM TABLENAME
WHERE FieldName IN ('LIFE''S GOOD')

The value is forming properly for a single text to the sql server to understand and run without any errors. My issue is I have a string with singleoute-comma-singlequote

Ex:
-SET DEFAULT &WFVARIABLE='';

string --> 'LIFE'S GOOD','DIANNE%','NEWYORKSTOCK,'

How to put the string into WHERE CLAUSE so that the it should not throw any error.

If I use QUOTEDSTRING then the string is forming like below

SELECT * FROM TABLENAME
WHERE FieldName IN (''LIFE''S GOOD'',''DIANNE%'',''NEWYORKSTOCK,'') --> This is wrong,

I want the string in the following way If we use the QUOTEDSTRING

SELECT * FROM TABLENAME
WHERE FieldName IN ('LIFE''S GOOD','DIANNE%','NEWYORKSTOCK,')


Thanks in Advance

This message has been edited. Last edited by: Kerry,

To get a quote into an & variable, you need an extra single quote pair as follows:

-SET &test = 'O''''BRIEN';

SQL
select LASTNAME from VIDEOTRK
where LASTNAME = '&test'
;
END

producing:

PAGE 1

LASTNAME
O'BRIEN


Note that you also need to still put quotes round &test in the query.

If you use FOCUS code, it is easier as described here here.

This should work:

-SET &WFVARIABLE = '''LIFE'''S GOOD'',''DIANNE%'',''NEWYORKSTOCK''';
.
.
WHERE FieldName IN (&WFVARIABLE)

But this may be easier to understand:

-SET &WFVAR1 = 'LIFE'S GOOD';
-SET &WFVAR2 = 'DIANNE%';
-SET &WFVAR3 = 'NEWYORKSTOCK';
.
.
WHERE FieldName IN (&WFVAR1.QUOTEDSTRING,&WFVAR2.QUOTEDSTRING,&WFVAR3.QUOTEDSTRING)

Here is the way I like to do quotes within a set command.


-SET &QT='''' ;
-SET &WHERE1VAL=&QT || 'BMW' || &QT | ' OR ' | &QT || 'AUDI' || &QT;


The result would be:

TABLE FILE CAR
PRINT *
WHERE CAR LIKE &WHERE1VAL;
-*WHERE CAR LIKE 'BMW' OR 'AUDI';
END