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,

Norb, try putting a "|" pipe between the & and the rest of the variable.

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

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

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

Norb,

Does it work if you use QUOTEDSTRING?:

-SET &LOOKUP_Q = &LOOKUP.QUOTEDSTRING;

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

Sean

Norb,

Have you tried this:

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

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.

Yep, I made it more complex than it needed to be for this scenario...

Glenda and Alan examples work best.

Tom

has anyone noticed that glenda's post got edited by the very funny 'bad words you cant say on tv' forum editor???

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.

escape character
&|
Use a STRREP command to edit the incoming character string
stripping out the & and replacing with a &|

. . . 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.

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'.

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,

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?

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.

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.

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