Focal Point Banner


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.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Pulling class notes

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] Pulling class notes
 Login/Join
 
Platinum Member
posted
Our campus is using oracle/peoplesoft tables. For our online environment, we are flagging specific courses on peoplesoft to indicate that a shell needs to be created in the online CLE. We are using class notes to do this. We are using a class note number to indicate this flag. I am able to pull that okay but I run into a problem when I want to pull the text that could be in the free format text area. We are using this to help combine courses. I am creating the CLE site id based on the term and course call number. Some instructors like to combine several of their sections into 1 class online. If this is the case we are leaving the course note number blank and indicating a sequence number of 20. If the sequence number 20 exists then we use the number that is in the free format text instead of the regular course call number. This, I am not able to get to work, it always pulls the regular call number. Below is my code, and help would be much appreciated!

JOIN
 LEFT_OUTER CSQASC_CLASS_TBL.CSQASC_CLASS_TBL.STRM IN CSQASC_CLASS_TBL
 TO MULTIPLE CSQASC_TERM_VAL_TBL.CSQASC_TERM_VAL_TBL.STRM IN CSQASC_TERM_VAL_TBL
 TAG J0 AS J0
 END
JOIN
 LEFT_OUTER CSQASC_CLASS_TBL.CSQASC_CLASS_TBL.CRSE_ID
 AND CSQASC_CLASS_TBL.CSQASC_CLASS_TBL.CRSE_OFFER_NBR
 AND CSQASC_CLASS_TBL.CSQASC_CLASS_TBL.STRM
 AND CSQASC_CLASS_TBL.CSQASC_CLASS_TBL.SESSION_CODE
 AND CSQASC_CLASS_TBL.CSQASC_CLASS_TBL.CLASS_SECTION IN CSQASC_CLASS_TBL
 TO MULTIPLE CSQASC_CLASS_NOTES.CSQASC_CLASS_NOTES.CRSE_ID
 AND CSQASC_CLASS_NOTES.CSQASC_CLASS_NOTES.CRSE_OFFER_NBR
 AND CSQASC_CLASS_NOTES.CSQASC_CLASS_NOTES.STRM
 AND CSQASC_CLASS_NOTES.CSQASC_CLASS_NOTES.SESSION_CODE
 AND CSQASC_CLASS_NOTES.CSQASC_CLASS_NOTES.CLASS_SECTION IN CSQASC_CLASS_NOTES
 TAG J4 AS J4
 END
JOIN
 LEFT_OUTER J4.CSQASC_CLASS_NOTES.CLASS_NOTE_NBR IN CSQASC_CLASS_TBL TO MULTIPLE
 CSQASC_CLASS_NOTES_TBL.CSQASC_CLASS_NOTES_TBL.CLASS_NOTE_NBR
 IN CSQASC_CLASS_NOTES_TBL TAG J5 AS J5
 END
DEFINE FILE CSQASC_CLASS_TBL
CALL_NO_SEQ/A5=EDIT (J4.CSQASC_CLASS_NOTES.DESCRLONG, '99999$*');
CALL_NO_CLASS/A5=EDIT (CSQASC_CLASS_TBL.CSQASC_CLASS_TBL.CLASS_NBR, '99999');
CALL_NO/A5=IF ( J4.CSQASC_CLASS_NOTES.CLASS_NOTE_NBR EQ ' ' AND J4.CSQASC_CLASS_NOTES.CLASS_NOTES_SEQ EQ 20) THEN CALL_NO_SEQ ELSE CALL_NO_CLASS;
SITE_ID/A11=CSQASC_CLASS_TBL.CSQASC_CLASS_TBL.STRM||'.'||CALL_NO;
SESSION/A11=
IF CSQASC_CLASS_TBL.CSQASC_CLASS_TBL.SESSION_CODE EQ '8W1' THEN '1st 8 Weeks' ELSE
IF CSQASC_CLASS_TBL.CSQASC_CLASS_TBL.SESSION_CODE EQ '8W2' THEN '2nd 8 Weeks' ELSE
IF CSQASC_CLASS_TBL.CSQASC_CLASS_TBL.SESSION_CODE EQ '5W1' THEN '1st 5 Weeks' ELSE
IF CSQASC_CLASS_TBL.CSQASC_CLASS_TBL.SESSION_CODE EQ '5W2' THEN '2nd 5 Weeks' ELSE
IF CSQASC_CLASS_TBL.CSQASC_CLASS_TBL.SESSION_CODE EQ '5W3' THEN '3rd 5 Weeks' ELSE
IF CSQASC_CLASS_TBL.CSQASC_CLASS_TBL.SESSION_CODE EQ '4W1' THEN '1st 4 Weeks' ELSE
IF CSQASC_CLASS_TBL.CSQASC_CLASS_TBL.SESSION_CODE EQ '4W2' THEN '2nd 4 Weeks' ELSE
IF CSQASC_CLASS_TBL.CSQASC_CLASS_TBL.SESSION_CODE EQ '4W3' THEN '3rd 4 Weeks' ELSE
IF CSQASC_CLASS_TBL.CSQASC_CLASS_TBL.SESSION_CODE EQ '2W1' THEN '1st 2 Weeks' ELSE
IF CSQASC_CLASS_TBL.CSQASC_CLASS_TBL.SESSION_CODE EQ '2W2' THEN '2nd 2 Weeks' ELSE
IF CSQASC_CLASS_TBL.CSQASC_CLASS_TBL.SESSION_CODE EQ '2W3' THEN '3rd 2 Weeks' ELSE
IF CSQASC_CLASS_TBL.CSQASC_CLASS_TBL.SESSION_CODE EQ '2W4' THEN '4th 2 Weeks' ELSE
IF CSQASC_CLASS_TBL.CSQASC_CLASS_TBL.SESSION_CODE EQ '2W5' THEN '5th 2 Weeks' ELSE
IF CSQASC_CLASS_TBL.CSQASC_CLASS_TBL.SESSION_CODE EQ '2W6' THEN '6th 2 Weeks' ELSE ' ';
COURSE_ID/A27=CSQASC_CLASS_TBL.CSQASC_CLASS_TBL.SUBJECT | CSQASC_CLASS_TBL.CSQASC_CLASS_TBL.CATALOG_NBR |' '| CSQASC_CLASS_TBL.CSQASC_CLASS_TBL.CLASS_SECTION || '-' || CSQASC_CLASS_TBL.CSQASC_CLASS_TBL.SESSION_CODE;
COURSE_ID_1/A24=CSQASC_CLASS_TBL.CSQASC_CLASS_TBL.SUBJECT | CSQASC_CLASS_TBL.CSQASC_CLASS_TBL.CATALOG_NBR |' '| CSQASC_CLASS_TBL.CSQASC_CLASS_TBL.CLASS_SECTION;
SEM/A1=EDIT ( CSQASC_CLASS_TBL.CSQASC_CLASS_TBL.STRM , '$$$9');
SECTION_NO/A2=EDIT (CSQASC_CLASS_TBL.CSQASC_CLASS_TBL.CLASS_SECTION, '99$$');
TERM/A50=
IF ((COURSE_ID_1 EQ 'COLG010 01') AND (SEM EQ '8' OR '1')) THEN '2010 Year Long' ELSE
IF (SECTION_NO EQ '81' AND SEM EQ '8') THEN 'Fall 2010 Correspondence' ELSE
IF (SECTION_NO EQ '81' AND SEM EQ '1') THEN 'Spring 2011 Correspondence' ELSE
IF SECTION_NO EQ '80' THEN 'Year Long Correspondence' ELSE J0.CSQASC_TERM_VAL_TBL.DESCR||(' '| SESSION);
TITLE/A60='"'||CSQASC_CLASS_TBL.CSQASC_CLASS_TBL.SUBJECT | CSQASC_CLASS_TBL.CSQASC_CLASS_TBL.CATALOG_NBR |' '| CSQASC_CLASS_TBL.CSQASC_CLASS_TBL.CLASS_SECTION ||'-'|| CSQASC_CLASS_TBL.CSQASC_CLASS_TBL.SESSION_CODE ||'-'|| CSQASC_CLASS_TBL.CSQASC_CLASS_TBL.DESCR||'"';
DESC/A29='"'||CSQASC_CLASS_TBL.CSQASC_CLASS_TBL.SUBJECT | CSQASC_CLASS_TBL.CSQASC_CLASS_TBL.CATALOG_NBR |' '| CSQASC_CLASS_TBL.CSQASC_CLASS_TBL.CLASS_SECTION ||'-'|| CSQASC_CLASS_TBL.CSQASC_CLASS_TBL.SESSION_CODE||'"';
PUBLISH/A1='0';
TEMPLATE/A25=
IF SECTION_NO EQ '79' OR '89' OR '99' THEN 'template.online' ELSE
IF SECTION_NO EQ '80' THEN 'template.corr.yearlong' ELSE
IF SECTION_NO EQ '81' THEN 'template.corr.semester' ELSE 'template.webenhanced';
END
TABLE FILE CSQASC_CLASS_TBL
-* Pull courses based on the class note number 0025 to indicate the course needs created on Sakai
PRINT 
     'J0.CSQASC_TERM_VAL_TBL.TERM'
     'J4.CSQASC_CLASS_NOTES.SITE_ID'
     'CSQASC_CLASS_TBL.CSQASC_CLASS_TBL.START_DT/YY-M-D'
     'CSQASC_CLASS_TBL.CSQASC_CLASS_TBL.TITLE'
     'CSQASC_CLASS_TBL.CSQASC_CLASS_TBL.COURSE_ID'
     'CSQASC_CLASS_TBL.CSQASC_CLASS_TBL.DESCR'
     PUBLISH
     'CSQASC_CLASS_TBL.CSQASC_CLASS_TBL.END_DT/YY-M-D'
     'CSQASC_CLASS_TBL.CSQASC_CLASS_TBL.TEMPLATE'
HEADING
""
FOOTING
""
WHERE CSQASC_CLASS_TBL.CSQASC_CLASS_TBL.INSTITUTION EQ 'CSC01';
WHERE CSQASC_CLASS_TBL.CSQASC_CLASS_TBL.STRM EQ &STRM.(OR(FIND CSQASC_CLASS_TBL.CSQASC_CLASS_TBL.STRM,CSQASC_CLASS_TBL.CSQASC_CLASS_TBL.STRM IN csqasc_class_tbl)).Select term.;
WHERE J5.CSQASC_CLASS_NOTES_TBL.CLASS_NOTE_NBR EQ '0025';
ON TABLE SET PAGE-NUM OFF 
ON TABLE NOTOTAL
ON TABLE HOLD AS COURSES FORMAT FOCUS INDEX 'J4.CSQASC_CLASS_NOTES.SITE_ID' 
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     UNITS=IN,
     SQUEEZE=ON,
     ORIENTATION=PORTRAIT,
$
TYPE=REPORT,
     GRID=OFF,
     FONT='ARIAL',
     SIZE=9,
$
TYPE=TITLE,
     STYLE=BOLD,
$
TYPE=TABHEADING,
     SIZE=12,
     STYLE=BOLD,
$
TYPE=TABFOOTING,
     SIZE=12,
     STYLE=BOLD,
$
TYPE=HEADING,
     SIZE=12,
     STYLE=BOLD,
$
TYPE=FOOTING,
     SIZE=12,
     STYLE=BOLD,
$
TYPE=SUBHEAD,
     SIZE=10,
     STYLE=BOLD,
$
TYPE=SUBFOOT,
     SIZE=10,
     STYLE=BOLD,
$
TYPE=SUBTOTAL,
     BACKCOLOR=RGB(210 210 210),
$
TYPE=ACROSSVALUE,
     SIZE=9,
$
TYPE=ACROSSTITLE,
     STYLE=BOLD,
$
TYPE=GRANDTOTAL,
     BACKCOLOR=RGB(210 210 210),
     STYLE=BOLD,
$
ENDSTYLE
END
TABLE FILE COURSES
PRINT 
     'COURSES.SEG01.TERM'
     'COURSES.SEG01.SITE_ID'
     'COURSES.SEG01.START_DT'
     'COURSES.SEG01.TITLE'
     'COURSES.SEG01.COURSE_ID'
     'COURSES.SEG01.DESCR'
     'COURSES.SEG01.PUBLISH'
     'COURSES.SEG01.END_DT'
     'COURSES.SEG01.TEMPLATE'
BY 'COURSES.SEG01.TERM' NOPRINT
BY 'COURSES.SEG01.SITE_ID' NOPRINT
HEADING
""
FOOTING
""
ON TABLE SET PAGE-NUM OFF 
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *

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


WebFOCUS 7.6.11
Windows
all output (Excel, HTML, PDF)
 
Posts: 149 | Registered: April 15, 2010Report This Post
Expert
posted Hide Post
This too much to swallow in one sitting!

I would start by turning SQL traces on - you will get informational messages about the JOIN and DEFINE statements which should help in improving and correcting the SQL that's generated by WebFOCUS.

-*-- Set up SQL tracing ----------------------------------------------
-*-- Deactivate SQL tracing --------------------------------
SET TRACEOFF = ALL
-*-- Enable Trace for the SQL Translator -------------------
SET TRACEON = SQLTRANS
-*-- Show SQL statements -----------------------------------
SET TRACEON = STMTRACE//CLIENT
-*-- Show Optimization information -------------------------
SET TRACEON = SQLAGGR//CLIENT
-*-- Disable the trace stamp (Date/Time etc) ---------------
SET TRACESTAMP = OFF
-*-- Set trace line wrapping - # of characters -------------
SET TRACEWRAP = 78
-*-- Activate SQL tracing ----------------------------------
SET TRACEUSER = ON
-RUN

-*-- Turn off data retrieval (for testing purposes) ------------------
SET XRETRIEVAL=OFF
-RUN


urning off data retrieval helps while fine-tuning the code.


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
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Virtuoso
posted Hide Post
Malinda, this is one of those cases in which I'd rather create a database view with those crazy rules inside of it and would just create a WF synonym on top and query out of it.

It would not only simplify your report but also will give you more control over the actual SQL statement you want to produce without being constrained by the actual SQL that is generated by the WF adapter for Oracle.

Now, I understand there are certain environment out there that impose limitations on the creation of database objects but that some possibility I'd certainly like to validate.



Prod/Dev: WF Server 8008/Win 2008 - WF Client 8008/Win 2008 - Dev. Studio: 8008/Windows 7 - DBMS: Oracle 11g Rel 2
Test: Dev. Studio 8008 /Windows 7 (Local) Output:HTML, EXL2K.
 
Posts: 1533 | Registered: August 12, 2005Report This Post
Virtuoso
posted Hide Post
And of course, there's always SQL passthru as an additional alternative although it depends on how comfortable the WF developer might feel at writing SQL code. Maintenance may also be a little nightmarish if you expect changes on the underlying database objects in which case I prefer the additional level of abstraction that can be obtained by using views and WF synonyms.

Anyway, it may or may not help in your case but keep it in the back of your head.



Prod/Dev: WF Server 8008/Win 2008 - WF Client 8008/Win 2008 - Dev. Studio: 8008/Windows 7 - DBMS: Oracle 11g Rel 2
Test: Dev. Studio 8008 /Windows 7 (Local) Output:HTML, EXL2K.
 
Posts: 1533 | Registered: August 12, 2005Report This Post
Platinum Member
posted Hide Post
here is what I get from the sql trace. I need help uhderstanding what the various messages mean Smiler

FOC2506 - INTERFACE-MANAGED NATIVE JOIN SELECTED FOR THE FOLLOWING REASON:
FOC2516 - MULTIPLICATIVE EFFECT WOULD ENSUE
FOC2590 - AGGREGATION NOT DONE FOR THE FOLLOWING REASON:
FOC2592 - RDBMS-MANAGED JOIN HAS BEEN DISABLED
SELECT T1."CRSE_ID",T1."CRSE_OFFER_NBR",T1."STRM",
T1."SESSION_CODE",T1."CLASS_SECTION",T1."INSTITUTION",
T1."SUBJECT",T1."CATALOG_NBR",T1."DESCR",T1."CLASS_NBR",
T1."START_DT",T1."END_DT",T2."CRSE_ID",T2."CRSE_OFFER_NBR",
T2."STRM",T2."SESSION_CODE",T2."CLASS_SECTION",
T2."CLASS_NOTES_SEQ",T2."CLASS_NOTE_NBR",T2."DESCRLONG",
T3."INSTITUTION",T3."CLASS_NOTE_NBR",T3."EFFDT",T4."STRM",
T4."DESCR" FROM ( ( ( SYSADM.PS_CLASS_TBL T1 INNER JOIN
SYSADM.PS_CLASS_NOTES T2 ON T2."CRSE_ID" = T1."CRSE_ID" AND
T2."CRSE_OFFER_NBR" = T1."CRSE_OFFER_NBR" AND T2."STRM" =
T1."STRM" AND T2."SESSION_CODE" = T1."SESSION_CODE" AND
T2."CLASS_SECTION" = T1."CLASS_SECTION" ) INNER JOIN
SYSADM.PS_CLASS_NOTES_TBL T3 ON T3."CLASS_NOTE_NBR" =
T2."CLASS_NOTE_NBR" ) LEFT OUTER JOIN SYSADM.PS_TERM_VAL_TBL T4
ON T4."STRM" = T1."STRM" ) WHERE (T1."STRM" = '1108') AND
(T1."INSTITUTION" = 'CSC01') AND (T3."CLASS_NOTE_NBR" = '0025')
ORDER BY T1."CRSE_ID" DESC ,T1."CRSE_OFFER_NBR" DESC ,T1."STRM"
DESC ,T1."SESSION_CODE" DESC ,T1."CLASS_SECTION" DESC ,
T2."CRSE_ID" DESC ,T2."CRSE_OFFER_NBR" DESC ,T2."STRM" DESC ,
T2."SESSION_CODE" DESC ,T2."CLASS_SECTION" DESC ,
T2."CLASS_NOTES_SEQ" DESC ,T3."INSTITUTION" DESC ,
T3."CLASS_NOTE_NBR" DESC ,T3."EFFDT" DESC ,T4."STRM" DESC ;
...RETRIEVAL KILLED
0 NUMBER OF RECORDS IN TABLE= 0 LINES= 0
...RETRIEVAL KILLED
0 NUMBER OF RECORDS IN TABLE= 0 LINES= 0


WebFOCUS 7.6.11
Windows
all output (Excel, HTML, PDF)
 
Posts: 149 | Registered: April 15, 2010Report This Post
Virtuoso
posted Hide Post
quote:

CALL_NO/A5=IF ( J4.CSQASC_CLASS_NOTES.CLASS_NOTE_NBR EQ ' ' AND J4.CSQASC_CLASS_NOTES.CLASS_NOTES_SEQ EQ 20) THEN CALL_NO_SEQ ELSE CALL_NO_CLASS;
.
.
WHERE J5.CSQASC_CLASS_NOTES_TBL.CLASS_NOTE_NBR EQ '0025';


I looks to me like your WHERE clause will prevent your report from ever finding any records where CLASS_NOTE_NBR is blank. Try changing your WHERE clause to:

WHERE J5.CSQASC_CLASS_NOTES_TBL.CLASS_NOTE_NBR EQ '0025' OR ' ';


WebFOCUS 7.7.05
 
Posts: 1213 | Location: Seattle, Washington - USA | Registered: October 22, 2007Report This Post
Expert
posted Hide Post
I don't know if updated documentation exists, but here are two links to documentation that might help (you have to login to Tech Support first):

Information Builders Systems Journal, March-April 1996 - Relational Reporting Efficiencies: Part I

Information Builders Systems Journal, July-August 1996 - Relational Reporting Efficiencies: Part II


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
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Platinum Member
posted Hide Post
My where statement could have been part of it. I did switch that to include the or ' '. I am thinking the problem is somewhere in my defines. I am using the call_no* to indicate that if there is a sequence of 20 out there use that instead of the course call number.

Malinda


WebFOCUS 7.6.11
Windows
all output (Excel, HTML, PDF)
 
Posts: 149 | Registered: April 15, 2010Report This Post
Expert
posted Hide Post
quote:
The multiplicative effect occurs when we attempt to sum at different levels in the join structure and there is a one-to-many relationship between those levels. If we do all summing at the lower level, the multiplicative effect does not occur. In addition, the multiplicative effect can occur if we sum at that high level in the join structure
but sort at the low level. The relationship between the TABLEs is important and keys must be described appropriately.


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
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Virtuoso
posted Hide Post
If CLASS_NOTE_NBR is missing in host table CSQASC_CLASS_TBL but blank in foreign table CSQASC_CLASS_NOTES_TBL, the JOIN will never work.


WebFOCUS 7.7.05
 
Posts: 1213 | Location: Seattle, Washington - USA | Registered: October 22, 2007Report This Post
Virtuoso
posted Hide Post
In fact, I cannot find any reason in your code for the J5 join to table CSQASC_CLASS_NOTES_TBL. I don't see any references in the code to this table except in the join and the WHERE clause. Can you eliminate this join and change the WHERE clause to:

WHERE J4.CSQASC_CLASS_NOTES.CLASS_NOTE_NBR EQ '0025' OR ' ';


WebFOCUS 7.7.05
 
Posts: 1213 | Location: Seattle, Washington - USA | Registered: October 22, 2007Report This Post
Platinum Member
posted Hide Post
I am still working on this. I did take out the extra join. You are right I really didn't need it. I tried doing a hold file for all the class notes 25, then joining that hold file back to my class table, class notes table and term table, then adding the define and where statement to get the seq to combine classes. That still wasn't working. Then I tried putting each in its own hold file and joining those together - it pulls the hold files correctly but when I join them together it will not output the sequence one correctly.

Malinda


WebFOCUS 7.6.11
Windows
all output (Excel, HTML, PDF)
 
Posts: 149 | Registered: April 15, 2010Report This Post
Platinum Member
posted Hide Post
I think I might be getting closer - I redid how I had my hold files indexed and it is listing both now.

Malinda


WebFOCUS 7.6.11
Windows
all output (Excel, HTML, PDF)
 
Posts: 149 | Registered: April 15, 2010Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Pulling class notes

Copyright © 1996-2020 Information Builders