All,

I have a strange situation going on in which I'm trying to understand why WebFocus is not pulling all records during a join.

My situation includes 3 tables:
NW_S_TERM_CRS_ENROLL - this table has 1 record for each course each student is taking
SSRATTR - this table has an attribute for each course
NW_V_ATTR - this table has additional details for each attribute code

When I create the following report in Microsoft Access, everything works correctly:

 SELECT NWDWHOUSE_NW_S_TERM_CRS_ENROLL.TERM, NWDWHOUSE_NW_S_TERM_CRS_ENROLL.T_CYCLE, NWDWHOUSE_NW_V_ATTR.ATTR_DESC, Sum(NWDWHOUSE_NW_S_TERM_CRS_ENROLL.S_CR_HRS) AS SumOfS_CR_HRS
FROM (NWDWHOUSE_NW_S_TERM_CRS_ENROLL INNER JOIN PUBLIC_SSRATTR ON (NWDWHOUSE_NW_S_TERM_CRS_ENROLL.CRS_CRN = PUBLIC_SSRATTR.SSRATTR_CRN) AND (NWDWHOUSE_NW_S_TERM_CRS_ENROLL.TERM = PUBLIC_SSRATTR.SSRATTR_TERM_CODE)) INNER JOIN NWDWHOUSE_NW_V_ATTR ON PUBLIC_SSRATTR.SSRATTR_ATTR_CODE = NWDWHOUSE_NW_V_ATTR.ATTR_CODE
WHERE (((NWDWHOUSE_NW_V_ATTR.FINANCE_FEE_REPORT)="Y"))
GROUP BY NWDWHOUSE_NW_S_TERM_CRS_ENROLL.TERM, NWDWHOUSE_NW_S_TERM_CRS_ENROLL.T_CYCLE, NWDWHOUSE_NW_V_ATTR.ATTR_DESC
HAVING (((NWDWHOUSE_NW_S_TERM_CRS_ENROLL.TERM)="201510") AND ((NWDWHOUSE_NW_S_TERM_CRS_ENROLL.T_CYCLE)="CEN")); 

But when I create the following report in WebFocus I'm missing data:

  JOIN
INNER NW_S_TERM_CRS_ENROLL.NW_S_TERM_CRS_ENROLL.TERM
AND NW_S_TERM_CRS_ENROLL.NW_S_TERM_CRS_ENROLL.CRS_CRN IN nw_s_term_crs_enroll
TO MULTIPLE SSRATTR.SSRATTR.SSRATTR_TERM_CODE AND SSRATTR.SSRATTR.SSRATTR_CRN
IN ssrattr TAG J5 AS J5
END
JOIN
 INNER FILE nw_s_term_crs_enroll AT J5.SSRATTR.SSRATTR_ATTR_CODE TO MULTIPLE
 FILE nw_v_attr AT NW_V_ATTR.NW_V_ATTR.ATTR_CODE TAG J6 AS J6
 WHERE J5.SSRATTR.SSRATTR_ATTR_CODE EQ J6.NW_V_ATTR.ATTR_CODE;
 END
TABLE FILE NW_S_TERM_CRS_ENROLL
SUM 
     NW_S_TERM_CRS_ENROLL.NW_S_TERM_CRS_ENROLL.S_CR_HRS
BY  NW_S_TERM_CRS_ENROLL.NW_S_TERM_CRS_ENROLL.TERM
BY  NW_S_TERM_CRS_ENROLL.NW_S_TERM_CRS_ENROLL.T_CYCLE
BY  J6.NW_V_ATTR.ATTR_DESC
WHERE ( NW_S_TERM_CRS_ENROLL.NW_S_TERM_CRS_ENROLL.TERM EQ '201510' ) AND ( NW_S_TERM_CRS_ENROLL.NW_S_TERM_CRS_ENROLL.T_CYCLE EQ 'CEN' ) AND ( J6.NW_V_ATTR.FINANCE_FEE_REPORT EQ 'Y' );
ON TABLE SET PAGE-NUM NOLEAD 
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/endeflt.sty,
$
ENDSTYLE
END

ACCESS results:

TERM	T_CYCLE	ATTR_DESC	                SumOfS_CR_HRS
201510	CEN	Command College	                244
201510	CEN	Ed Outreach Standard	        21
201510	CEN	HS Dual Enroll-Arts/Sci (Web)	42
201510	CEN	HS Dual Enroll-Bus/Prof (Web)	3
201510	CEN	HS Dual Enrollment-Arts/Sci	1162
201510	CEN	HS Dual Enrollment-Bus/Prof	96
201510	CEN	HS Dual Enrollment-Ed/Hum Serv	42
201510	CEN	Study Abroad	                15
  

WebFocus results:

 
TERM    T_CYCLE    ATTR_DESC                     S_CR_HRS
201510  CEN        HS Dual Enroll-Arts/Sci (Web) 42
                   HS Dual Enroll-Bus/Prof (Web) 3
 

Both processes are using the exact same data sources.

This message has been edited. Last edited by: <Kathryn Henning>,

1. I would avoid mixing the two styles of JOIN syntax ("AT" and "IN").
2. INNER with MULTIPLE is an unusual combo -- if there are zero rows on the right, the row on the left I excluded.
3. Let's see the generated SQL.

I've changed all the join syntax to IN. It did not change anything. The AT was a left over from my other attempts at figuring out what was going on.

The INNER with MULTIPLE is okay with me, as I do not want the records on the left that do not match the criteria on the right.

Below is the generated SQL:

14.03.45 BT  (FOC2510) FOCUS-MANAGED JOIN SELECTED FOR FOLLOWING REASON(S):
14.03.45 BT  (FOC2519) THE JOINED SEGMENTS RESIDE IN MORE THAN ONE NODE OR SUBS
14.03.45 BT  (FOC2590) AGGREGATION NOT DONE FOR THE FOLLOWING REASON:
14.03.45 BT  (FOC2592) RDBMS-MANAGED JOIN HAS BEEN DISABLED
14.03.45 AE    SELECT
14.03.45 AE   T1."TERM",
14.03.45 AE   T1."T_CYCLE",
14.03.45 AE   T1."CRS_CRN",
14.03.45 AE   T1."S_CR_HRS"
14.03.45 AE    FROM
14.03.45 AE   NWDWHOUSE.NW_S_TERM_CRS_ENROLL T1
14.03.45 AE    WHERE
14.03.45 AE   (T1."T_CYCLE" = 'CEN') AND
14.03.45 AE   (T1."TERM" = '201510');
14.03.45 AE    SELECT
14.03.45 AE   T2."SSRATTR_ATTR_CODE"
14.03.45 AE    FROM
14.03.45 AE   SATURN.SSRATTR T2
14.03.45 AE    WHERE
14.03.45 AE   (T2."SSRATTR_TERM_CODE" = :0001) AND
14.03.45 AE   (T2."SSRATTR_CRN" = :0002);
14.03.45 AE    SELECT
14.03.45 AE   T3."ATTR_DESC",
14.03.45 AE   T3."FINANCE_FEE_REPORT"
14.03.45 AE    FROM
14.03.45 AE   NWDWHOUSE.NW_V_ATTR T3
14.03.45 AE    WHERE
14.03.45 AE   (T3."ATTR_CODE" = :0001) AND
14.03.45 AE   (T3."FINANCE_FEE_REPORT" = 'Y');
0 NUMBER OF RECORDS IN TABLE=       15  LINES=      2
0 HOLDING HTML FILE ON PC DISK ...  

In Access you have HAVING. Why?

I suggest you inspect the underlying detail data:

Take out the 'Y' test, change SUM to PRINT, and include all referenced columns (FINANCE_FEE_REPORT)

Access does the HAVING automatic when you put a where on a group by column.

I took out the Y test, and changed all fields to PRINT and included the FINANCE_FEE_REPORT field.

The underlying data still only shows 15 records with a Y and 13730 without a Y. Access shows far more records with a Y if I do the same there.

Egon, the reason you don't get all the records is that you are generating multiple select statements instead of 1 like you run with ACCESS. FOCUS is managing the JOIN, so that means by default, you will only get 1 child record per parent and if any record path has no child, the record path is excluded.

If you can change your code so that you get 1 select statement generated by WebFOCUS you should get the proper results.

See if this works

JOIN NW_S_TERM_CRS_ENROLL.NW_S_TERM_CRS_ENROLL.TERM AND 
     NW_S_TERM_CRS_ENROLL.NW_S_TERM_CRS_ENROLL.CRS_CRN IN nw_s_term_crs_enroll TO
     SSRATTR.SSRATTR.SSRATTR_TERM_CODE AND
     SSRATTR.SSRATTR.SSRATTR_CRN IN ssrattr TAG J5 AS J5
END
JOIN J5.SSRATTR.SSRATTR_ATTR_CODE IN nw_s_term_crs_enroll TO
     NW_V_ATTR.NW_V_ATTR.ATTR_CODE IN nw_v_attr AT  TAG J6 AS J6
END
-*
TABLE FILE NW_S_TERM_CRS_ENROLL
  SUM NW_S_TERM_CRS_ENROLL.NW_S_TERM_CRS_ENROLL.S_CR_HRS
   BY  NW_S_TERM_CRS_ENROLL.NW_S_TERM_CRS_ENROLL.TERM
   BY  NW_S_TERM_CRS_ENROLL.NW_S_TERM_CRS_ENROLL.T_CYCLE
   BY  J6.NW_V_ATTR.ATTR_DESC
WHERE ( NW_S_TERM_CRS_ENROLL.NW_S_TERM_CRS_ENROLL.TERM EQ '201510' )
  AND ( NW_S_TERM_CRS_ENROLL.NW_S_TERM_CRS_ENROLL.T_CYCLE EQ 'CEN' )
  AND ( J6.NW_V_ATTR.FINANCE_FEE_REPORT EQ 'Y' )
  AND (J5.SSRATTR.SSRATTR_ATTR_CODE EQ J6.NW_V_ATTR.ATTR_CODE)
   ON TABLE SET PAGE-NUM NOLEAD 
   ON TABLE NOTOTAL
   ON TABLE PCHOLD FORMAT HTML
   ON TABLE SET HTMLCSS ON
   ON TABLE SET STYLE *
     INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/endeflt.sty,$
 ENDSTYLE
 END

In general, if you are dealing with tables in a single database, if possible, you should structure your code to generate a single select statement. If WebFOCUS can't, then you always have the option to use your own SQL.

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

jgelona,

That did not fix the situation. I've tried to simply things a lot to find my issue and here's what I've ended up with.

I've taken my SSRATTR table and joined it to my NW_V_ATTR table. The SSRATTR has one record for each course. The NW_V_ATTR table has every possible ATTR_CODE listed once.

When I run a report on just SSRATTR I get all my codes. When I run a report on NW_V_ATTR I get all my codes. When I join the two I get zero records...

Please note that these tables are both in Oracle, but not in the same database.

Both fields are VARCHAR2 (4 char) in Oracle and set to the following in WebFocus:
Actual: A4
Usage: A4

Any thoughts on what is happening here?

JOIN
LEFT_OUTER SSRATTR.SSRATTR.SSRATTR_TERM_CODE IN ssrattr TO MULTIPLE
NW_V_ATTR.NW_V_ATTR.ATTR_CODE IN nw_v_attr TAG J0 AS J0
END
TABLE FILE SSRATTR
BY LOWEST SSRATTR.SSRATTR.SSRATTR_ATTR_CODE
BY LOWEST J0.NW_V_ATTR.ATTR_CODE
WHERE SSRATTR.SSRATTR.SSRATTR_TERM_CODE EQ '201510';
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/endeflt.sty,
$
ENDSTYLE
END

If the data-tables are on different database server connections, WF cannot (and Access presumably does not) submit this as a single Select. You may as well use MATCH FILE, which gives you complete control of the process.

I am also working with BANNER and I am unable to join banner tables (BANSTAN) like SSRATTR to ODS views like NW_V_ATTR.

You may want to see if you can find the field you need in SSRATTR somewhere in an ODS view.

You may want to use a match like j.gross suggested.

Somehow this should be possible though, it's not all that complicated.

Here's what I've done next, I wrote all my values to hold files and had WebFocus do a join between just two hold files.

It's only pulling codes that are 4 characters long, it drops all those off that are less than 4 characters long.

Somehow I got it to work through an elaborate way, code is below.

 SET TRACEOFF=ALL
SET TRACEON=SQLAGGR//CLIENT
SET TRACEON=STMTRACE//CLIENT
SET TRACEON=STMTRACE/2/CLIENT
SET TRACEUSER=ON
TABLE FILE NW_V_ATTR
PRINT
     NW_V_ATTR.NW_V_ATTR.ATTR_CODE
     NW_V_ATTR.NW_V_ATTR.ATTR_DESC
     NW_V_ATTR.NW_V_ATTR.ATTR_OFF_CAMPUS
     NW_V_ATTR.NW_V_ATTR.GENED
     NW_V_ATTR.NW_V_ATTR.INSTREQ
     NW_V_ATTR.NW_V_ATTR.ACTY
     NW_V_ATTR.NW_V_ATTR.DEV
     NW_V_ATTR.NW_V_ATTR.WEB
     NW_V_ATTR.NW_V_ATTR.DIST
     NW_V_ATTR.NW_V_ATTR.KC
     NW_V_ATTR.NW_V_ATTR.ST
     NW_V_ATTR.NW_V_ATTR.MSSU
     NW_V_ATTR.NW_V_ATTR.MU
     NW_V_ATTR.NW_V_ATTR.NCMC
     NW_V_ATTR.NW_V_ATTR.DUAL
     NW_V_ATTR.NW_V_ATTR.FINANCE_FEE_REPORT
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE HOLD AS TMP_ATTRVAL
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/endeflt.sty,
$
ENDSTYLE
END
JOIN
INNER NW_S_TERM_CRS_ENROLL.NW_S_TERM_CRS_ENROLL.TERM
AND NW_S_TERM_CRS_ENROLL.NW_S_TERM_CRS_ENROLL.CRS_CRN IN nw_s_term_crs_enroll
TO MULTIPLE SSRATTR.SSRATTR.SSRATTR_TERM_CODE AND SSRATTR.SSRATTR.SSRATTR_CRN
IN ssrattr TAG J5 AS J5
END
TABLE FILE NW_S_TERM_CRS_ENROLL
SUM
     NW_S_TERM_CRS_ENROLL.NW_S_TERM_CRS_ENROLL.S_CR_HRS/P18 AS 'SCH'
BY  NW_S_TERM_CRS_ENROLL.NW_S_TERM_CRS_ENROLL.TERM AS 'Term code'
BY  NW_S_TERM_CRS_ENROLL.NW_S_TERM_CRS_ENROLL.T_CYCLE AS 'Cycle'
BY  J5.SSRATTR.SSRATTR_ATTR_CODE AS 'Attribute Code'
WHERE ( NW_S_TERM_CRS_ENROLL.NW_S_TERM_CRS_ENROLL.TERM EQ '&TERM.Please enter the term code:.' ) AND ( NW_S_TERM_CRS_ENROLL.NW_S_TERM_CRS_ENROLL.T_CYCLE EQ '&T_CYCLE.Please enter CEN or EOT:.' );
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE HOLD AS tmp_SCH
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     INCLUDE = IBFS:/EDA/EDASERVE/BASEAPP/NW_IR.sty,
$
ENDSTYLE
END
JOIN CLEAR *
JOIN
TMP_SCH.TMP_SCH.SSRATTR_ATTR_CODE IN tmp_SCH TO MULTIPLE
TMP_ATTRVAL.TMP_ATTR.ATTR_CODE IN TMP_ATTRVAL TAG J0 AS J0
END
TABLE FILE TMP_SCH
SUM
     TMP_SCH.TMP_SCH.S_CR_HRS AS 'Student Credit Hours'
BY  TMP_SCH.TMP_SCH.TERM AS 'Term Code'
BY  TMP_SCH.TMP_SCH.T_CYCLE AS 'Cycle'
BY  TMP_SCH.TMP_SCH.SSRATTR_ATTR_CODE AS 'Attribute Code'
BY  J0.TMP_ATTR.ATTR_DESC AS 'Attribute Description'
WHERE J0.TMP_ATTR.FINANCE_FEE_REPORT EQ 'Y';
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     INCLUDE = IBFS:/EDA/EDASERVE/BASEAPP/NW_IR.sty,
$
ENDSTYLE
END
 

quote:

Please note that these tables are both in Oracle, but not in the same database.

Egon, as I stated, WebFOCUS will generate a single select state only when the tables are in the same database and you are using the same WebFOCUS connection defined on the Reporting Server. For example, we have 2 connections set up to our database. One allows read/write the other is read only. (You can find this in the .acx files associated with your .mas files.) If I JOIN 2 tables and one uses the read only connection and the other uses the read/write connection, WebFOCUS will not generate a single select. The same thing applies to tables in different databases.

As for what you got to work, I'd advise using MATCH instead. Another option is to use HOLD format FOCUS for TMP_ATTRVAL like this:

TABLE FILE NW_V_ATTR
PRINT
     NW_V_ATTR.NW_V_ATTR.ATTR_DESC
     NW_V_ATTR.NW_V_ATTR.ATTR_OFF_CAMPUS
.....
BY NW_V_ATTR.NW_V_ATTR.ATTR_CODE
ON TABLE HOLD AS TMP_ATTRVAL FORMAT FOCUS INDEX ATTR_CODE
END

I never use JOIN flat files. If ATTR_CODE is ever out of sequence, your report will return incorrect results.