Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] Using an embedded ampersand in a string

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[CLOSED] Using an embedded ampersand in a string
 Login/Join
 
Platinum Member
posted
Hi All,

I have an amper variable, &A, that is passed from a calling fex and it's value is "**** & Jane" with an embedded ampersand.

I want to create a dynamic WHERE statement that looks like this: -SET &W = WHERE LOOKUP EQ '&A'; and I cannot get the code to look past the ampersand embedded in the variable &A. My results for &W end up looking like this: WHERE LOOKUP EQ '**** ';.

I have tried all kinds of things and I haven't hit the correct syntax. Can anyone help?

Thanks,

Norb

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


prod:7.6.9, win2k3 mre, caster, bid, devstudio 7.6.9
 
Posts: 242 | Location: Minneapolis | Registered: February 16, 2006Report This Post
Expert
posted Hide Post
Norb, try putting a "|" pipe between the & and the rest of the variable.

-SET &W = WHERE LOOKUP EQ '&|A';


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6347 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
Platinum Member
posted Hide Post
Nope that's not working. Perhaps I can be more clear about what I'm doing.

I basically have a WHERE statement that looks like this:

WHERE LOOKUP EQ '&LOOKUP';

and I pass the &LOOKUP value from a calling fex. When the value of &LOOKUP does not contain an ampersand the query works great. So if the value of &LOOKUP is equal to "Norb" everything works and the WHERE statement expands to

WHERE LOOKUP EQ 'Norb';

If value of &LOOKUP is equal to 'Norb & Chubby" then the expanded statement gets truncated and ends up looking like this

WHERE LOOKUP EQ 'Norb '

which in turn is an invalid value and the query fails. How can I prevent the truncation when a string contains an ampersand?

Thanks,

Norb


prod:7.6.9, win2k3 mre, caster, bid, devstudio 7.6.9
 
Posts: 242 | Location: Minneapolis | Registered: February 16, 2006Report This Post
Expert
posted Hide Post
Norb,

The option I would choose is to convert special characters in the passing fex to the calling fex, then, reversing them:

-* Translate Special Characters in PARMS so Multiple Drilldown(s) Work
-*
-* CHANGE BLANK(32) TO UNDERSCORE(95)
-* CHANGE QUOTE(39) TO ASTERISK(42)
-* CHANGE AMPER(38) TO EXCLAMATION(33)
-*
-* Convert Parms before passing
-*
-SET &LOOKUP = 'Norb & Chubby';
-SET &LEN    = &LOOKUP.LENGTH ;
-SET &FMT    = 'A' | &LEN.EVAL ;
-SET &C_CNV1 = CTRAN(&LEN, &LOOKUP, 32, 95, '&FMT');
-SET &C_CNV2 = CTRAN(&LEN, &C_CNV1, 39, 42, '&FMT');
-SET &C_CNV3 = CTRAN(&LEN, &C_CNV2, 38, 33, '&FMT');
-SET &CONV_1 = IF &LOOKUP EQ 'FOC_NONE' THEN &LOOKUP ELSE &C_ORG3;
-TYPE Lookup = &CONV_1

Pass &CONV_1 the drill fex and reverse:

-SET &LEN    = &CONV_1.LENGTH ;
-SET &FMT    = 'A' | &LEN.EVAL ;
-SET &C_LKP1 = CTRAN(&LEN, &CONV_1, 95, 32, '&FMT');
-SET &C_LKP2 = CTRAN(&LEN, &C_LKP1, 42, 39, '&FMT');
-SET &C_LKP3 = CTRAN(&LEN, &C_LKP2, 33, 38, '&FMT');
-SET &CONV_2 = IF &CONV_1 EQ 'FOC_NONE' THEN &LOOKUP ELSE &C_LKP3;
-TYPE Lookup #2 = '&CONV_2'



Hope this helps...

Tom


Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe
 
Posts: 1972 | Location: Centennial, CO | Registered: January 31, 2006Report This Post
Platinum Member
posted Hide Post
Norb,

Does it work if you use QUOTEDSTRING?:

-SET &LOOKUP_Q = &LOOKUP.QUOTEDSTRING;

TABLE FILE YOURTABLE
PRINT *
WHERE LOOKUP EQ '&LOOKUP_Q'
END

Sean


------------------------------------------------------------------------
PROD: WebFOCUS 7.6.2 on Unix AIX/Tomcat/Servlet Mode
TEST: WebFOCUS 7.6.2 on Unix AIX/Tomcat/Servlet Mode
 
Posts: 210 | Location: Ottawa | Registered: November 03, 2005Report This Post
Guru
posted Hide Post
Norb,

Have you tried this:

-SET &A = '**** & Jane';
-SET &W = 'WHERE LOOKUP EQ ''&A.EVAL''';


Glenda

In FOCUS Since 1990
Production 8.2 Windows
 
Posts: 301 | Location: Galveston, Texas | Registered: July 07, 2004Report This Post
Virtuoso
posted Hide Post
I would tend to use:
-SET &W ='WHERE LOOKUP EQ ''&A''';
.
.
&W.EVAL
.

but I think Glenda's solution is the same really and will work.


Alan.
WF 7.705/8.007
 
Posts: 1451 | Location: Portugal | Registered: February 07, 2007Report This Post
Expert
posted Hide Post
Yep, I made it more complex than it needed to be for this scenario...

Glenda and Alan examples work best.

Tom


Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe
 
Posts: 1972 | Location: Centennial, CO | Registered: January 31, 2006Report This Post
Expert
posted Hide Post
has anyone noticed that glenda's post got edited by the very funny 'bad words you cant say on tv' forum editor???




In Focus since 1979///7706m/5 ;wintel 2008/64;OAM security; Oracle db, ///MRE/BID
 
Posts: 3811 | Location: Manhattan | Registered: October 28, 2003Report This Post
Virtuoso
posted Hide Post
A follow-up to this --

This works for "A & P" but not when the incoming amper contains "A&P" . . . the lack of a space after the ampersand makes quite a difference. Has anyone managed to get it to work with an ampersand followed immediately by another character?

J.



 
Posts: 1012 | Location: At the Mast | Registered: May 17, 2007Report This Post
Expert
posted Hide Post
escape character
&|
Use a STRREP command to edit the incoming character string
stripping out the & and replacing with a &|




In Focus since 1979///7706m/5 ;wintel 2008/64;OAM security; Oracle db, ///MRE/BID
 
Posts: 3811 | Location: Manhattan | Registered: October 28, 2003Report This Post
Virtuoso
posted Hide Post
. . . and I need to recorrect on the far side? I'm curious what will happen in my -SET commands when I'm playing with ampersands.

Thanks for the quick reply.

J.



 
Posts: 1012 | Location: At the Mast | Registered: May 17, 2007Report This Post
Virtuoso
posted Hide Post
John

The method I used above:
-SET &W ='WHERE LOOKUP EQ ''&A''';
.
.
&W.EVAL
.

works for me with 'A & P' as well as 'A&P'.


Alan.
WF 7.705/8.007
 
Posts: 1451 | Location: Portugal | Registered: February 07, 2007Report This Post
Virtuoso
posted Hide Post
That failed for me yesterday, but I will try again this morning when my brain is a little sharper. I may have missed something.

I'm making the call from Maintain and when I do that it appears to fail. When I just do a -SET to test the unit it runs correctly. That leads me to believe that somehow Maintain is booting the send, but that's kind of perplexing.

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



 
Posts: 1012 | Location: At the Mast | Registered: May 17, 2007Report This Post
Expert
posted Hide Post
John, what do need to do with the incoming value containing an ampersand?

This URL passes a variable with A&P to program fmtest77:

http://ocdt70195939.office.adr...test77&COMPANY=A%26P

This fex has no problem with the value:

TABLE FILE CAR
PRINT *
WHERE COUNTRY NE '&COMPANY'
END
Do you need to manipulate the variable for some other reason?


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, 2005Report This Post
Virtuoso
posted Hide Post
I just need to drop it into a simple WHERE clause. It works for a simple call or for defaults that I set in the focexec.

But, for a call from Maintain, it's not working and it's just not telling me why it isn't. If I attempt to pass the amper variable back to Maintain I get an empty field. This has turned into one great big hole that I'm pouring hours into, and my project simply cannot afford so much time on such a picayune little problem.

Given my short list of incoming text strings and the almost assured need for each of them to be human readable (they're all company names) I've made the following compromise, since anything with an amper followed by a character is imploding:

-- Convert all & to % prior to sending, and then use the result in a LIKE clause instead off an EQ clause.

What results is
WHERE PAYEE_NAME LIKE 'A%P SHOPPING';

This works, but in theory could return records that shouldn't apply. I think I'm safe, but Jeeze-Louise trying to get an amper to just act like any other character should NOT be this hard. They're frikkin' everywhere.

I'm concerned it has something to do with the GET or POST command sent via Maintain to the focexec. That is absolutely positively beyond my control.

S.



 
Posts: 1012 | Location: At the Mast | Registered: May 17, 2007Report This Post
Expert
posted Hide Post
John, I don't know if this will help at all for the Maintain issue - as suggested by susannah...

-SET &CTY_LENGTH = &COMPANY.LENGTH + 1;
-SET &CTY1 = STRREP (&COMPANY.LENGTH, &COMPANY, 1, '&', '2', '&||', &CTY_LENGTH, 'A&CTY_LENGTH.EVAL');
That || translates into |, so A&P translates to A&|P, which may or may not work.


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, 2005Report This Post
Master
posted Hide Post
John
My solution is to use: '&1.EVAL'

In my Maintain I have:
Compute X/a10 = 'A&P';
Exec Proc From X

In Proc I have:
TABLE FILE file
PRINT fields
WHERE STORE EQ '&1.EVAL';
ON TABLE PCHOLD
END

Without the .EVAL I was getting A&|P. With it I get A&P.

If you even need to see EXACTLY what is going on in an EXECed procedure, place:
-SET &ECHO=ALL; at the top of your procedure. Then in your Maintain, place an HTMLTable on the your form populated by FOCMSG.MSG.

Mark
 
Posts: 663 | Location: New York | Registered: May 08, 2003Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] Using an embedded ampersand in a string

Copyright © 1996-2020 Information Builders