DEFINE FILE CAR
TEST1/A20 = DECODE COUNTRY(FRANCE 'JUST A T''EST'
ENGLAND 'JU''ST ANOTHER TEST'
ELSE 'PEOPLE''S – YORK RD');
TEST2/A20 = DECODE TEST1('PEOPLE''S – YORK RD' 'PCHC - BCHD ETI'
ELSE ' ');
END
-*
TABLE FILE CAR
PRINT CAR
COUNTRY
TEST1
TEST2
END
Glenda
In FOCUS Since 1990 Production 8.2 Windows
December 12, 2008, 08:22 AM
John_Edwards
Yeah, the double apostrophe didn't work and threw some really strange errors.
String Replace is pretty doggone ugly and likely means I need to have three defined fields to make the connection (this is for a join in Data Migrator) but I may just have to go that far down the pipe to get an answer.
Thank you all!
J.
December 12, 2008, 08:31 AM
<JG>
Silly question but don't you have key that you can decode on instead of the text.
Alternatively try using IF-THEN-ELSE syntax it should handle the ''.
December 12, 2008, 09:10 AM
John_Edwards
I'm likely going to go with the if-then-else for a first start (it's only about a dozen values).
Key? I'm the guy creating the key. Once I get the data from the seven tables that were never designed to work with each other I'll have a nice set of information with a perfectly reasonable key at the front of it. That's when I hand it off to the rookies.
J.
December 12, 2008, 09:14 AM
Glenda
The double apostrophe works in 5.3.6. It concerns me that it won't work in the newer releases.
Glenda
In FOCUS Since 1990 Production 8.2 Windows
December 12, 2008, 09:15 AM
Glenda
I guess I should have said may not work in the newer releases.
Glenda
In FOCUS Since 1990 Production 8.2 Windows
December 13, 2008, 05:42 AM
Alan B
Double apostrophe works for me in 7.6.4 Glenda, so don't worry!
I don't see why it should become unsupported as it is built in to .QUOTEDSTRING
-SET &ECHO=ALL;
-SET &VAR1 = 'PEOPLE'S - YORK RD';
DEFINE FILE CAR
TEST1/A20 = DECODE COUNTRY(FRANCE 'JUST A T''EST'
ENGLAND 'JU''ST ANOTHER TEST'
ELSE 'PEOPLE''S - YORK RD');
TEST2/A20 = DECODE TEST1(&VAR1.QUOTEDSTRING 'PCHC - BCHD ETI'
ELSE ' ');
END
-*
TABLE FILE CAR
PRINT CAR
COUNTRY
TEST1
TEST2
END
Maybe John needs to retry this approach as it is the cleanest around.
Alan. WF 7.705/8.007
December 13, 2008, 12:28 PM
Tom Flynn
All,
Maybe I'm not getting the process(my ignorance has no boundaries!), but, when they change the data and remove the apostrophe, will the decode work???
quote:
String Replace is pretty doggone ugly and likely means I need to have three defined fields to make the connection (this is for a join in Data Migrator) but I may just have to go that far down the pipe to get an answer.
Well, you will need a minimum of 2 , with DECODE, so 1 more line of code will strain the mind????
That is why I don't hard-code WITH special characters:
Assuming the PROVIDER length is known, say, A50:
-* Remove apostrophe from all records REM_APOS/A50 = STRREP(50, PROVIDER, 1, '''', 0, 'X', 50, REM_APOS);
-* DECODE without apostrophe TEST1/A50 = DECODE REM_APOS ('PEOPLES - YORK RD' 'PCHC - BCHD ETI' ELSE 'XXX');
-* Get non-DECODEd data TEST2/A50 = IF TEST1 EQ 'XXX' THEN PROVIDER ELSE TEST1;
TEST2 is now my column for display/JOIN. If the apostrophe is removed, DECODE still works...
That is why I don't hard-code WITH special characters:
When you're faced with an application that allows free form text entry, the users will - quite rightly - take full advantage and however much you might like to avoid coding using special caharacters, sometimes the applications from which you have to report do not allow the luxury to choose!!
T
In FOCUS since 1986
WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2
WebFOCUS App Studio 8.2.06 standalone on Windows 10
December 15, 2008, 07:56 AM
Tom Flynn
Tony,
When did DECODE from a production table turn into input from a user text box????
In answer to your "new" comment, I would use, say, for example, STRREP...
You bit and who mentioned user text box? I mentioned "application that allows free form text entry" which could be any form of input. Even a proudction table has it's input served from some "form" of input.
As for apostrophes within user data - I normally change them using STRREP to a HEXBYT(96,'A1') or something so that it still retains " a look" of the original input.
T
In FOCUS since 1986
WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2
WebFOCUS App Studio 8.2.06 standalone on Windows 10
December 15, 2008, 09:12 AM
Tom Flynn
Well, then, I'm happy for you. Thanks for sharing...
Sorry I didn't get back to you guys sooner - you seem to be having quite a party without me.
I have stumbled onto additional issues that seem to be muddying up the works, but it appears that the double apostrophes is not a problem in DECODE. It is indeed a problem when one of them merely looks like an apostrophe but isn't. There's also two different dash characters in unicode. This is why I get the big bucks, and the nagging headaches.
In this case the issue is not user entry fields or display, since all of this effort is for data transformation and migration. I think the heart of the matter is the tortured path of operating systems and tools used to get the data into a position where I can grab it. Somewhere along the way a poor decision is being made (likely automated) regarding which unicode values should be used to represent what. Everything looks fine, but it's not.
So once again a task booked at six hours takes five days. Software is expensive because it's technologically in the same stage as building architecture was when St. David''s Cathedral was built.