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,
Malinda

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

Malinda,

Please psot your code.

SET ALL=SQL is supposed to work...

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.

quote:

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

Here is my code:

 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 * 

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

IN (<list>)

SQL statement in Oracle.

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

There will be more tha 1,000 records in my first hold file and we are running oracle. So this will probably not work.

Francis, I am going to see if I can get upgraded to 7.7 and then try the set shortpath=sql.

If I can't get 7.7 right now, are there any other suggestions?

How about match instead of join?

It is not always my first choise, but is gives sometimes the proper result, and that is what counts..

Or create the hold file as an oracle file and you can do the join at oracle server level.

Frank I have not done anything with match but will research that and try it.

The creating the hold file as an oracle file and then joining at the oracle server level.....how do you do this?

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?

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.

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;

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?

mrguru -

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

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

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.

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

Another option is to do a conditional join ie

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

Dan,

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:

Column1  Column2  Column3  Totals
-------  -------  -------  ------
   10        5        0       15
   20       10        1       31
   30    (null)       2    (null)
   40       20        3       63
                           ------
Total                        109

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,