Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] Single Quote issue with in a text
Go
New
Search
Notify
Tools
Reply
  
[CLOSED] Single Quote issue with in a text
 Login/Join
 
Gold member
posted
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,


WebFOCUS 7.6.10
Windows
Output: Excel,PDF
 
Posts: 78 | Registered: January 07, 2008Reply With QuoteReport This Post
Member
posted Hide Post
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.


WF 7.6.8, Windows
Any output format: HTML, Excel, PDF, XML, etc.
 
Posts: 7 | Registered: July 14, 2009Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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)


WebFOCUS 7.7.05
 
Posts: 1213 | Location: Seattle, Washington - USA | Registered: October 22, 2007Reply With QuoteReport This Post
Gold member
posted Hide Post
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


------------------------------------------
DevStudio 8.2.03
WFS 8.2.03
 
Posts: 86 | Location: Atlanta | Registered: May 10, 2007Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] Single Quote issue with in a text

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.