 |
|||||
|
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] Connecting Databases | Login/Join |
| Gold member |
I am trying to connect a sql database to an oracle database to run a combined query. For some reason I am having no luck doing this. Could you please review the below code and inform what I am doing wrong. The query is displaying NO results at all. The problem seems to be coming from the join statement, but the join statement may be incorrect. “//” denotes comments:
-* File District_Org_Chart.fex
-SET &ECHO=ALL;
-*
-*-DEFAULT &DIVISION = '1D' ;
-* DEFAULT &SUB_ASG = 'SHIFT 1' ;
//START OF ORACLE CONNECTION
ENGINE SQLORA SET CONNECTION_ATTRIBUTES BPGAS
SQL SQLORA
select distinct INCIDENT_SUPPLEMENTS.APPROVAL_DATE, INCIDENTS.INC_REPORT_NUMBER,
EMPLOYEES.OFFICER_ID
from BLAH..BLAH..BLAH…
where BLAH..BLAH..BLAH..
//END OF ORACLE CONNECTION, I WOULD LIKE TO STORE THE QUERY IN A TABLE FILE FOR EASY QUERY ACCESS
TABLE FILE SQLOUT
PRINT *
ON TABLE HOLD FORMAT ALPHA
END
-RUN
TABLE FILE HOLD
SUM
BLAH..BLAH..BLAH
BY OFFICER_ID
ON TABLE HOLD AS OFFNSDAT FORMAT FOCUS INDEX OFFICER_ID
-RUN
//THIS JOIN DOES NOT SEEM TO BE WORKING, I AM TRYING TO CONNECT THE PERID( SQL SERVER) TO THE EMPLOYEES. OFFICER_ID (ORACLE)
JOIN PerID IN TELESTAFFROSTER TO EMPLOYEES.OFFICER_ID IN OFFNSDAT AS J1
//THE TELESTAFFROSTER FILE WORKS CORRECTLY
TABLE FILE TELESTAFFROSTER
HEADING CENTER
"&SUB_ASG"
PRINT UPLASTNAME AS ''
//I WOULD LIKE TO PRINT THE ORACLE_ID NUMBER ( OR SOME INFORMATION FROM THE ORACLE TABLE) JUST TO LET ME KNOW THAT I AM CONNECTING TO THE ORACLE DATABASE
ORACLE_ID
LASTNAME
BY RANK_ORDER NOPRINT
BY RANK_ABRV AS ''
WHERE TSDIVISION EQ '&DIVISION';
WHERE SUB_ASSIGNMENT EQ '&SUB_ASG';
WHERE RANK_ABRV NE 'CPT' OR 'LT';
-*ON TABLE SET EMPTYREPORT ON
ON TABLE HOLD FORMAT HTML AS 'X1'
ON TABLE SET PAGE-NUM OFF
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
INCLUDE = overtime_blue.sty,
TOPMARGIN=0.0,
LEFTMARGIN=0.0,
RIGHTMARGIN=0.0,
BOTTOMMARGIN=0.0,
$
TYPE=HEADING,
STYLE=BOLD,
$
TYPE=REPORT,
BACKCOLOR=RGB(153 204 255),
FONT=ARIAL,
$
TYPE=REPORT
COLUMN=3,
WIDTH=1.2,
WRAP=1.2,
SIZE=9,
$
END
-HTMLFORM BEGIN
<html>
<body style="background-color:#99CCFF;")>
<div valign="top" style="width:150px;">
!IBI.FIL.X1;
</div>
</body>
</html>
-HTMLFORM END
This message has been edited. Last edited by: <Kathryn Henning>, WebFOCUS 7.6 Windows, All Outputs | ||
|
| Virtuoso |
Initial guess is that the formats of the fields are different. Can you post the MFDs for TELESTAFFROSTER and OFFNSDAT. Also please put your code in between the code tags, the RED icon on the right in the reply window. Alan. WF 7.705/8.007 | |||
|
| Gold member |
Thank you for the quick response. I have the MFD for Telestaffroster. But not for OFFNSDAT. I thought I could just create the ORacle query like I did, could you please advise on the correct method. SEGNAME=TELESTAFFROSTER, TABLENAME=dbo.TelestaffRoster,
CONNECTION=TELESTAFF_F, KEYS=1, $
FIELD=TBLID, AUTOINCREMENT=YES, START=1, INCREMENT=1, $
INDEX_NAME=PerID, INDEX_UNIQUE=Y, INDEX_COLUMN=PerID, INDEX_ORDER=ASC, $
INDEX_NAME=LastName, INDEX_UNIQUE=N, INDEX_COLUMN=LastName, INDEX_ORDER=ASC, $
INDEX_NAME=TSAssignment, INDEX_UNIQUE=N, INDEX_COLUMN=TSAssignment,
INDEX_ORDER=ASC, $
INDEX_NAME=TSDivision, INDEX_UNIQUE=N, INDEX_COLUMN=TSDivision,
INDEX_ORDER=ASC, $
INDEX_NAME=TSRank, INDEX_UNIQUE=N, INDEX_COLUMN=TSRank, INDEX_ORDER=ASC, $
WebFOCUS 7.6 Windows, All Outputs | |||
|
| Expert |
ENGINE SQLORA SET CONNECTION_ATTRIBUTES BPGAS SQL SQLORA PREPARE SQLOUT FOR select distinct INCIDENT_SUPPLEMENTS.APPROVAL_DATE, INCIDENTS.INC_REPORT_NUMBER, EMPLOYEES.OFFICER_ID from BLAH..BLAH..BLAH… where BLAH..BLAH..BLAH.. ; END TABLE FILE SQLOUT PRINT * ON TABLE HOLD FORMAT ALPHA END -EXIT Run this and see if you get any records... Tom Flynn WebFOCUS 8.1.05 - PROD/QA DB2 - AS400 - Mainframe | |||
|
| Gold member |
Yes, I do get records. It seems to be coming from the Oracle table, since I used the "EXIT" line. WebFOCUS 7.6 Windows, All Outputs | |||
|
| Gold member |
I need to add a statement to only return 10 rows at a time (the query returns alot of rows), how would I go about doing that. It now shows the oracle table, but I still need to join it to the SQL table to complete the query. Thank you. WebFOCUS 7.6 Windows, All Outputs | |||
|
| Gold member |
I just added the rowcount through SQL WHERE ROWNUM <= 10 I still need to connect the 2 databases though. Any help is appreciated. Thank you WebFOCUS 7.6 Windows, All Outputs | |||
|
| Expert |
There no such column as EMPLOYEES.OFFICER_ID Correct syntax is: JOIN PerID IN TELESTAFFROSTER TO OFFICER_ID IN OFFNSDAT AS J1 OR, if it's one-to-many(need to know your data) JOIN PerID IN TELESTAFFROSTER TO ALL OFFICER_ID IN OFFNSDAT AS J1 OR, if it's many-to-many: JOIN LEFT_OUTER PerID IN TELESTAFFROSTER TO ALL OFFICER_ID IN OFFNSDAT AS J1 OR, if the original file is suppose to be the HOST file: JOIN OFFICER_ID IN OFFNSDAT TO PerID IN TELESTAFFROSTER AS J1 -RUN TABLE FILE OFFNSDAT etc... Tom Flynn WebFOCUS 8.1.05 - PROD/QA DB2 - AS400 - Mainframe | |||
|
| Gold member |
When I take away the END and the Exit statement that was told for me to do and run the query after the join I get no results, no tables shown. When I add the Exit and End statements I can get the queries to run independantly. I need to be able to add columns from both the sql and oracle statements to be shown to the screen at the same time. WebFOCUS 7.6 Windows, All Outputs | |||
|
| Expert |
Your JOIN syntax is wrong, see my previous post... Tom Flynn WebFOCUS 8.1.05 - PROD/QA DB2 - AS400 - Mainframe | |||
|
| Gold member |
The join works, no errors. But when I try to print to the screen a value that should be returned from the ORACLE query. It shows a blank screen. I'm trying to add INC_INCIDENT_ID FROM the ORACLE query to the below print to screen statement: All other values are from the SQL statment and they work fine in the PRINT STATEMENT - but when I add INC_INCIDENT_ID, I'm getting a blank screen
TABLE FILE TELESTAFFROSTER
HEADING CENTER
"&SUB_ASG"
PRINT UPLASTNAME AS ''
//INC_INCIDENT_ID IS A value from the Oracle query that I would like shown to the screen like the rest of the values in this statement
INC_INCIDENT_ID
BY RANK_ORDER NOPRINT
BY RANK_ABRV AS ''
WHERE TSDIVISION EQ '&DIVISION';
WHERE SUB_ASSIGNMENT EQ '&SUB_ASG';
WHERE RANK_ABRV NE 'CPT' OR 'LT';
-*ON TABLE SET EMPTYREPORT ON
ON TABLE HOLD FORMAT HTML AS 'X1'
ON TABLE SET PAGE-NUM OFF
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
INCLUDE = overtime_blue.sty,
TOPMARGIN=0.0,
LEFTMARGIN=0.0,
RIGHTMARGIN=0.0,
BOTTOMMARGIN=0.0,
$
TYPE=HEADING,
STYLE=BOLD,
$
TYPE=REPORT,
BACKCOLOR=RGB(153 204 255),
FONT=ARIAL,
$
TYPE=REPORT
COLUMN=3,
WIDTH=1.2,
WRAP=1.2,
SIZE=9,
$
END
WebFOCUS 7.6 Windows, All Outputs | |||
|
| Expert |
Have you tried "View Source" from the web browser window to see what errors were generated by your code? This is the first step in determining what the problem is. 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 | |||
|
| Gold member |
Thank you for the trouble shooting tip. I will do this now. WebFOCUS 7.6 Windows, All Outputs | |||
|
| Gold member |
It says that it can't recognize INCIDENT_SUPPLEMENTS.APPROVAL_DATE and INC_REPORT_NUMBER, but they should both be in the HOLD file. It also says THE DESCRIPTION CANNOT BE FOUND FOR FILE NAMED: OFFNSDAT Any help would be appreciated. This is what I am getting in the view source- 0 ERROR AT OR NEAR LINE 38 IN PROCEDURE district_org_chart (FOC003) THE FIELDNAME IS NOT RECOGNIZED: INCIDENT_SUPPLEMENTS.APPROVAL_DATE BYPASSING TO END OF COMMAND (FOC009) INCOMPLETE REQUEST STATEMENT TABLE FILE TELESTAFFROSTER HEADING CENTER "SHIFT 2" PRINT UPLASTNAME AS '' INC_REPORT_NUMBER BY RANK_ORDER NOPRINT BY RANK_ABRV AS '' WHERE TSDIVISION EQ '1D'; WHERE SUB_ASSIGNMENT EQ 'SHIFT 2'; WHERE RANK_ABRV NE 'CPT' OR 'LT'; -*ON TABLE SET EMPTYREPORT ON ON TABLE HOLD FORMAT HTML AS 'X1' ON TABLE SET PAGE-NUM OFF ON TABLE SET HTMLCSS ON ON TABLE SET STYLE * INCLUDE = overtime_blue.sty, TOPMARGIN=0.0, LEFTMARGIN=0.0, RIGHTMARGIN=0.0, BOTTOMMARGIN=0.0, $ TYPE=HEADING, STYLE=BOLD, $ TYPE=REPORT, BACKCOLOR=RGB(153 204 255), FONT=ARIAL, $ TYPE=REPORT COLUMN=3, WIDTH=1.2, WRAP=1.2, SIZE=9, $ END -HTMLFORM BEGIN 0 ERROR AT OR NEAR LINE 57 IN PROCEDURE district_org_chart (FOC205) THE DESCRIPTION CANNOT BE FOUND FOR FILE NAMED: OFFNSDAT BYPASSING TO END OF COMMAND 0 ERROR AT OR NEAR LINE 64 IN PROCEDURE district_org_chart (FOC003) THE FIELDNAME IS NOT RECOGNIZED: INC_REPORT_NUMBER BYPASSING TO END OF COMMAND (FOC009) INCOMPLETE REQUEST STATEMENT TABLE FILE HOLD SUM INCIDENT_SUPPLEMENTS.APPROVAL_DATE INCIDENTS.INC_REPORT_NUMBER INCIDENT_SUPPLEMENTS.CREATOR_IDBY OFFICER_ID ON TABLE HOLD AS OFFNSDAT FORMAT FOCUS INDEX OFFICER_ID -RUN 0 ERROR AT OR NEAR LINE 38 IN PROCEDURE district_org_chart (FOC003) THE FIELDNAME IS NOT RECOGNIZED: INCIDENT_SUPPLEMENTS.APPROVAL_DATE BYPASSING TO END OF COMMAND (FOC009) INCOMPLETE REQUEST STATEMENT WebFOCUS 7.6 Windows, All Outputs | |||
|
| Expert |
Your HOLD file will not include the table names of the selected columns. To determine the names of the columns in a HOLD file (or any table or file with a WebFOCUS Master) add this after the END statement of the ON TABLE HOLD statement: ON TABLE HOLD FORMAT ALPHA END -RUN ?FF HOLD -EXITThis message has been edited. Last edited by: Francis Mariani, 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 | |||
|
| Gold member |
YOU ARE TRULY A SCHOLAR!!! This is what I got back after the statement you gave me : ?FF HOLD -EXIT FILENAME= HOLD APPROVAL_DATE E01 HYYMDS INC_REPORT_NUMBER E02 A20V CREATOR_ID E03 A15V DESCRIPTION E04 A60V OFFNSE_CD_OFFENSE_STATUS_CODE E05 A2V ISC_STATUS_CODE E06 A1V INCIDENT_ID E07 I11 ITC_CODE E08 A3V INC_INCIDENT_ID E09 I11 LOGIN_ID E10 A30V EJS_EMP_ID E11 P31 EJS_EMP_ID1 E12 P31 SRV_ASSIGN_CODE E13 A5V OFFICER_ID E14 I11 So Officer_ID would be E14, SO WOULD MY NEW JOIN BE JOIN E14 IN OFFNSDAT TO PerID IN TELESTAFFROSTER AS J1 AND SINCE APPROVAL_DATE IS E01 WOULD I CHANGE THE BELOW: TABLE FILE TELESTAFFROSTER HEADING CENTER "&SUB_ASG" PRINT UPLASTNAME AS '' //THIS WOULD BE APPROVAL_DATE IF I WANTED TO SHOW IT TO THE SCREEN WOULD I PUT E01 INSTEAD OF APPROVAL DATE E01 BY RANK_ORDER NOPRINT BY RANK_ABRV AS '' WHERE TSDIVISION EQ '&DIVISION'; WHERE SUB_ASSIGNMENT EQ '&SUB_ASG'; WHERE RANK_ABRV NE 'CPT' OR 'LT'; -*ON TABLE SET EMPTYREPORT ON ON TABLE HOLD FORMAT HTML AS 'X1' ON TABLE SET PAGE-NUM OFF ON TABLE SET HTMLCSS ONV WebFOCUS 7.6 Windows, All Outputs | |||
|
| Expert |
But you can use the real column names. The E99 names are aliases. Just use the real names, without the table name qualifier. 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 | |||
|
| Gold member |
Thanks for the update. But why am I getting the below error, when OFFNSDAT is the file name and OFFICER_ID should be recognized as a REAL NAME : How come any value from the ORACLE table does not want to print out with the SQL code in the TABLE FILE. Is what I'm doing even possible. Both queries print out Independently. But when I want to PRINT them both out in ONE TABLE FILE. I get a BLANK SCREEN. I'm still getting a blank report when I'm adding "OFFICER_ID" TO THE PRINT STATEMENT. This is the TABLE OUT STATEMENT Below, everything in it works right now. Because it is all SQL code, BUT as soon as I add a ORACLE variable like Officer_id, I get a BLANK SCREEN. Can Oracle data and SQL data be put in the same TABLE OUT... TABLE FILE TELESTAFFROSTER HEADING CENTER "&SUB_ASG" PRINT UPLASTNAME AS '' BY RANK_ORDER NOPRINT BY RANK_ABRV AS '' WHERE TSDIVISION EQ '&DIVISION'; WHERE SUB_ASSIGNMENT EQ '&SUB_ASG'; WHERE RANK_ABRV NE 'CPT' OR 'LT'; -*ON TABLE SET EMPTYREPORT ON ON TABLE HOLD FORMAT HTML AS 'X1' ON TABLE SET PAGE-NUM OFF ON TABLE SET HTMLCSS ON 0 ERROR AT OR NEAR LINE 65 IN PROCEDURE district_org_chart (FOC205) THE DESCRIPTION CANNOT BE FOUND FOR FILE NAMED: OFFNSDAT BYPASSING TO END OF COMMAND 0 ERROR AT OR NEAR LINE 73 IN PROCEDURE district_org_chart (FOC003) THE FIELDNAME IS NOT RECOGNIZED: OFFICER_ID BYPASSING TO END OF COMMAND (FOC009) INCOMPLETE REQUEST STATEMENT -HTMLFORM BEGINThis message has been edited. Last edited by: QuickLearner, WebFOCUS 7.6 Windows, All Outputs | |||
|
| Expert |
You may have to post your entire code. Check the code output from the run and see if OFFNSDAT gets created. Waz...
| |||||||||||||||||||||||||||
| ||||||||||||||||||||||||||||
| Expert |
And the line number specified in the error is important. 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 | |||
|
| Gold member |
HERE is my Entire code for review: -* File District_Org_Chart.fex
-SET &ECHO=ALL;
-*
-*-DEFAULT &DIVISION = '1D' ;
-* DEFAULT &SUB_ASG = 'SHIFT 1' ;
ENGINE SQLORA SET CONNECTION_ATTRIBUTES EJSWAR
SQL SQLORA
select distinct INCIDENT_SUPPLEMENTS.APPROVAL_DATE, INCIDENTS.INC_REPORT_NUMBER,
INCIDENT_SUPPLEMENTS.CREATOR_ID, INCIDENT_TYPE_CODES.DESCRIPTION, OFFENSES.OFFNSE_CD_OFFENSE_STATUS_CODE,
INCIDENT_SUPPLEMENTS.ISC_STATUS_CODE, INCIDENTS.INCIDENT_ID, INCIDENTS.ITC_CODE,
OFFENSES.INC_INCIDENT_ID, EMPLOYEES.LOGIN_ID, EMP_SERVICE_HISTORIES.EJS_EMP_ID, EMPLOYEES.EJS_EMP_ID,
EMP_SERVICE_HISTORIES.SRV_ASSIGN_CODE , EMPLOYEES.OFFICER_ID
from EMPLOYEES, EMP_SERVICE_HISTORIES, INCIDENTS, INCIDENT_SUPPLEMENTS, INCIDENT_TYPE_CODES, OFFENSES
where INCIDENTS.INCIDENT_ID=INCIDENT_SUPPLEMENTS.INC_INCIDENT_ID and
INCIDENTS.INCIDENT_ID=OFFENSES.INC_INCIDENT_ID and
EMPLOYEES.EJS_EMP_ID=EMP_SERVICE_HISTORIES.EJS_EMP_ID and
INCIDENT_TYPE_CODES.CODE=INCIDENTS.ITC_CODE and
INCIDENT_SUPPLEMENTS.CREATOR_ID = EMPLOYEES.LOGIN_ID and
INCIDENTS.ITC_CODE = INCIDENT_TYPE_CODES.CODE AND
offenses.OFFNSE_CD_OFFENSE_STATUS_CODE = '03' and
INCIDENT_SUPPLEMENTS.ISC_STATUS_CODE = 'A'
and ROWNUM <= 10;
TABLE FILE SQLOUT
PRINT *
ON TABLE HOLD FORMAT ALPHA
END
-*EXIT
-RUN
TABLE FILE HOLD
SUM
INCIDENT_SUPPLEMENTS.APPROVAL_DATE
INCIDENTS.INC_REPORT_NUMBER
INCIDENT_SUPPLEMENTS.CREATOR_ID
INCIDENT_TYPE_CODES.DESCRIPTION
OFFENSES.OFFNSE_CD_OFFENSE_STATUS_CODE
INCIDENT_SUPPLEMENTS.ISC_STATUS_CODE
INCIDENTS.INCIDENT_ID
INCIDENTS.ITC_CODE
OFFENSES.INC_INCIDENT_ID
EMPLOYEES.LOGIN_ID
EMP_SERVICE_HISTORIES.EJS_EMP_ID
EMPLOYEES.EJS_EMP_ID
EMP_SERVICE_HISTORIES.SRV_ASSIGN_CODE
BY OFFICER_ID
ON TABLE HOLD AS OFFNSDAT FORMAT FOCUS INDEX OFFICER_ID
-RUN
-*?FF HOLD
-*-EXIT
-*JOIN PerID IN TELESTAFFROSTER TO ALL OFFICER_ID IN OFFNSDAT AS J1
-*JOIN OFFICER_ID IN OFFNSDAT TO PerID IN TELESTAFFROSTER AS J1
JOIN OFFICER_ID IN OFFNSDAT TO PerID IN TELESTAFFROSTER AS J1
TABLE FILE TELESTAFFROSTER
HEADING CENTER
"&SUB_ASG"
PRINT UPLASTNAME AS ''
//I would like to print out some information to the screen from the ORACLE query. This is where the problem is. OFFICER_ID is not showing.
OFFICER_ID
BY RANK_ORDER NOPRINT
BY RANK_ABRV AS ''
WHERE TSDIVISION EQ '&DIVISION';
WHERE SUB_ASSIGNMENT EQ '&SUB_ASG';
WHERE RANK_ABRV NE 'CPT' OR 'LT';
-*ON TABLE SET EMPTYREPORT ON
ON TABLE HOLD FORMAT HTML AS 'X1'
ON TABLE SET PAGE-NUM OFF
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
INCLUDE = overtime_blue.sty,
TOPMARGIN=0.0,
LEFTMARGIN=0.0,
RIGHTMARGIN=0.0,
BOTTOMMARGIN=0.0,
$
TYPE=HEADING,
STYLE=BOLD,
$
TYPE=REPORT,
BACKCOLOR=RGB(153 204 255),
FONT=ARIAL,
$
TYPE=REPORT
COLUMN=3,
WIDTH=1.2,
WRAP=1.2,
SIZE=9,
$
END
-HTMLFORM BEGIN
<html>
<body style="background-color:#99CCFF;")>
<div valign="top" style="width:150px;">
!IBI.FIL.X1;
</div>
</body>
</html>
-HTMLFORM END WebFOCUS 7.6 Windows, All Outputs | |||
|
| Guru |
After you first SQL, why not give it table hold name? Remove this after you SQL TABLE FILE SQLOUT PRINT * ON TABLE HOLD FORMAT ALPHA END And add this after your Semi-colon and your SQL TABLE ON TABLE HOLD AS "Tablename" END -RUN I personally would never call anything "HOLD". You have an additional step in there, that is not needed. I would do your Sum statement right after your SQL pull. Maybe also do some sort of define prior to your SUM step so you have a field properly defined for joining to the next table. WebFOCUS 8.1.05 | |||
|
| Gold member |
I changed the code to meet your needs and added the OFFICER_ID TO THE last TABLE FILE TELESTAFFROSTER AND AM STILL GETTING A BLANK SCREEN. Please review. I named the table OFFNSDAT, is this ok since there is another TABLE ON HOLD NAMED the same thing. When I remove the OFFICER_ID, ORACLE CODE, it works fine. But as soon as I add ORACLE CODE To that table out, I get a blank screen -* File District_Org_Chart.fex
-SET &ECHO=ALL;
-*
-*-DEFAULT &DIVISION = '1D' ;
-* DEFAULT &SUB_ASG = 'SHIFT 1' ;
ENGINE SQLORA SET CONNECTION_ATTRIBUTES EJSWAR
SQL SQLORA
select distinct INCIDENT_SUPPLEMENTS.APPROVAL_DATE, INCIDENTS.INC_REPORT_NUMBER,
INCIDENT_SUPPLEMENTS.CREATOR_ID, INCIDENT_TYPE_CODES.DESCRIPTION, OFFENSES.OFFNSE_CD_OFFENSE_STATUS_CODE,
INCIDENT_SUPPLEMENTS.ISC_STATUS_CODE, INCIDENTS.INCIDENT_ID, INCIDENTS.ITC_CODE,
OFFENSES.INC_INCIDENT_ID, EMPLOYEES.LOGIN_ID, EMP_SERVICE_HISTORIES.EJS_EMP_ID, EMPLOYEES.EJS_EMP_ID,
EMP_SERVICE_HISTORIES.SRV_ASSIGN_CODE , EMPLOYEES.OFFICER_ID
from EMPLOYEES, EMP_SERVICE_HISTORIES, INCIDENTS, INCIDENT_SUPPLEMENTS, INCIDENT_TYPE_CODES, OFFENSES
where INCIDENTS.INCIDENT_ID=INCIDENT_SUPPLEMENTS.INC_INCIDENT_ID and
INCIDENTS.INCIDENT_ID=OFFENSES.INC_INCIDENT_ID and
EMPLOYEES.EJS_EMP_ID=EMP_SERVICE_HISTORIES.EJS_EMP_ID and
INCIDENT_TYPE_CODES.CODE=INCIDENTS.ITC_CODE and
INCIDENT_SUPPLEMENTS.CREATOR_ID = EMPLOYEES.LOGIN_ID and
INCIDENTS.ITC_CODE = INCIDENT_TYPE_CODES.CODE AND
offenses.OFFNSE_CD_OFFENSE_STATUS_CODE = '03' and
INCIDENT_SUPPLEMENTS.ISC_STATUS_CODE = 'A'
and ROWNUM <= 10;
-*TABLE FILE SQLOUT
-*PRINT *
-*ON TABLE HOLD FORMAT ALPHA
-*END
TABLE
ON TABLE HOLD AS "OFFNSDAT"
END
-RUN
-*EXIT
-RUN
TABLE FILE HOLD
SUM
INCIDENT_SUPPLEMENTS.APPROVAL_DATE
INCIDENTS.INC_REPORT_NUMBER
INCIDENT_SUPPLEMENTS.CREATOR_ID
INCIDENT_TYPE_CODES.DESCRIPTION
OFFENSES.OFFNSE_CD_OFFENSE_STATUS_CODE
INCIDENT_SUPPLEMENTS.ISC_STATUS_CODE
INCIDENTS.INCIDENT_ID
INCIDENTS.ITC_CODE
OFFENSES.INC_INCIDENT_ID
EMPLOYEES.LOGIN_ID
EMP_SERVICE_HISTORIES.EJS_EMP_ID
EMPLOYEES.EJS_EMP_ID
EMP_SERVICE_HISTORIES.SRV_ASSIGN_CODE
BY OFFICER_ID
ON TABLE HOLD AS OFFNSDAT FORMAT FOCUS INDEX OFFICER_ID
-RUN
-*?FF HOLD
-*-EXIT
-*JOIN PerID IN TELESTAFFROSTER TO ALL OFFICER_ID IN OFFNSDAT AS J1
-*JOIN OFFICER_ID IN OFFNSDAT TO PerID IN TELESTAFFROSTER AS J1
JOIN OFFICER_ID IN OFFNSDAT TO PerID IN TELESTAFFROSTER AS J1
TABLE FILE TELESTAFFROSTER
HEADING CENTER
"&SUB_ASG"
PRINT UPLASTNAME AS ''
OFFICER_ID
BY RANK_ORDER NOPRINT
BY RANK_ABRV AS ''
WHERE TSDIVISION EQ '&DIVISION';
WHERE SUB_ASSIGNMENT EQ '&SUB_ASG';
WHERE RANK_ABRV NE 'CPT' OR 'LT';
-*ON TABLE SET EMPTYREPORT ON
ON TABLE HOLD FORMAT HTML AS 'X1'
ON TABLE SET PAGE-NUM OFF
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
INCLUDE = overtime_blue.sty,
TOPMARGIN=0.0,
LEFTMARGIN=0.0,
RIGHTMARGIN=0.0,
BOTTOMMARGIN=0.0,
$
TYPE=HEADING,
STYLE=BOLD,
$
TYPE=REPORT,
BACKCOLOR=RGB(153 204 255),
FONT=ARIAL,
$
TYPE=REPORT
COLUMN=3,
WIDTH=1.2,
WRAP=1.2,
SIZE=9,
$
END
-HTMLFORM BEGIN
<html>
<body style="background-color:#99CCFF;")>
<div valign="top" style="width:150px;">
!IBI.FIL.X1;
</div>
</body>
</html>
-HTMLFORM END
WebFOCUS 7.6 Windows, All Outputs | |||
|
| Guru |
Remove the double quotes, those aren't needed. In your next step your doing a TABLE FILE HOLD Do a TABLE FILE OFFNSDAT and your SUM. Do you get records from your SQL and do you get records from your SUM? WebFOCUS 8.1.05 | |||
|
| Gold member |
I'm still getting a BLANK SCREEN. Please review updated code: I added INC_REPORT_NUMBER from the ORACLE QUERY TO TRY ANOTHER COLUMN, STILL GETTING BLANK SCREE. -* File District_Org_Chart.fex
-SET &ECHO=ALL;
-*
-*-DEFAULT &DIVISION = '1D' ;
-* DEFAULT &SUB_ASG = 'SHIFT 1' ;
ENGINE SQLORA SET CONNECTION_ATTRIBUTES EJSWAR
SQL SQLORA
select distinct INCIDENT_SUPPLEMENTS.APPROVAL_DATE, INCIDENTS.INC_REPORT_NUMBER,
INCIDENT_SUPPLEMENTS.CREATOR_ID, INCIDENT_TYPE_CODES.DESCRIPTION, OFFENSES.OFFNSE_CD_OFFENSE_STATUS_CODE,
INCIDENT_SUPPLEMENTS.ISC_STATUS_CODE, INCIDENTS.INCIDENT_ID, INCIDENTS.ITC_CODE,
OFFENSES.INC_INCIDENT_ID, EMPLOYEES.LOGIN_ID, EMP_SERVICE_HISTORIES.EJS_EMP_ID, EMPLOYEES.EJS_EMP_ID,
EMP_SERVICE_HISTORIES.SRV_ASSIGN_CODE , EMPLOYEES.OFFICER_ID
from EMPLOYEES, EMP_SERVICE_HISTORIES, INCIDENTS, INCIDENT_SUPPLEMENTS, INCIDENT_TYPE_CODES, OFFENSES
where INCIDENTS.INCIDENT_ID=INCIDENT_SUPPLEMENTS.INC_INCIDENT_ID and
INCIDENTS.INCIDENT_ID=OFFENSES.INC_INCIDENT_ID and
EMPLOYEES.EJS_EMP_ID=EMP_SERVICE_HISTORIES.EJS_EMP_ID and
INCIDENT_TYPE_CODES.CODE=INCIDENTS.ITC_CODE and
INCIDENT_SUPPLEMENTS.CREATOR_ID = EMPLOYEES.LOGIN_ID and
INCIDENTS.ITC_CODE = INCIDENT_TYPE_CODES.CODE AND
offenses.OFFNSE_CD_OFFENSE_STATUS_CODE = '03' and
INCIDENT_SUPPLEMENTS.ISC_STATUS_CODE = 'A'
and ROWNUM <= 10;
-*TABLE FILE SQLOUT
-*PRINT *
-*ON TABLE HOLD FORMAT ALPHA
-*END
TABLE
ON TABLE HOLD AS OFFNSDAT
END
-RUN
-*EXIT
-RUN
TABLE FILE OFFNSDAT
SUM
INCIDENT_SUPPLEMENTS.APPROVAL_DATE
INCIDENTS.INC_REPORT_NUMBER
INCIDENT_SUPPLEMENTS.CREATOR_ID
INCIDENT_TYPE_CODES.DESCRIPTION
OFFENSES.OFFNSE_CD_OFFENSE_STATUS_CODE
INCIDENT_SUPPLEMENTS.ISC_STATUS_CODE
INCIDENTS.INCIDENT_ID
INCIDENTS.ITC_CODE
OFFENSES.INC_INCIDENT_ID
EMPLOYEES.LOGIN_ID
EMP_SERVICE_HISTORIES.EJS_EMP_ID
EMPLOYEES.EJS_EMP_ID
EMP_SERVICE_HISTORIES.SRV_ASSIGN_CODE
BY OFFICER_ID
ON TABLE HOLD AS OFFNSDAT FORMAT FOCUS INDEX OFFICER_ID
-RUN
-*?FF HOLD
-*-EXIT
-*JOIN PerID IN TELESTAFFROSTER TO ALL OFFICER_ID IN OFFNSDAT AS J1
-*JOIN OFFICER_ID IN OFFNSDAT TO PerID IN TELESTAFFROSTER AS J1
JOIN OFFICER_ID IN OFFNSDAT TO PerID IN TELESTAFFROSTER AS J1
TABLE FILE TELESTAFFROSTER
HEADING CENTER
"&SUB_ASG"
PRINT UPLASTNAME AS ''
INC_REPORT_NUMBER
BY RANK_ORDER NOPRINT
BY RANK_ABRV AS ''
WHERE TSDIVISION EQ '&DIVISION';
WHERE SUB_ASSIGNMENT EQ '&SUB_ASG';
WHERE RANK_ABRV NE 'CPT' OR 'LT';
-*ON TABLE SET EMPTYREPORT ON
ON TABLE HOLD FORMAT HTML AS 'X1'
ON TABLE SET PAGE-NUM OFF
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
INCLUDE = overtime_blue.sty,
TOPMARGIN=0.0,
LEFTMARGIN=0.0,
RIGHTMARGIN=0.0,
BOTTOMMARGIN=0.0,
$
TYPE=HEADING,
STYLE=BOLD,
$
TYPE=REPORT,
BACKCOLOR=RGB(153 204 255),
FONT=ARIAL,
$
TYPE=REPORT
COLUMN=3,
WIDTH=1.2,
WRAP=1.2,
SIZE=9,
$
END
-HTMLFORM BEGIN
<html>
<body style="background-color:#99CCFF;")>
<div valign="top" style="width:150px;">
!IBI.FIL.X1;
</div>
</body>
</html>
-HTMLFORM END WebFOCUS 7.6 Windows, All Outputs | |||
|
| Gold member |
I figured it out. I had to change the last TABLE OUT from TELLESTAFFROSTER TO OFFNSDAT. WebFOCUS 7.6 Windows, All Outputs | |||
|
| 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 |