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.
TABLE FILE SAFILE
PRINT
SA0LW AS SA0LW
SA08R AS SA08R
SA000 AS SA000
SA0M7 AS SA0M7
SA08S AS SA08S
SA0L2 AS SA0L2
SA2DG AS SA2DG
SA000 AS SA000
AND COMPUTE UNMET_NEED / D10.2 = SA0L2-SA08R;
BY STU_ID
WHERE SA000 EQ '&AWDYR1'
ON TABLE HOLD AS S023A&INSTX
END
-RUN
-*
However, iwhen I place that very same code into antoher FOCEXEC, the data gets skewed - the data that should go with the correct STU_ID shows up on the next STU_ID line! Here is the same code from the offending program:
TABLE FILE SAFILE
PRINT
SA0LW AS SA0LW
SA08R AS SA08R
SA000 AS SA000
SA0M7 AS SA0M7
SA08S AS SA08S
SA0L2 AS SA0L2
SA2DG AS SA2DG
SA000 AS SA000
AND COMPUTE UNMET_NEED / D10.2 = SA0L2-SA08R;
BY STU_ID
WHERE SA000 EQ '&AWDYR1'
ON TABLE HOLD AS S022X&INSTX
END
-RUN
-*
What might cause something like that? If anyone has an idea or two, I would really love to hear it. Thanks in advance!This message has been edited. Last edited by: Kerry,
I am using an original database. It is not the result of a JOIN or of a FEX that creates an intermediate HOLD file. Also, I had mentioned the code works great in another totally different FEX, but not in this one. Here is the complete code, with the offending portion fairly near the top of the code and just to let you know, you'll see the same code repeated three times, in order to get three separate years of data. That's probably not the best way to do it, but it's what I've got at the moment. (Please do not concern yourself with the remaining code, since this is a work in progress.):
SET ASNAMES = ON
SET MSG = ON
-*SET MULTIPATH = COMPOUND
-DEFAULT &AWDYR1 = 2006;
-DEFAULT &AWDYR2 = 2007;
-DEFAULT &AWDYR3 = 2008;
-SET &YR1 = &AWDYR1 - 0001;
-SET &YR2 = &AWDYR2 - 0001;
-SET &YR3 = &AWDYR3 - 0001;
-SET &FALL1 = &YR1 | '1';
-SET &SPR1 = &AWDYR1 | '2';
-SET &FALL2 = &YR2 | '1';
-SET &SPR2 = &AWDYR2 | '2';
-SET &FALL3 = &YR3 | '1';
-SET &SPR3 = &AWDYR3 | '2';
-DEFAULT &INST = '03';
-DEFAULT &PRG = 'SFA080';
-DEFAULT &STARTKEY = '000000000';
-DEFAULT &ENDKEY = '999999999';
-SET &TITLE =
- 'SCHOLARSHIPS OF REQUESTED FUND OVER 2 YEAR PERIOD';
-*
-INCLUDE FOCDEF
-*
-SET &PARM1 = 'PROGRAM ID :';
-SET &PARM2 = 'PROGRAM TITLE:';
-SET &PARM3 = 'INSTITUTION :';
-SET &PARM4 = 'AWARD YEARS :';
-SET &PARM6 = 'FUND :';
-SET &VALUE1 = &PRG;
-SET &VALUE2 = &TITLE;
-SET &VALUE3 = &INST;
-SET &VALUE4 = &AWDYR1 | '&' | &AWDYR2 | '&' | &AWDYR3;
-*
EX DYNALLOC DD=AAFILE,DSN=&INSTX|.AAFILE
EX DYNALLOC DD=RTFILE,DSN=&INSTX|.RTFILE
EX DYNALLOC DD=SAFILE,DSN=&INSTX|.SAFILE
EX DYNALLOC DD=SFFILE,DSN=&INSTX|.SFFILE
EX DYNALLOC DD=SWFILE,DSN=&INSTX|.SWFILE
-*
-*===============================================================
-*================= PROCESSING SAFILE ========================
-*===============================================================
TABLE FILE SAFILE
PRINT
SA0LW AS SA0LW
SA08R AS SA08R
SA000 AS SA000
SA0M7 AS SA0M7
SA08S AS SA08S
SA0L2 AS SA0L2
SA2DG AS SA2DG
SA000 AS SA000
AND COMPUTE UNMET_NEED / D10.2 = SA0L2-SA08R;
BY STU_ID
WHERE SA000 EQ '&AWDYR1'
-*WHERE STU_ID EQ '453896181'
ON TABLE HOLD AS S022X&INSTX
END
-RUN
-*
TABLEF FILE S022X&INSTX
PRINT *
END
-RUN
-*
TABLE FILE SAFILE
PRINT
SA0LW AS SA0LW
SA08R AS SA08R
SA000 AS SA000
SA0M7 AS SA0M7
SA08S AS SA08S
SA0L2 AS SA0L2
SA2DG AS SA2DG
SA000 AS SA000
AND COMPUTE UNMET_NEED / D10.2 = SA0L2-SA08R;
BY STU_ID
WHERE SA000 EQ '&AWDYR2'
-*WHERE STU_ID EQ '453896181'
ON TABLE HOLD AS S022Y&INSTX
END
-RUN
-*
TABLEF FILE S022Y&INSTX
PRINT *
END
-RUN
-*
TABLE FILE SAFILE
PRINT
SA0LW AS SA0LW
SA08R AS SA08R
SA000 AS SA000
SA0M7 AS SA0M7
SA08S AS SA08S
SA0L2 AS SA0L2
SA2DG AS SA2DG
SA000 AS SA000
AND COMPUTE UNMET_NEED / D10.2 = SA0L2-SA08R;
BY STU_ID
WHERE SA000 EQ '&AWDYR3'
-*WHERE STU_ID EQ '453896181'
ON TABLE HOLD AS S022Z&INSTX
END
-RUN
-*
TABLE FILE S022Z&INSTX
PRINT
SA0LW
SA08R
SA000
SA0M7
SA08S
SA0L2
SA2DG
SA000
BY STU_ID
END
-RUN
-*
-*===============================================================
-*================= PROCESSING SWFILE ========================
-*===============================================================
-STEP1
-TYPE ...STEPSW RUNNING
-*
JOIN CLEAR *
-*
-*JOIN SW_FUND IN SWFILE TO ROOT_KEY IN SFFILE AS SWSF
-*
DEFINE FILE SWFILE
FUNDPART /A04 = EDIT(SW_FUND,'9999$');
YEAR1 /A04 = '&AWDYR1';
YEAR2 /A04 = '&AWDYR2';
YEAR3 /A04 = '&AWDYR3';
Y1 /A01 = EDIT(YEAR1,'$$$9');
Y2 /A01 = EDIT(YEAR2,'$$$9');
Y3 /A01 = EDIT(YEAR3,'$$$9');
SELFUNDS /A01 = IF (SW_FUND EQ '8930'||Y1 OR '8930'||Y2 OR '8930'||Y3
OR '8940'||Y1 OR '8940'||Y2 OR '8940'||Y3
OR '8950'||Y1 OR '8950'||Y2 OR '8950'||Y3
OR '8960'||Y1 OR '8960'||Y2 OR '8950'||Y3)
THEN 'Y' ELSE 'N';
FALLAWD1 /P09.2 = IF (SW121 EQ '&FALL1') THEN SW123 ELSE 0;
FALLAWD2 /P09.2 = IF (SW121 EQ '&FALL2') THEN SW123 ELSE 0;
FALLAWD3 /P09.2 = IF (SW121 EQ '&FALL3') THEN SW123 ELSE 0;
SPRAWD1 /P09.2 = IF (SW121 EQ '&SPR1') THEN SW123 ELSE 0;
SPRAWD2 /P09.2 = IF (SW121 EQ '&SPR2') THEN SW123 ELSE 0;
SPRAWD3 /P09.2 = IF (SW121 EQ '&SPR3') THEN SW123 ELSE 0;
FALLERND1 /P09.2 = IF (SW121 EQ '&FALL1') THEN SW124 ELSE 0;
FALLERND2 /P09.2 = IF (SW121 EQ '&FALL2') THEN SW124 ELSE 0;
FALLERND3 /P09.2 = IF (SW121 EQ '&FALL3') THEN SW124 ELSE 0;
SPRERND1 /P09.2 = IF (SW121 EQ '&SPR1') THEN SW124 ELSE 0;
SPRERND2 /P09.2 = IF (SW121 EQ '&SPR2') THEN SW124 ELSE 0;
SPRERND3 /P09.2 = IF (SW121 EQ '&SPR3') THEN SW124 ELSE 0;
END
-*
TABLE FILE SWFILE
-*
WRITE
-*
MAX.SELFUNDS
MAX.SW_FUND
MAX.FALLAWD1
MAX.SPRAWD1
MAX.FALLERND1
MAX.SPRERND1
MAX.AWD_YR
-*
BY STU_ID
BY AWD_YR
-*
WHERE AWD_YR EQ '&AWDYR1' AND AWD_PER_ID NE ' '
-*WHERE SELFUNDS EQ 'Y'
WHERE FUNDPART EQ '8930' OR '8940' OR '8950' OR '8960'
WHERE STU_ID IS-FROM '&STARTKEY' TO '&ENDKEY'
-*
ON TABLE HOLD AS S0221&INSTX
-*
END
-RUN
-*
-IF &RECORDS EQ 0 GOTO NOLINES;
-*
TABLE FILE S0221&INSTX
HEADING
"***** FROM S0221"
PRINT
SELFUNDS
SW_FUND
FALLAWD1
SPRAWD1
FALLERND1
SPRERND1
-*
BY STU_ID
BY AWD_YR
END
-RUN
-*
TABLE FILE SWFILE
-*
WRITE
-*
MAX.SELFUNDS
MAX.SW_FUND
MAX.FALLAWD2
MAX.SPRAWD2
MAX.FALLERND2
MAX.SPRERND2
MAX.AWD_YR
-*
BY STU_ID
BY AWD_YR
-*
WHERE AWD_YR EQ '&AWDYR2' AND AWD_PER_ID NE ' '
-*WHERE SELFUNDS EQ 'Y'
WHERE FUNDPART EQ '8930' OR '8940' OR '8950' OR '8960'
WHERE STU_ID IS-FROM '&STARTKEY' TO '&ENDKEY'
-*
ON TABLE HOLD AS S0222&INSTX
-*
END
-RUN
-*
TABLE FILE SWFILE
-*
WRITE
-*
MAX.SELFUNDS
MAX.SW_FUND
MAX.FALLAWD3
MAX.SPRAWD3
MAX.FALLERND3
MAX.SPRERND3
MAX.AWD_YR
-*
BY STU_ID
BY AWD_YR
-*
WHERE AWD_YR EQ '&AWDYR3' AND AWD_PER_ID NE ' '
-*WHERE SELFUNDS EQ 'Y'
WHERE FUNDPART EQ '8930' OR '8940' OR '8950' OR '8960'
WHERE STU_ID IS-FROM '&STARTKEY' TO '&ENDKEY'
-*
ON TABLE HOLD AS S0223&INSTX
-*
END
-RUN
-*
JOIN STU_ID IN S0221&INSTX TO STU_ID IN S0222&INSTX AS AAA
JOIN STU_ID IN S0221&INSTX TO STU_ID IN S0223&INSTX AS BBB
-*
TABLE FILE S0221&INSTX
-*
PRINT
-*
SELFUNDS
SW_FUND
FALLAWD1
FALLAWD2
FALLAWD3
SPRAWD1
SPRAWD2
SPRAWD3
FALLERND1
FALLERND2
FALLERND3
SPRERND1
SPRERND2
SPRERND3
-*
BY STU_ID
BY AWD_YR
-*
WHERE SELFUNDS EQ 'Y'
WHERE STU_ID IS-FROM '&STARTKEY' TO '&ENDKEY'
-*
ON TABLE HOLD AS S022A&INSTX
-*
END
-RUN
-*
-*===============================================================
-*================= PROCESSING RTFILE ========================
-*===============================================================
-STEPRT
-*
JOIN CLEAR *
-*
SET ALL = OFF
-*
-TYPE ...CREATING TEMP FILE 3
-*
DEFINE FILE RTFILE
NHOURS /D8 = RT020;
LHOURS /A2 = EDIT(NHOURS,'$$$$$$99');
FALLHRS1 /A08 = IF (TERM EQ '&FALL1') AND (RT020 GT 0) THEN
'&FALL1' || ' ' | LHOURS ELSE ' ';
SPRHRS1 /A08 = IF (TERM EQ '&SPR1') AND (RT020 GT 0) THEN
'&SPR1' || ' ' | LHOURS ELSE ' ';
FALLHRS2 /A08 = IF (TERM EQ '&FALL2') AND (RT020 GT 0) THEN
'&FALL2' || ' ' | LHOURS ELSE ' ';
SPRHRS2 /A08 = IF (TERM EQ '&SPR2') AND (RT020 GT 0) THEN
'&SPR2' || ' ' | LHOURS ELSE ' ';
FALLHRS3 /A08 = IF (TERM EQ '&FALL3') AND (RT020 GT 0) THEN
'&FALL3' || ' ' | LHOURS ELSE ' ';
SPRHRS3 /A08 = IF (TERM EQ '&SPR3') AND (RT020 GT 0) THEN
'&SPR3' || ' ' | LHOURS ELSE ' ';
END
-*
TABLE FILE RTFILE
-*
PRINT
-*
MAX.FALLHRS1
MAX.SPRHRS1
MAX.FALLHRS2
MAX.SPRHRS2
MAX.FALLHRS3
MAX.SPRHRS3
AND COMPUTE
AWD_YR/A04 = IF (TERM EQ '&FALL1' OR '&SPR1')
THEN '&AWDYR1' ELSE
IF (TERM EQ '&FALL2' OR '&SPR2')
THEN '&AWDYR2' ELSE '&AWDYR3';
-*
BY STU_ID
-*
WHERE TERM EQ '&FALL1' OR '&SPR1' OR '&FALL2' OR '&SPR2'
OR '&FALL3' OR '&SPR3'
WHERE STU_ID IS-FROM '&STARTKEY' TO '&ENDKEY'
-*
ON TABLE HOLD AS S022B&INSTX
-*
END
-RUN
-*
TABLE FILE S022B&INSTX
-*
WRITE
-*
MAX.FALLHRS1
MAX.SPRHRS1
MAX.FALLHRS2
MAX.SPRHRS2
MAX.FALLHRS3
MAX.SPRHRS3
-*
BY STU_ID
BY AWD_YR
-*
WHERE TERM EQ '&FALL1' OR '&SPR1' OR '&FALL2' OR '&SPR2'
OR '&FALL3' OR '&SPR3'
WHERE STU_ID IS-FROM '&STARTKEY' TO '&ENDKEY'
-*
ON TABLE HOLD AS S022C&INSTX
-*
END
-RUN
-*
-IF &RECORDS EQ 0 GOTO NOLINES;
-*
JOIN CLEAR *
JOIN STU_ID IN S022A&INSTX TO STU_ID IN AAFILE AS SATOAA
JOIN STU_ID IN S022A&INSTX TO STU_ID IN S022Z&INSTX AS SATOBB
-*
TABLE FILE S022A&INSTX
-*
PRINT
-*
SELFUNDS
SW_FUND
FALLAWD1
FALLAWD2
FALLAWD3
SPRAWD1
SPRAWD2
SPRAWD3
FALLERND1
FALLERND2
FALLERND3
SPRERND1
SPRERND2
SPRERND3
STU_NAME/A25
UNMET_NEED
-*
BY STU_ID
BY AWD_YR
-*
ON TABLE HOLD AS S022D&INSTX
-*
END
-RUN
-*
-IF &RECORDS EQ 0 GOTO NOLINES;
-*
TABLE FILE S022D&INSTX
HEADING
"***** FROM S022D"
PRINT
SELFUNDS
SW_FUND
FALLAWD3
SPRAWD3
FALLERND1
FALLERND2
FALLERND3
SPRERND1
SPRERND2
SPRERND3
-*
BY STU_ID
BY AWD_YR
END
-RUN
-*
-TYPE ...MERGING FILES NOW!
-*
JOIN CLEAR *
-*
SET ALL = ON
-*
JOIN STU_ID AND AWD_YR IN S022D&INSTX TO STU_ID AND AWD_YR IN S022C&INSTX
-*
TABLE FILE S022D&INSTX
-*
PRINT
-*
STU_NAME
SELFUNDS
SW_FUND
MAX.FALLHRS1
MAX.SPRHRS1
MAX.FALLHRS2
MAX.SPRHRS2
MAX.FALLHRS3
MAX.SPRHRS3
MAX.FALLAWD1
MAX.FALLAWD2
MAX.FALLAWD3
MAX.SPRAWD1
MAX.SPRAWD2
MAX.SPRAWD3
MAX.FALLERND1
MAX.FALLERND2
MAX.FALLERND3
MAX.SPRERND1
MAX.SPRERND2
MAX.SPRERND3
UNMET_NEED
-*
BY STU_ID
BY AWD_YR
-*
WHERE STU_ID IS-FROM '&STARTKEY' TO '&ENDKEY'
WHERE SELFUNDS EQ 'Y'
-*
ON TABLE HOLD AS S022E&INSTX
-*
END
-RUN
-*
-IF &RECORDS EQ 0 GOTO NOLINES;
-*
SET NODATA = 0
-*
-*
TABLE FILE S022E&INSTX
HEADING
"***** FROM S022E"
PRINT
SELFUNDS
SW_FUND
MAX.FALLHRS3
MAX.SPRHRS3
MAX.FALLAWD3
MAX.SPRAWD3
MAX.FALLERND1
MAX.FALLERND2
MAX.FALLERND3
MAX.SPRERND1
MAX.SPRERND2
MAX.SPRERND3
UNMET_NEED
-*
BY STU_ID
BY AWD_YR
END
-RUN
-*
DEFINE FILE S022E&INSTX ADD
BOTH /A07 = IF (STU_ID EQ LAST STU_ID) THEN 'RENEWED' ELSE
'NEW';
INST /A03 = '&INSTX';
-INCLUDE DYNOFFP
-*
TABLE FILE S022E&INSTX
-*
PRINT
-*
MAX.SW_FUND AS 'FUND'
MAX.UNMET_NEED AS 'UNMET,NEED'
MAX.FALLHRS1 AS 'FAL1,HRS'
MAX.FALLAWD1 AS 'FAL1,AWD'
MAX.FALLERND1 AS 'FAL1,E/P'
MAX.SPRHRS1 AS 'SPR1,HRS'
MAX.SPRAWD1 AS 'SPR1,AWD'
MAX.SPRERND1 AS 'SPR1,E/P'
MAX.FALLHRS2 AS 'FAL2,HRS'
MAX.SPRHRS2 AS 'SPR2,HRS'
MAX.FALLHRS3 AS 'FAL3,HRS'
MAX.SPRHRS3 AS 'SPR3,HRS'
-*
-* MAX.FALLHRS2
-* MAX.SPRHRS2
-* MAX.FALLHRS3
-* MAX.SPRHRS3
-* MAX.FALLAWD2
-* MAX.FALLAWD3
-* MAX.SPRAWD2
-* MAX.SPRAWD3
-* MAX.FALLERND2
-* MAX.FALLERND3
-* MAX.SPRERND2
-* MAX.SPRERND3
-*
-*WHERE STU_NAME NE ' '
WHERE STU_ID EQ '453896181'
-*
BY INST REPAGE SUB-TOTAL
BY STU_NAME NOSPLIT UNDER-LINE
BY STU_ID
BY AWD_YR AS 'AWD,YR'
-*
WHERE SELFUNDS EQ 'Y'
ON TABLE HOLD AS S080A FORMAT EXCEL
-*ON STU_NAME SUBFOOT
-*" <BOTH"
-*
-*
-INCLUDE REPTDEL7
-*
END
-*
-INCLUDE RETYPE
-RUN
-*
-IF &RECORDS EQ 0 GOTO NOLINES;
-*
-*
-GOOD TYPE Run Successfully Completed
-EXIT
-NOLINES
-TYPE NO RECORDS FOUND
-RUN
-*
-EXIT
You store the output in an holdfile and you show us some output. Is this the result of a table request from these hold files or did you open the holdfiles in a NOTEPAD or similar program?
Your request creates a hold file with 10 fields and you show us only 7 fields, why?
Frank
prod: WF 7.6.10 platform Windows, databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7 test: WF 7.6.10 on the same platform and databases,IE7
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006
the result of a table request from these hold files
Please note in the code I supplied, that there is a TABLEF against the hold file just created.
I only show 7 fields because that should be enough to show how the data is skewed, plus I did not want to have the data line "wrap" and possibly confuse anyone.
If not for the misalignment, would you be satisfied? If these three HOLD files are drawn from data of different periods, why are the data rows identical?
Maybe you should turn your attention to your data source, SAFILE. It seems to be a summary. Maybe the fault lies upstream in the process that assembles it.
- Jack Gross WF through 8.1.05
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005
Not sure what you mean... can you explain a bit more?
Frank, No time today to try your sample....
I have to leave work early today and will be out tomorrow, but will try to give you both good answers when I return on Wednesday. Thanks for replying...I appreciate it very much!
I meant, the three hold requests differ only by the year (2006,7,8) in WHERE SA000 EQ '....'. When you look at the dependent data (columns 2-6), you have IDENTICAL vectors of values (e.g., 6,491 1,755.00 2008 377 1,755.00 6,114 ) appearing in the hold files, but attached to different student-id keys.
I don't know the nature of your data, but if I can assume that at least some of the first six dependent columns SA0LW SA08R SA000 SA0M7 SA08S SA0L2 (the only ones you showed us) can vary from year (SA000) to year for a given student, then your data look improbable even if we can posit a program gremlin that causes rows of data to slip by one student-id. Removing the gremlin to reattach the data rows to the right student key would leave you with identical results, where you would expect some year-to-year perturbations.
Ergo, I conjectured that the common data source SAFILE may be faulty.
- - -
ALSO: what's in in FOCDEF, and what is the structure of SAFILE (suffix, segment structure)
- Jack Gross WF through 8.1.05
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005
Actually, to all, the problem sorted itself out just fine. I had a filedef of the wrong length in one part of the program that was causing the problem, and when I corrected that, everything ended up running just fine.
I wish to thank all of you wonderful folk who answered and offered help and advice. You make this forum a great place.