 |
|||||
|
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
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. | |||||
Read-Only TopicGo | Search | Notify | Tools |
| [SOLVED] Pulling class notes | Login/Join |
| Platinum Member |
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) | ||
|
| Expert |
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 | |||
|
| Virtuoso |
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. | |||
|
| Virtuoso |
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. | |||
|
| Platinum Member |
here is what I get from the sql trace. I need help uhderstanding what the various messages mean  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) | |||
|
| Virtuoso |
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 | |||
|
| Expert |
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 | |||
|
| Platinum Member |
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) | |||
|
| Expert |
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 | |||
|
| Virtuoso |
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 | |||
|
| Virtuoso |
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 | |||
|
| Platinum Member |
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) | |||
|
| Platinum Member |
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) | |||
|
| Powered by Social Strata |
 | Please Wait. Your request is being processed... |
Read-Only TopicCopyright © 1996-2020 Information Builders
| View $GS_USERNAME's Public Profile |
| Add $GS_USERNAME to my Ignore List |
| View Recent Posts by $GS_USERNAME |
| Notify me of New Posts by $GS_USERNAME |