Focal Point
[CLOSED] Using an embedded ampersand in a string

This topic can be found at:
https://forums.informationbuilders.com/eve/forums/a/tpc/f/7971057331/m/3931023382

May 07, 2008, 08:52 PM
Norb Eckert
[CLOSED] Using an embedded ampersand in a string
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
May 07, 2008, 11:46 PM
Waz
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!

May 09, 2008, 04:02 PM
Norb Eckert
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
May 09, 2008, 04:50 PM
Tom Flynn
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
May 09, 2008, 04:52 PM
smiths
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
May 09, 2008, 05:09 PM
Glenda
Norb,

Have you tried this:

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



Glenda

In FOCUS Since 1990
Production 8.2 Windows
May 09, 2008, 05:34 PM
Alan B
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
May 10, 2008, 07:28 AM
Tom Flynn
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
June 24, 2008, 07:51 PM
susannah
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
August 03, 2010, 04:18 PM
John_Edwards
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.



August 03, 2010, 04:52 PM
susannah
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
August 03, 2010, 06:35 PM
John_Edwards
. . . 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.



August 04, 2010, 04:08 AM
Alan B
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
August 04, 2010, 09:04 AM
John_Edwards
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,



August 04, 2010, 10:46 AM
Francis Mariani
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
August 04, 2010, 12:05 PM
John_Edwards
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.



August 04, 2010, 12:18 PM
Francis Mariani
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
August 09, 2010, 09:07 AM
Maintain Wizard
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