We are on webfocus 5.3.3. We are getting an odd result with a define. I've figured out multiple work arounds but they just don't satisfy me because I don't understand why the initial problem arose in the first place. I'm wondering if anyone else has come across this. We are putting data to a hold file and we are checking WHERE PRTPSTAT EQ 'A'. So, all data in the hold file should be "A" (active). In the next section we are defining PRTPSTAT_DESC/A8 = IF PRTPSTAT EQ 'A' THEN 'ACTIVE'
ELSE 'INACTIVE';

The results are returning INACTIVE when we print it. If you display the PRTPSTAT field, it's A. It's a 1 byte alpha field. If I rename the prtpstat field with AS 'STATUS', it appears correctly. If I change the if around to check if it's "I" then "INACTIVE" ELSE "ACTIVE", it works.

Does anyone see why we're having the problem in the first place?

This is the code:
SET EMPTYREPORT=ON
SET LINES=44
SET FIELDNAME=NEW
SET ASNAMES=ON
SET CARTESIAN=OFF
SET MULTIPATH=SIMPLE
SET ALL=PASS
SET HOLDMISS=ON
SET HOLDLIST=PRINTONLY
-SET &='&'

TABLEF FILE TQTBNAME
PRINT
FEDTAXID
PYMTTYPE
PLANNBR
PLANNME
PARTNAME
NMEADRNBR
NMEADRLNE3
NMEADRLNE4
CITY
ST
PRTPSTAT

WHERE PLANNBR EQ '123456789'
WHERE PYMTTYPE IS-NOT MISSING
WHERE PRTPSTAT EQ 'A'
BY PLANNBR NOPRINT
BY FEDTAXID NOPRINT
BY PYMTTYPE NOPRINT
ON TABLE HOLD AS ALLPLANS
END

-*
-* Generate special fields
-*
DEFINE FILE ALLPLANS
FEDTAXID_DESC/A11= FEDTAXID || ' ';
PRTPSTAT_DESC/A8 = IF PRTPSTAT EQ 'A' THEN 'ACTIVE'
ELSE 'INACTIVE';
SF_TEMP/A7= GETTOK(PARTNAME,36,-1,' ',7,'A7');
SUFFIX/A7 = IF SF_TEMP EQ 'JR' OR SF_TEMP EQ 'JR.' OR SF_TEMP EQ 'SR' OR SF_TEMP EQ 'SR.' THEN SF_TEMP
ELSE IF SF_TEMP EQ 'II' OR SF_TEMP EQ 'III' OR SF_TEMP EQ 'IV' OR SF_TEMP EQ 'V' OR SF_TEMP EQ 'VI' OR SF_TEMP EQ 'VII' THEN SF_TEMP
ELSE IF SF_TEMP EQ 'DDS' OR SF_TEMP EQ 'PHD' OR SF_TEMP EQ 'ESQ' OR SF_TEMP EQ 'MD' THEN SF_TEMP
ELSE IF SF_TEMP EQ 'ESTATE' OR SF_TEMP EQ 'TRUSTEE' THEN SF_TEMP
ELSE '';
ADOCTEMP_POS/I2= IF SUFFIX EQ '' THEN 0 ELSE POSIT(PARTNAME, 36, SUFFIX, ARGLEN(7,SUFFIX,'I2'), 'I2');
AHDOCTEMPNAME/A36= IF ADOCTEMP_POS EQ 0 THEN PARTNAME
ELSE SUBSTR(36, PARTNAME, 1, ADOCTEMP_POS - 1, ADOCTEMP_POS - 1, 'A36');
AHDOCTEMPNAME/A36 = CTRAN(36, AHDOCTEMPNAME, 0, 32, AHDOCTEMPNAME);
AHDOCTEMPNAME/A36 = TRIM('B', AHDOCTEMPNAME, 36, ' ', 1, 'A36');
AHDOCTEMPNAME/A36 = SQUEEZ(36, AHDOCTEMPNAME, 'A36');
LASTNAME_SEG1/A5 = SUBSTR(12, NMEADRNBR, 1, 5, 5, LASTNAME_SEG1);
LASTNAME_SEG2/A5 = TRIM('B', CTRAN(5, LASTNAME_SEG1, 46, 32, 'A5'), 5, ' ', 1, LASTNAME_SEG2);
NAME_LEN2/I3=ARGLEN(5,LASTNAME_SEG2,NAME_LEN2);
LASTNAME_SEG4/A6 = ' ' | LASTNAME_SEG2;
NAME_LEN4/I3=ARGLEN(5,LASTNAME_SEG4,NAME_LEN4);
LASTNAME_POS2/I2= POSIT(AHDOCTEMPNAME, 36, LASTNAME_SEG2, NAME_LEN2, 'I2');
LASTNAME_POS4/I2= POSIT(AHDOCTEMPNAME, 36, LASTNAME_SEG4, NAME_LEN4, 'I2');
LASTNAME_POS4/I2= IF LASTNAME_POS4 NE 0 THEN LASTNAME_POS4 + 1;
-* PARSE OUT EVERYTHING BUT THE LAST NAME.
FIRSTMIDDLENAME/A36= IF LASTNAME_POS4 EQ 0 AND LASTNAME_POS2 EQ 0
THEN AHDOCTEMPNAME
ELSE IF LASTNAME_POS4 NE 0 AND LASTNAME_POS2 EQ LASTNAME_POS4
THEN SUBSTR(36, AHDOCTEMPNAME, 1, LASTNAME_POS4 - 1, LASTNAME_POS4 - 1, FIRSTMIDDLENAME)
ELSE IF LASTNAME_POS4 EQ 0 AND LASTNAME_POS2 NE LASTNAME_POS4
THEN SUBSTR(36, AHDOCTEMPNAME, 1, LASTNAME_POS2 - 1, LASTNAME_POS2 - 1, FIRSTMIDDLENAME);
-* PARSE OUT THE LAST NAME
LAST_NAME/A36= IF LASTNAME_POS4 EQ 0 AND LASTNAME_POS2 EQ 0
THEN GETTOK(FIRSTMIDDLENAME, ARGLEN(36,FIRSTMIDDLENAME,'I2'), -1, ' ', 36, LAST_NAME)
ELSE IF LASTNAME_POS4 NE 0 AND LASTNAME_POS2 EQ LASTNAME_POS4
THEN SUBSTR(36, AHDOCTEMPNAME, LASTNAME_POS4, 36, 36 - LASTNAME_POS4 + 1, LAST_NAME)
ELSE IF LASTNAME_POS4 EQ 0 AND LASTNAME_POS2 NE LASTNAME_POS4
THEN SUBSTR(36, AHDOCTEMPNAME, LASTNAME_POS2, 36, 36 - LASTNAME_POS2 + 1, LAST_NAME);
FIRSTMIDDLENAME/A36 = TRIM('B', FIRSTMIDDLENAME, 36, ' ', 1, FIRSTMIDDLENAME);
TOTAL_LENGTH/I2= ARGLEN(36, FIRSTMIDDLENAME, TOTAL_LENGTH);
-* SET THE MIDDLE_INIT TO THE LAST TOKEN IN THE FIRSTMIDDLENAME.
MIDDLE_INIT/A36 = GETTOK(FIRSTMIDDLENAME, ARGLEN(36,FIRSTMIDDLENAME,'I2'), -1, ' ', 36, MIDDLE_INIT);
MIDDLE_INIT/A36 = TRIM('B', MIDDLE_INIT, 36, ' ', 1, MIDDLE_INIT);
FIRST_NAME/A36 = IF MIDDLE_INIT EQ FIRSTMIDDLENAME THEN FIRSTMIDDLENAME
ELSE SUBSTR(36, FIRSTMIDDLENAME, 1, TOTAL_LENGTH - ARGLEN(36,MIDDLE_INIT,'I2') - 1, TOTAL_LENGTH - ARGLEN(36,MIDDLE_INIT,'I2') - 1, FIRST_NAME);
-* IF THE FIRST_NAME AND MIDDLE_INIT ARE THE SAME, THEN SET THE MIDDLE_INIT TO A BLANK.
MIDDLE_INIT/A36 = IF MIDDLE_INIT EQ FIRST_NAME THEN '' ELSE MIDDLE_INIT;
-* IF THE LAST_NAME AND MIDDLE_INIT ARE THE SAME AND LASTNAME_POS2 AND LASTNAME_POS4 ARE ZERO,
-* THEN BLANK OUT THE MIDDLE_INIT
MIDDLE_INIT/A36 = IF MIDDLE_INIT EQ LAST_NAME AND LASTNAME_POS4 EQ 0 AND LASTNAME_POS2 EQ 0 THEN '' ELSE MIDDLE_INIT;

END
TABLE FILE ALLPLANS
PRINT PRTPSTAT_DESC
END

Try: ON TABLE HOLD AS ALLPLANS FORMAT ALPHA
Should eliminate all workarounds...

Pan, did you try WHERE PRTPSTAT CONTAINS 'A' ?
Is the format of PRTPSTAT A1 ?
and can you do a test on the intermediate holdfile ALLPLANS
TABLE FILE ALLPLANS
BY PRTPSTAT
END
just to make sure the initial screen is doing what you expect.
would you teach me what
-SET &='&' does? looks very interesting.

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

Pam, obviously the IF condition

PRTPSTAT_DESC/A8 = IF PRTPSTAT EQ 'A' THEN 'ACTIVE'
ELSE 'INACTIVE';

is not passing. This should be the only reason why you are seeing 'INACTIVE' in the status.

You've also indicated that if you reverse the IF condition, then it does work.

PRTPSTAT_DESC/A8 = IF PRTPSTAT EQ 'I' THEN 'INACTIVE'
ELSE 'ACTIVE';

Hmmmm. Basically we're saying that the IF condition is always failing. Do you have any other columns in your data that may be prefixed with the characters 'PRTPSTAT'. Maybe the IF condition is checking another column that might appear before this column in the data structure.

Maybe do a CTRAN on the field converting all spaces to an underscore and then printing that out might reveal something.

Ken

This message has been edited. Last edited by: k.lane,

None of the suggestions worked. FORMAT Alpha didn't change anything. If I do a print of the allplans with or without any defines the value for prtpstat is equal to 'A'. The field prtpstat is an A1 field, so to do CTRAN on it doesn't make sense since the only thing you see in the field is an 'A'. I removed all defines except for the fedtaxid_desc and it still shows as inactive. If I put the prtpstat_desc above the fedtaxid_desc then it works correctly. Fedtaxid is A9. I've tried increasing the fedtaxid_desc field size, but the problem still occurs if that field is above the prtpstat_desc. Unfortunately, this is an adhoc report, so it depends how the users select the fields as to the order of the fields in the define so we can't force prtpstat to be the first field in the define (then again, we shouldn't need it as the first field).

I checked the table and there are no other fields similar to PRTPSTAT. There are no joins, so it's just the one table to look at. I scanned all the fields being used and none are spelled anything remotely close to PRTPSTAT.
I asked around and no one knows what set &='&' does ... it was around here before we started. I can only guess that it somehow allows us to have & in our data without the system thinking it needs input.

This message has been edited. Last edited by: Pam Kratt,

Syntactically, the -SET &='&' should be
-SET &='&';
But it still looks weird.
Try ? HOLD ALLPLANS to make sure that PRTPSTAT is present.

Try moving the

PRTPSTAT_DESC/A8 = IF PRTPSTAT EQ 'A' THEN 'ACTIVE'
ELSE 'INACTIVE';

to a COMPUTE...

The field is in there:
0DEFINITION OF HOLD FILE: ALLPLAN
0FIELDNAME ALIAS FORMAT
PRTPSTAT E01 A1 MISSING = ON
FEDTAXID E02 A9 MISSING = ON
PYMTTYPE E03 A2 MISSING = ON
PLANNBR E04 A12 MISSING = ON
PLANNME E05 A36 MISSING = ON
PARTNAME E06 A36 MISSING = ON
NMEADRNBR E07 A12 MISSING = ON
NMEADRLNE3 E08 A36 MISSING = ON
NMEADRLNE4 E09 A36 MISSING = ON
CITY E10 A20 MISSING = ON
ST E11 A2 MISSING = ON

I just noticed in our code that we have -SET &='&' ... somehow it got converted to -SET &='&' when I originally posted. & is what we send over in the data and then we convert it in webfocus so just the & displays. If we don't send & in the data we get errors in our code.

Pam, i concur with jimster...take that -SET & line out.
But then when you run the job, can you set echo = all and view source on the output so we can see what kind of errors you're getting?

Didn't notice the TABLEF; chg to TABLE

What I find confusing is that I'm running 5.3.3 as well and if I run a similar report to this one with the -SET &='&', I receive the following error:

(FOC312) NO FIELD IS SPECIFIED ON THE LEFT HAND SIDE OF THE ASSIGNMENT: -SET
&='&'

Ken

the compute does work (that was my suggestion this morning to the programmer) but the programmer that does the adhoc piece says he can't do a compute based on how the users select the fields ... it has to be a define. I don't know enough about the adhoc query and how the code is generated that I take his word that he can't do a compute.

The -SET command is an & plus the word AMP ... it's being converted to & and removing the word AMP.
I removed the -SET &=&, still didn't work.
I removed the TABLEF and changed to TABLE, still didn't work.
I put SET ECHO = ALL and there are no errors.

It is definitely something with the field name. If I use E11 in the define (the column # that webfocus assigns), it works.

This message has been edited. Last edited by: Pam Kratt,

Pam, realize you're frustrated, but, my last shot:
PRTPSTAT_DESC/A8 WITH PLANNBR =
IF PRTPSTAT EQ 'A' THEN 'ACTIVE'
ELSE 'INACTIVE';

I thought that was it. That solution made sense also (to add with plannbr). It didn't work.

I finally decided to try and put a define in the master file and that worked! It still doesn't make sense why all the other suggestions didn't work, but at least we have something that will work.

Thanks everyone for all of your suggestions.

Run
? LET
and see whether A is defined to mean something else.

It comes back with "NO LETS CURRENTLY IN EFFECT."

Hi Pam,

Just a few questions that might help to identify the "wierd" problem you are experiencing (or then maybe not? ).

What type of table is TQTBNAME? e.g SQL, DB2, Focus.
Depending upon the answer above, is it a view, multi segmented etc?
Do you have any JOINs in effect? (? JOIN)
What other SETs do you have in your profiles? (? SET)

I appreciate that you can't specify in which order your programmer relates the fields and therefore you can't specify the internal FOCUS column name (E11 etc.) but how about prefixing the field name with an AS in the initial extract?

Incidently the ampersand thingy on posting is the browser interpreting your & amp as an internal item due to the semi colon after it (much the same as & nbsp; etc.)

TQTBNAME is a db2 table. It is not a view and there are no segments. Very simple table. There are no joins in effect (I've seen odd things with joins so that would make sense but there are no joins). Prefixing with an AS was my first suggestion to him yesterday because that worked, but there was some reason he couldn't do it.

I don't know if you saw my earlier post that I recommended him putting a define in the master file for tqtbname instead of the define on the hold file and that did work. So, we do have something that works now ... it just doesn't explain the odd behaviour.

These are my parameter settings. I hope they read ok (they look odd in this post).
PARAMETER SETTINGS
ALL. PASS FOCSTACK SIZE 8 QUALCHAR .
ASNAMES ON FORMFEED ASCII QUALTITLES OFF
AUTOINDEX ON HDAY REBUILDMSG 1000
AUTOPATH ON HIPERFOCUS OFF RECAP-COUNT OFF
BINS 64 HOLDATTRS FOCUS SAVEMATRIX OFF
BLKCALC NEW HOLDLIST PRINTONLY SCREEN OFF
BUSDAYS _MTWTF_ HOLDSTAT OFF SHADOW PAGE OFF
BYPANELING OFF HOTMENU OFF SPACES AUTO
CACHE 0 IBMLE OFF SQLENGINE
CARTESIAN OFF INDEX TYPE NEW SUMPREFIX LST
CDN OFF LANGUAGE AMENGLISH TCPIPINT OFF
COLUMNSCROLL OFF LINES/PAGE 66 TEMP DISK /app01/u01/..
DATEDISPLAY OFF LINES/PRINT 44 TERMINAL IBM3270
DATEFNS ON MESSAGE ON TESTDATE TODAY
DATETIME STARTUP/RESET MODE UNIX TITLES ON
DEFCENT 19 MORE OFF VIEWNAMESIZE 60
EMPTYREPORT ON MULTIPATH SIMPLE WIDTH 80
EXL2KLANG 1 NODATA . WINPFKEY OLD
EXTAGGR ON PAGE-NUM ON XFBINS 16 (passive)
EXTHOLD ON PANEL 0 XFC ON
EXTSORT OFF PAUSE OFF XRETRIEVAL ON
FIELDNAME NEW POOL OFF YRTHRESH 0
FOC2GIGDB ON PRINT ONLINE
FOCALLOC OFF PRINTPLUS OFF
1

Hi Pam,

I take it that you are running in a Unix partition on Z/Os then against DB2? Not that it matters much as it should function virtually the same for all.

I did read your previous posting about the master Define and that would appear to me to be the best solution / workaround especially as your programmer couldn't (or wouldn't - only kidding) use a prefix etc. Glad you have a way forward at least.

As for the reason of the problem - no idea I'm afraid, all the settings look fine as far as I can tell (my eyes now hurt ) and I was hoping that a spurious JOIN would be the culprit - but no.

I'm curious: Does DECODE recognize the I and A properly?

Say,

PRTPSTAT_DESC/A8 = DECODE PRTPSTAT ('A' 'ACTIVE',
'I' 'INACTIVE',
ELSE 'MYSTERY');

table ... count entries by PRTPSTAT_DESC by PRTPSTAT

MYSTERY displays for all records. Good suggestion for something to look at. I'm sure glad the define in the master works!

How Interesting. Could you try one more thing? Try changing the defined field name from PRTPSTAT_DESC to something else, like PRTSTAT_DESC.

Just another long-shot . . . but maybe there's a bug leftover from the old FOCUS field name truncation, where you only had to input enough characters to identify a unique field name. (If the field name was DEPARTMENT, you could input DEP as long as DEPARTMENT was the only field that started with DEP).

no luck. Named the field PRSTAT_DESC and still got the odd results. Thanks for the suggestion.

OK, something else to try. I see that you have SET HOLDLIST=PRINTONLY. I'm not sure how your DEFINE for the FEDTAXID_DESC can be working. Because the FEDTAXID field was NOPRINTed, it will not be included in the HOLD file when HOLDLIST=PRINTONLY.

Since you mentioned that the PRTPSTAT_DESC field def works OK when it appears BEFORE the FEDTAXID_DESC definition, I wonder if the problem is REALLY with the FEDTAXID_DESC field.

Try setting HOLDLIST=ALL and see if anything changes.

no change. thanks again.