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.
I have a report where I pull a student's highest eff dated program and put that into a hold file. Then I do a left outer join from my hold file to the plan table to pull in a student's plan. I have found though that I am always missing about 20 students when I add the plan. I need these students included even if they don't have a record in the child table. I have tried the set all=on and set all=pass. I also tried set all=sql from other discussions on here and none of those are working.
Please help
Thanks, MalindaThis message has been edited. Last edited by: Kerry,
WebFOCUS 7.6.11 Windows all output (Excel, HTML, PDF)
It may depend on what you're using for selection criteria (WHERE statements.) I have found instances where a test on a non-existent (or null) field in the child segment can eliminate a record even though SET ALL=PASS is set.
We may be able to help is we can see your code example.
Regards,
Darin
In FOCUS since 1991 WF Server: 7.7.04 on Linux and Z/OS, ReportCaster, Self-Service, MRE, Java, Flex Data: DB2/UDB, Adabas, SQL Server Output: HTML,PDF,EXL2K/07, PS, AHTML, Flex WF Client: 77 on Linux w/Tomcat
Posts: 2298 | Location: Salt Lake City, Utah | Registered: February 02, 2007
I have a report where I pull a student's highest eff dated program and put that into a hold file.
As you are getting a set of data, HOLDing it and then joining it back to the database I am don't think that SET ALL=SQL would be applicable as the database won't be handling the JOIN. Most likely WebFOCUS will be looping through the HOLD file and connecting to the database to retrieve matching records for each instance.
You may need to change your approach a little to achieve what you want.
1. One alternative would be to make your database handle the whole request (in a single JOIN) but that may not be feasible as you need to first collect the HIGHEST EDD DATED program and then join it back.
2. Another one would be to write the entire statement in SQL and run it through SQL passthru. That would give you full control and most likely better performance but maintenance might be difficult for non-SQL developers.
3. Using what you have, you may attempt to use something like this:
a. Get the intended students in HOLD_A FORMAT ALPHA (you already have this one)
b. Get student plans for all those students:
TABLE FILE STUDENTS
PRINT blah
WHERE STUDENT_ID IN (HOLD_A)
ON TABLE HOLD AS HOLD_B FORMAT FOCUS INDEX BY STUDENT_ID
END
c. Outer Join HOLD_A to HOLD_B!
SET ALL=ON
JOIN ST_ID IN HOLD_A TO ALL STUDENT_ID IN HOLD_B
TABLE FILE HOLD_A
PRINT HOLD_A.blah
HOLD_B.blah
END
If you post your code as previously asked by other members here, we might be able to provide a more appropriate approach.
Regards, - Neftali.This message has been edited. Last edited by: njsden,
Darin, that's why they introduced SET ALL=SQL - it's supposed to make the left outer join behave like we expect it to Who knows if it really wants to...
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
JOIN
LEFT_OUTER CSPRDSC_ADM_APPL_PROG.CSPRDSC_ADM_APPL_PROG.EMPLID
AND CSPRDSC_ADM_APPL_PROG.CSPRDSC_ADM_APPL_PROG.ACAD_CAREER
AND CSPRDSC_ADM_APPL_PROG.CSPRDSC_ADM_APPL_PROG.STDNT_CAR_NBR
AND CSPRDSC_ADM_APPL_PROG.CSPRDSC_ADM_APPL_PROG.ADM_APPL_NBR IN
CSPRDSC_ADM_APPL_PROG TO MULTIPLE
CSPRDSC_ADM_APPL_DATA.CSPRDSC_ADM_APPL_DATA.EMPLID
AND CSPRDSC_ADM_APPL_DATA.CSPRDSC_ADM_APPL_DATA.ACAD_CAREER
AND CSPRDSC_ADM_APPL_DATA.CSPRDSC_ADM_APPL_DATA.STDNT_CAR_NBR
AND CSPRDSC_ADM_APPL_DATA.CSPRDSC_ADM_APPL_DATA.ADM_APPL_NBR
IN CSPRDSC_ADM_APPL_DATA TAG J0 AS J0
END
TABLE FILE CSPRDSC_ADM_APPL_PROG
PRINT
'CSPRDSC_ADM_APPL_PROG.CSPRDSC_ADM_APPL_PROG.ACAD_CAREER'
'CSPRDSC_ADM_APPL_PROG.CSPRDSC_ADM_APPL_PROG.STDNT_CAR_NBR'
'CSPRDSC_ADM_APPL_PROG.CSPRDSC_ADM_APPL_PROG.ADM_APPL_NBR'
'CSPRDSC_ADM_APPL_PROG.CSPRDSC_ADM_APPL_PROG.APPL_PROG_NBR'
'CSPRDSC_ADM_APPL_PROG.CSPRDSC_ADM_APPL_PROG.EFFDT' AS 'PROG_EFF_DATE'
'CSPRDSC_ADM_APPL_PROG.CSPRDSC_ADM_APPL_PROG.EFFSEQ' AS 'PROG_EFF_SEQ'
'CSPRDSC_ADM_APPL_PROG.CSPRDSC_ADM_APPL_PROG.ACAD_PROG'
'CSPRDSC_ADM_APPL_PROG.CSPRDSC_ADM_APPL_PROG.PROG_STATUS'
'CSPRDSC_ADM_APPL_PROG.CSPRDSC_ADM_APPL_PROG.PROG_ACTION'
'CSPRDSC_ADM_APPL_PROG.CSPRDSC_ADM_APPL_PROG.ACTION_DT'
'CSPRDSC_ADM_APPL_PROG.CSPRDSC_ADM_APPL_PROG.ADMIT_TERM'
'CSPRDSC_ADM_APPL_PROG.CSPRDSC_ADM_APPL_PROG.CAMPUS'
'J0.CSPRDSC_ADM_APPL_DATA.ADMIT_TYPE'
'J0.CSPRDSC_ADM_APPL_DATA.ADM_CREATION_DT'
'J0.CSPRDSC_ADM_APPL_DATA.ADM_CREATION_BY'
'J0.CSPRDSC_ADM_APPL_DATA.ADM_APPL_DT'
BY 'CSPRDSC_ADM_APPL_PROG.CSPRDSC_ADM_APPL_PROG.EMPLID'
BY HIGHEST 1 'CSPRDSC_ADM_APPL_PROG.CSPRDSC_ADM_APPL_PROG.EFFDT' NOPRINT
AS 'PROG_EFF_DATE'
BY HIGHEST 1 'CSPRDSC_ADM_APPL_PROG.CSPRDSC_ADM_APPL_PROG.EFFSEQ' NOPRINT
AS 'PROG_EFF_SEQ'
BY HIGHEST 1 'CSPRDSC_ADM_APPL_PROG.CSPRDSC_ADM_APPL_PROG.ADM_APPL_NBR' NOPRINT
AS 'PROG_APP_NBR'
HEADING
""
FOOTING
""
WHERE CSPRDSC_ADM_APPL_PROG.CSPRDSC_ADM_APPL_PROG.INSTITUTION EQ 'CSC01';
WHERE CSPRDSC_ADM_APPL_PROG.CSPRDSC_ADM_APPL_PROG.ADMIT_TERM EQ &ADMIT_TERM.(OR(FIND ADMIT_TERM,CSPRDSC_ADM_APPL_PROG.CSPRDSC_ADM_APPL_PROG.ADMIT_TERM IN csprdsc_adm_appl_prog)).Select Admit Term EQ.;
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE HOLD AS CSCAPPS FORMAT FOCUS INDEX 'CSPRDSC_ADM_APPL_PROG.CSPRDSC_ADM_APPL_PROG.EMPLID'
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
END
-*Add in plan data to the prog in above file.
JOIN
LEFT_OUTER CSCAPPS.SEG01.EMPLID AND CSCAPPS.SEG01.ACAD_CAREER
AND CSCAPPS.SEG01.STDNT_CAR_NBR AND CSCAPPS.SEG01.ADM_APPL_NBR
AND CSCAPPS.SEG01.APPL_PROG_NBR IN CSCAPPS TO MULTIPLE
CSPRDSC_ADM_APPL_PLAN.CSPRDSC_ADM_APPL_PLAN.EMPLID
AND CSPRDSC_ADM_APPL_PLAN.CSPRDSC_ADM_APPL_PLAN.ACAD_CAREER
AND CSPRDSC_ADM_APPL_PLAN.CSPRDSC_ADM_APPL_PLAN.STDNT_CAR_NBR
AND CSPRDSC_ADM_APPL_PLAN.CSPRDSC_ADM_APPL_PLAN.ADM_APPL_NBR
AND CSPRDSC_ADM_APPL_PLAN.CSPRDSC_ADM_APPL_PLAN.APPL_PROG_NBR
IN CSPRDSC_ADM_APPL_PLAN TAG J6 AS J6
END
TABLE FILE CSCAPPS
PRINT
'CSCAPPS.SEG01.ACAD_CAREER'
'CSCAPPS.SEG01.APPL_PROG_NBR'
'CSCAPPS.SEG01.ADMIT_TERM'
'CSCAPPS.SEG01.CAMPUS'
'CSCAPPS.SEG01.ADMIT_TYPE'
'CSCAPPS.SEG01.ADM_CREATION_DT'
'CSCAPPS.SEG01.ADM_CREATION_BY'
'CSCAPPS.SEG01.ADM_APPL_DT'
'CSCAPPS.SEG01.ACAD_PROG'
'CSCAPPS.SEG01.PROG_EFF_DATE'
'CSCAPPS.SEG01.PROG_EFF_SEQ'
'CSCAPPS.SEG01.PROG_STATUS'
'CSCAPPS.SEG01.PROG_ACTION'
'CSCAPPS.SEG01.ACTION_DT'
'J6.CSPRDSC_ADM_APPL_PLAN.ACAD_PLAN'
'J6.CSPRDSC_ADM_APPL_PLAN.EFFDT'
'J6.CSPRDSC_ADM_APPL_PLAN.EFFSEQ'
'J6.CSPRDSC_ADM_APPL_PLAN.PLAN_SEQUENCE'
'J6.CSPRDSC_ADM_APPL_PLAN.DECLARE_DT'
BY 'CSCAPPS.SEG01.EMPLID'
BY HIGHEST 1 'J6.CSPRDSC_ADM_APPL_PLAN.EFFDT' NOPRINT
BY HIGHEST 1 'J6.CSPRDSC_ADM_APPL_PLAN.EFFSEQ' NOPRINT
HEADING
""
FOOTING
""
ON TABLE SET PAGE-NUM OFF
ON TABLE SET BYDISPLAY ON
ON TABLE NOTOTAL
ON TABLE HOLD AS CSCAPPS1 FORMAT FOCUS INDEX 'CSCAPPS.SEG01.EMPLID'
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
WebFOCUS 7.6.11 Windows all output (Excel, HTML, PDF)
Malinda, you may try adjusting your code as per alternative #3 in my previous post to see if it helps.
Which database are you running this code against? If it's Oracle you must expect 1,000 or less students in the first HOLD file or the approach won't work as there is a limit of 1,000 items in an
New feature in v7.7. They seemed to have renamed the set command.
:If you want WebFOCUS to assign null values to the fields in a missing segment instance when a left outer join is in effect, you can issue the command SET SHORTPATH=SQL."
quote:
How to Test For Null Values In a Left Outer Join SET SHORTPATH = {FOCUS|SQL} where: FOCUS Omits a host segment from the report output when it has no corresponding crossreferenced segment and the report has a screening condition on the cross-referenced segment. SQL Supplies missing values for the fields in a missing cross-referenced segment in an outer join. Applies screening conditions against this record and retains the record on the report output if it passes the screening test. Note: There must be an outer join in effect, either as a result of the SET ALL=ON command or a JOIN LEFT_OUTER command (either inside or outside of the Master File).
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
Hi guys....so I tried the match and the problem that I have with that is that it now mixing up all of my records and putting some plans with people that they don't belong to. Any other ideas.....would it work if I put the prog into a hold file and the plans into a hold file and then did the left outer join?
WebFOCUS 7.6.11 Windows all output (Excel, HTML, PDF)
From what I can tell the problem could be that you are referencing a field in the second file (BY HIGHEST 1 'J6.CSPRDSC_ADM_APPL_PLAN.EFFDT' NOPRINT BY HIGHEST 1 'J6.CSPRDSC_ADM_APPL_PLAN.EFFSEQ' NOPRINT) and they don't exist for those 20 students. Try running it without the references to the joined file.
WF 8.2.01M 8.2.01M Reporting Server Windows 2012 Srvr R2 PDF,Excel, HTML Graphs - a lot of graphs
Posts: 60 | Location: Atlanta, GA | Registered: October 30, 2003
Malinda, another possible solution is to use SQL passthru instead of WebFOCUS code.
This is what I've done in the past when encountering the problem.
Add SQL Trace commands to view the SQL generated by WebFOCUS. Comment out the WebFOCUS code. Add the generated SQL code as a SQL Passthru request, tweaking the SQL for the left outer joins (if necessary).
SQL DB2
SELECT
T2.COMM_CUST_KEY,
T2.APPL_DIM_KEY,
MIN(T3.APPL_PURP_ENG_DS) AS APPL_PURP_ENG_DS,
MIN(T1.CCH_ACTIVITY_DT) AS CCH_ACTIVITY_DT
FROM BSLC.COMM_CUST_CODED_CRED_HIST T1
INNER JOIN BSLC.COMM_CUST_D T2 ON T2.COMM_CUST_KEY = T1.COMM_CUST_KEY
LEFT OUTER JOIN BSLC.IB_APPL_PURP_C T3 ON T3.APPL_PURP_CD = T2.APPL_PURP_CD
WHERE
(T1.CCH_ACTIVITY_DT BETWEEN '&PRD_FR_DT' AND '&PRD_TO_DT') AND
(T1.ACTIVITY_CD = 'BC') AND
((T2.SAMU_RESP_CODE IS NULL) OR (T2.SAMU_RESP_CODE = ' ')) AND
(T2.CUST_END_DT >= '&PRD_TO_DT') AND (T2.CUST_ST_DT <= '&PRD_FR_DT') AND
(T2.SRC_BR_TRNST_NUM <> 3964)
GROUP BY
T2.COMM_CUST_KEY,T2.APPL_DIM_KEY
ORDER BY
T2.COMM_CUST_KEY,T2.APPL_DIM_KEY
FOR FETCH ONLY;
TABLE ON TABLE HOLD AS R031H002
END
-RUN
-IF &FOCERRNUM NE 0 THEN GOTO FOC_ERROR;
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
I guess Malinda had discarded that option as it had been already presented to her as a possible alternative.
quote:
2. Another one would be to write the entire statement in SQL and run it through SQL passthru. That would give you full control and most likely better performance but maintenance might be difficult for non-SQL developers.
I think the bottom line for Malinda to consider is that there are definitely many ways to obtain what she wants but trying to achieve it entirely in WebFOCUS may not be feasible or "performant". Having an Oracle database really facilitates things. If SQL passthru is not an option then perhaps resolving the complex join inside of a view which can be exposed as a masterfile can be another option.
Thanks for the options guys.....I have absolutely no knowledge of sql at all but may try that. I tried the match and the problem with that is that it isn't linking everything up properly and is mixing up my output. Any hints or good docs to read as I try this sql stuff?
WebFOCUS 7.6.11 Windows all output (Excel, HTML, PDF)
I took out my 2 by highest on my second file and it brought back matching records.....so maybe the alternative if it isn't do a correct left-outer join is to not do any selections in your bys and do that in a later hold file.....
Malinda
WebFOCUS 7.6.11 Windows all output (Excel, HTML, PDF)
First of all it is a really really really bad practice to join a FOCUS file to an Oracle table. In fact, although IBI says you can do it, and you can, it is a really bad practice to join a relational table to any other table than the same type of relational table.
First I would recommend that you check your hold file and make sure the missing students are actually in your initial hold file.
If they are, I would recommend that you change your first hold file to just a flat file. Then pull the data again with the fields you need from the database into a second hold file that is format focus. Then do the join. Since joining a FOCUS file to an Oracle table results in a FOCUS managed join, your performance may actually improve.
You may have to concatenate your join fields in order for the join to work.
TABLEF FILE DATABASE PRINT blah blah blah jblah jblah jblah where blah blah blah on table hold as ONE END DEFINE FILE DATABASE JKEY/A..=jblah|jblah|jblah; END TABLE FILE DATABASE PRINT nblah nblah nblah BY JKEY ON TABLE HOLD AS TWO FORMAT FOCUS INDEX JKEY END SET ALL=ON JOIN J2KEY WITH blah IN ONE TO ALL JKEY IN TWO AS J1 DEFINE FILE ONE J2KEY/A..=jblah|jblah|jblah; END TABLE FILE ONE . . . END
Posts: 17 | Location: Colorado, USA | Registered: January 22, 2010
When you use match the first thing you need to know is on which fields you do want to do the matching. Those fields should be concatenated to on keyfield then you save the intermediate results as focus files.
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
SQL passthru is not an option because the datasource in question is Peoplesoft. Though Oracle is the underlying RDBMS, the WebFOCUS Peoplesoft adapter uses a meta-data security layer to support row-level security. If Malinda went to SQL Passthru, it would defeat the row-level security, which is not an option to the Nebraska university and state college system.
I too have struggled with left outer join and testing for nulls in the result set, and have encountered the same frustrtation that Malinda has. I've tried SET PASS=ALL, etc and still get inconsistent results.
I ended up using MATCH instead of fighting with the LEFT_OUTER since it doesn't operate like a true left outer join.
-- Dan at UNO -- University of Nebraska at Omaha
WebFOCUS 8.8.05M (Prod)/8.0.09(Sandbox) Windows
Posts: 56 | Location: Omaha, Ne USA | Registered: October 15, 2007
JOIN LEFT_OUTER FILE CSTSTNU_PERSONAL_DATA AT EMPLID TO MULTIPLE FILE CSTSTNU_SCC_EMAIL_QVW AT EMPLID AS J1 WHERE (CSTSTNU_PERSONAL_DATA.EMPLID EQ CSPRDNU_SCC_EMAIL_QVW.EMPLID) AND ( CSTSTNU_SCC_EMAIL_QVW.E_ADDR_TYPE EQ 'PERS' ); END
This will pass PERSONAL_DATA across even if an email address of type 'PERS' does not exist.
I really wish WebFOCUS was better in: (1) LEFT OUTER JOIN support and (2) SUBSELECT support. To be honest, I have fought for many years to do simple things (simple in SQL) in the WebFOCUS language. I'm not a newbie to WF, we've been coding it at our institution since 1994. But the frustration to do simple things like left outer joins and simulate a subselect (after nearly 2 decades), leaves a lot to be desired. I know that the core language is very rich and there are many ways to "skin a cat", but we really shouldn't have to fight so hard for such basic stuff.
Dan
WebFOCUS 8.8.05M (Prod)/8.0.09(Sandbox) Windows
Posts: 56 | Location: Omaha, Ne USA | Registered: October 15, 2007
I certainly agree that WebFOCUS does not always translate code into SQL in a manner that one might expect, which is the reason why SQL traces should always be turned on when developing or changing code written for relational databases. However, the databases themselves can be responsible for some problems, and not WebFOCUS. Oracle, for example, treats nulls in ways I have never experienced with SQL Server or DB2.
With Oracle, WHERE clauses that test values in a column where nulls exist can return misleading results when 'not equals' is used as the test condition. WHERE [column name] NE [value] will not return rows where the column has a null value, even though a null value is clearly not equal to the specified value in the WHERE statement. If you want to include null values in the output, you must code: WHERE [column name] NE [value] OR [column name] IS MISSING.
Even more troublesome is the way Oracle treats nulls in cross-column aggregations. For example, if you had an Oracle table with the following values, the result of cross-column summing would produce erroneous output:
The grand total for the above columns should be 141 (15 + 31 + 32 + 63). The way to solve this problem is to sum each column first and then aggregate the column totals. For some reason, Oracle does ignore nulls when summing inter-column values, but not when performing inter-row calculations.
The above problems can be especially difficult to discern when JOINing tables. Oracle treats all values from missing child/foreign rows in the JOINed tables as nulls. So if you encounter missing rows in your WHERE clauaes or inter-row calculations, you may be getting erroneous results. The only general solution I could find to these problems was to not allow null values in the Oracle tables - by setting all missing values to blank or zero - which most DBAs will balk at for good reason.
These issues require special coding techniques in order to avoid returning erroneous results from an Oracle database - techniques that an adhoc user or new developer will probably not be aware of, and regardless whether the query language is SQL, WebFOCUS, or sometihng else.This message has been edited. Last edited by: Dan Satchell,
WebFOCUS 7.7.05
Posts: 1213 | Location: Seattle, Washington - USA | Registered: October 22, 2007