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] Adding double apostrophes to an alpha string
Go
New
Search
Notify
Tools
Reply
  
[CLOSED] Adding double apostrophes to an alpha string
 Login/Join
 
Silver Member
posted
I have an html page that has a list box of employee names. You can then select one or many of those names and click submit. When the user clicks submit, we take the values from that list box, massage it a bit, and then run a sql query to bring back information for those employees. We are having a problem with employees that have an apostrophe in their name. For example, Thomas O’Toole.

Because we allow for a multiple selection of names, I have to massage the data a bit to replace the OR’s with commas so they can be used in my sql query with an IN clause.
Once that has been done, I build a character string that is put into my sql query. I’m creating dynamic WHERE clauses for the sql so I can have 1 query with variables in it for whatever filter boxes were changed when the html page ran.
So here is what my code looks like:
-SET &FULL_LEADER_NAME = STRREP(2000,&LEADER_NAME,4,' OR ',3,' , ',990,A2000);
-SET &LEADER_LINE = 'AND ' | 'rtrim(D3.CRNT_LST_NM)' | ' || ' | ''', '' ' | '|| ' | 'rtrim(D3.CRNT_FRST_NM)' || ' IN( ' | '' || &FULL_LEADER_NAME || ')';
Then in my query, I have &LEADER_LINE as a variable. Obviously, there is quite a bit more to the story, but this is just a simple explanation.

When the query runs, it’s failing because the line in the query looks like this:
AND rtrim(D3.CRNT_LST_NM) || ', ' || rtrim(D3.CRNT_FRST_NM) IN('O'Toole, Thomas')
The sql can’t run with those 3 apostrophes like that.

My thinking was I would add another STRREP command to change all apostrophes to double apostrophes. That would fix the issue and my sql will run that way. I used this:
-SET &FULL_LEADER_NAME2 = STRRE0P(2000,&FULL_LEADER_NAME,1,'''',2,'''''',990,A2000);

And that worked except now all of my apostrophes are double apostrophes. So I get this result if I were to select just Thomas O’Toole:
AND rtrim(D3.CRNT_FRST_NM) IN(''O''Toole, Thomas'’)
and this if I were to select Thomas plus one more person:
AND rtrim(D3.CRNT_FRST_NM) IN(''O''Toole, Thomas'' , ''Osby, John'')

So my STREPP works but it adds changes to the double apostrophes that surround the name, not just the ones inside of it.

Has anyone ever dealt with this issue? I’m not sure how to do any commands that will allow me to change apostrophes in the middle of the name but not the ones on the outside of the name. Nor can I figure out a way to write any commands to remove those double apostrophes in the cases that I don’t want them.

Any ideas would be great. Thanks!

This message has been edited. Last edited by: FP Mod Chuck,


WebFOCUS 8.1.04
Windows, All Outputs
 
Posts: 34 | Registered: July 02, 2014Reply With QuoteReport This Post
Expert
posted Hide Post
Try using &FULL_LEADER_NAME.QUOTEDSTRING

T



In FOCUS
since 1986
WebFOCUS Server 8.2.01M, thru 8.2.06 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10 
 
Posts: 5617 | Location: United Kingdom | Registered: April 08, 2004Reply With QuoteReport This Post
Silver Member
posted Hide Post
I tried to add the quotedstring to the following line:
-SET &FULL_LEADER_NAME2 = STRREP(2000,&FULL_LEADER_NAME.QUOTEDSTRING,1,'''',2,'''''',990,A2000);

That didn't make a difference.


WebFOCUS 8.1.04
Windows, All Outputs
 
Posts: 34 | Registered: July 02, 2014Reply With QuoteReport This Post
Platinum Member
posted Hide Post
You could try something like this:

  
-SET &LEADER_LINE = '''O''Toole, Thomas'' OR ''Osby, John'' OR ''Hunter, De''Andre''';

-TYPE Start LEADER_LINE  = &LEADER_LINE

-SET &LEADER_LINE = SUBSTRING(&LEADER_LINE, 2, &LEADER_LINE.LENGTH-2);

-SET &LEADER_LINE = STRREP(2000, &LEADER_LINE, 6, ''' OR ''', 1, '|', 2000, 'A2000');

-SET &LEADER_LINE = STRREP(2000, &LEADER_LINE, 1, '''', 2, '''''', 2000, 'A2000');

-SET &LEADER_LINE = '''' | STRREP(2000, &LEADER_LINE, 1, '|', 3, ''',''', 2000, 'A2000') || '''';

-SET &LEADER_LINE = TRUNCATE(&LEADER_LINE);

-TYPE Ending LEADER_LINE = &LEADER_LINE



WF 8.2.06
Win10 / IE11
AHTML EXL2K PDF
 
Posts: 141 | Registered: October 19, 2010Reply With QuoteReport This Post
Gold member
posted Hide Post
Hi AprilC,
Unfortunately I have also run into this issue with our branch names. We have one with an apostrophe in it as well, and it does tend to throw us issues when we have it in a drop down selector box.
The only way I was able to get around this was to have a multi-select WHERE clause which defined out all the individual branch name fields. I am assuming this won't be as easy on your end however, since you more than likely have a lot more names in your DB than I do branches.

Just for example though, this is how I dealt with the ' in my code.

Branch Name: DOWNTOWN COEUR D'ALENE

WHERE J001.BRANCH.BR_NAME EQ &BR_NAME.(, , , , , , , , , <'DOWNTOWN COEUR D''ALENE','DOWNTOWN COEUR D''ALENE'>, , , , , , , , , , , , , , , , , , , |FORMAT=A40V).Select a branch/department to run this report for:.QUOTEDSTRING;

By adding in single quotes around where the original ' should have been, it does bring back the correct name like seen above. I'm not sure if you could work a define to function the same way,
IF EMP.NAME EQ 'Thomas O'Toole' THEN 'Thomas O' 'Toole' ELSE EMP.NAME

Good luck!


Version: 8.2.03M, OS/Platform: Windows 7 & 10, Output: Excel, pdf, html
 
Posts: 63 | Location: Liberty Lake, WA - USA | Registered: June 23, 2016Reply With QuoteReport This Post
Silver Member
posted Hide Post
Dbeagan, Thanks for the idea but it gives me a lot more apostrophies but they aren’t in a consistent manner where I could manipulate them. For example, with Thomas O’Toole, I get this end result:
AND rtrim(D3.CRNT_LST_NM) || ', ' || rtrim(D3.CRNT_FRST_NM) IN('''O''''Toole, Thomas''''')

And when I add a second name, I get this end result:
AND rtrim(D3.CRNT_LST_NM) || ', ' || rtrim(D3.CRNT_FRST_NM) IN('''Osby, John'''' , ''''O''''Toole, Thomas''''')


LarissaB, Thanks for the idea but I don’t have just this one person with an apostrophe. I do have others. And we are really trying to not hardcode any names. But it’s definitely something that I will keep in the back of my mind. If no one on here has any ideas that end up working for me, I will have to explore this idea.


WebFOCUS 8.1.04
Windows, All Outputs
 
Posts: 34 | Registered: July 02, 2014Reply With QuoteReport This Post
Expert
posted Hide Post
I believe Tony's suggestion was to add QUOTEDSTRING to the second mention of &FULL_LEADER_NAME:

-SET &FULL_LEADER_NAME = STRREP(2000,&LEADER_NAME,4,' OR ',3,' , ',990,A2000);
-SET &LEADER_LINE = 'AND ' | 'rtrim(D3.CRNT_LST_NM)' | ' || ' | ''', '' ' | '|| ' | 'rtrim(D3.CRNT_FRST_NM)' || ' IN( ' | '' || &FULL_LEADER_NAME.QUOTEDSTRING || ')';


The quotes would might then be passed properly to the SQL passthru query.

This message has been edited. Last edited by: Francis Mariani,


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Reply With QuoteReport This Post
Expert
posted Hide Post
You may simplify &LEADER_LINE:

-SET &LEADER_LINE = 'AND ' | 'rtrim(D3.CRNT_LST_NM) || '', '' || rtrim(D3.CRNT_FRST_NM) IN( ' | '' || &FULL_LEADER_NAME.QUOTEDSTRING || ')';


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Reply With QuoteReport This Post
Silver Member
posted Hide Post
I've added .QUOTEDLINE and it does not seem to make a difference. Once I did it I was getting the same results so I added a TYPE statement to see what it looks like. And whether I use the .QUOTEDSTRING or not, this is what my &LEADER_LINE looks like:
leader_line after set = AND rtrim(D3.CRNT_LST_NM) || ', ' || rtrim(D3.CRNT_FRST_NM) IN('O'Toole, Thomas')


WebFOCUS 8.1.04
Windows, All Outputs
 
Posts: 34 | Registered: July 02, 2014Reply With QuoteReport This Post
Platinum Member
posted Hide Post
If you run exactly the code I provided as a standalone procedure, does it produce the kind of result you are looking for?


WF 8.2.06
Win10 / IE11
AHTML EXL2K PDF
 
Posts: 141 | Registered: October 19, 2010Reply With QuoteReport This Post
Silver Member
posted Hide Post
dbeagan, I worked more on your idea and it's closer than I had originally thought. It seems to be good if we were to select one person at a time. So just Thomas O'Toole works but when I add in another person, it doesn't. I tried to run this in a standalone but when I do that, when I try to hardcode in the values coming in like they do from the html page, they just don't always work the same. So I had to just build this idea out in my real fex. When I run just Thomas O'Toole with your code, it all looks like this:
);
-TYPE Start LEADER_LINE_INIT = 'O'Toole, Thomas'
Start LEADER_LINE_INIT = 'O'Toole, Thomas'
-SET &LEADER_LINE_INIT = SUBSTRING('O'Toole, Thomas', 2, 17-2);
-SET &LEADER_LINE_INIT = STRREP(2000, O'Toole, Thomas, 6, ''' OR ''', 1, '|', 2000, 'A2000');
, 1, '''', 2, '''''', 2000, 'A2000');
, 1, '|', 3, ''',''', 2000, 'A2000') || '''';
);
-TYPE Ending LEADER_LINE_INIT = 'O''Toole, Thomas'
Ending LEADER_LINE_INIT = 'O''Toole, Thomas'
-SET &LEADER_LINE = 'AND ' | 'rtrim(D3.CRNT_LST_NM)' | ' || ' | ''', '' ' | '|| ' | 'rtrim(D3.CRNT_FRST_NM)' || ' IN( ' | '' || 'O''Toole, Thomas' || ')';
-TYPE leader_line after set = AND rtrim(D3.CRNT_LST_NM) || ', ' || rtrim(D3.CRNT_FRST_NM) IN('O''Toole, Thomas')
leader_line after set = AND rtrim(D3.CRNT_LST_NM) || ', ' || rtrim(D3.CRNT_FRST_NM) IN('O''Toole, Thomas')
-SET &WHERECLAUSE1 = AND rtrim(D3.CRNT_LST_NM) || ', ' || rtrim(D3.CRNT_FRST_NM) IN('O''Toole, Thomas') ;

Once it’s put into the query, looks like:
AND rtrim(D3.CRNT_LST_NM) || ', ' || rtrim(D3.CRNT_FRST_NM) IN('O''Toole, Thomas')

But once I add in another leader, that's when the apostrophes aren't working like I want. For example, here's Thomas plus John Osby:
);
-TYPE Start LEADER_LINE_INIT = 'O'Toole, Thomas' , 'Osby, John '
Start LEADER_LINE_INIT = 'O'Toole, Thomas' , 'Osby, John '
-SET &LEADER_LINE_INIT = SUBSTRING('O'Toole, Thomas' , 'Osby, John ', 2, 34-2);
-SET &LEADER_LINE_INIT = STRREP(2000, O'Toole, Thomas' , 'Osby, John, 6, ''' OR ''', 1, '|', 2000, 'A2000');
, 1, '''', 2, '''''', 2000, 'A2000');
, 1, '|', 3, ''',''', 2000, 'A2000') || '''';
);
-TYPE Ending LEADER_LINE_INIT = 'O''Toole, Thomas'' , ''Osby, John '
Ending LEADER_LINE_INIT = 'O''Toole, Thomas'' , ''Osby, John '
-SET &LEADER_LINE = 'AND ' | 'rtrim(D3.CRNT_LST_NM)' | ' || ' | ''', '' ' | '|| ' | 'rtrim(D3.CRNT_FRST_NM)' || ' IN( ' | '' || 'O''Toole, Thomas'' , ''Osby, John ' || ')';
-TYPE leader_line after set = AND rtrim(D3.CRNT_LST_NM) || ', ' || rtrim(D3.CRNT_FRST_NM) IN('O''Toole, Thomas'' , ''Osby, John ')
leader_line after set = AND rtrim(D3.CRNT_LST_NM) || ', ' || rtrim(D3.CRNT_FRST_NM) IN('O''Toole, Thomas'' , ''Osby, John ')
-SET &WHERECLAUSE1 = AND rtrim(D3.CRNT_LST_NM) || ', ' || rtrim(D3.CRNT_FRST_NM) IN('O''Toole, Thomas'' , ''Osby, John ') ;

Once it’s in the query, it looks like:
AND rtrim(D3.CRNT_LST_NM) || ', ' || rtrim(D3.CRNT_FRST_NM) IN('O''Toole, Thomas'' , ''Osby, John ')


WebFOCUS 8.1.04
Windows, All Outputs
 
Posts: 34 | Registered: July 02, 2014Reply With QuoteReport This Post
Member
posted Hide Post
To come at this from a different angle could you replace all the apostrophes in you employee or input file with Acute Accents ( [Alt]180 )? They are not the same character but will your users notice? You also could do the reverse replace on your output file. You may have to check the high order characters in your output font to find the right character to use.
 
Posts: 1 | Registered: May 01, 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] Adding double apostrophes to an alpha string

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