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] Connecting Databases

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] Connecting Databases
 Login/Join
 
Gold member
posted
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
 
Posts: 96 | Registered: October 13, 2014Report This Post
Virtuoso
posted Hide Post
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
 
Posts: 1451 | Location: Portugal | Registered: February 07, 2007Report This Post
Gold member
posted Hide Post
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
 
Posts: 96 | Registered: October 13, 2014Report This Post
Expert
posted Hide Post
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
 
Posts: 1972 | Location: Centennial, CO | Registered: January 31, 2006Report This Post
Gold member
posted Hide Post
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
 
Posts: 96 | Registered: October 13, 2014Report This Post
Gold member
posted Hide Post
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
 
Posts: 96 | Registered: October 13, 2014Report This Post
Gold member
posted Hide Post
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
 
Posts: 96 | Registered: October 13, 2014Report This Post
Expert
posted Hide Post
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
 
Posts: 1972 | Location: Centennial, CO | Registered: January 31, 2006Report This Post
Gold member
posted Hide Post
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
 
Posts: 96 | Registered: October 13, 2014Report This Post
Expert
posted Hide Post
Your JOIN syntax is wrong, see my previous post...


Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe
 
Posts: 1972 | Location: Centennial, CO | Registered: January 31, 2006Report This Post
Gold member
posted Hide Post
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
 
Posts: 96 | Registered: October 13, 2014Report This Post
Expert
posted Hide Post
quote:
It shows a blank screen

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
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Gold member
posted Hide Post
Thank you for the trouble shooting tip. I will do this now.


WebFOCUS 7.6
Windows, All Outputs
 
Posts: 96 | Registered: October 13, 2014Report This Post
Gold member
posted Hide Post
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
 
Posts: 96 | Registered: October 13, 2014Report This Post
Expert
posted Hide Post
quote:
INCIDENT_SUPPLEMENTS.APPROVAL_DATE

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

This 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
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Gold member
posted Hide Post
quote:
?FF HOLD
-EXIT


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
 
Posts: 96 | Registered: October 13, 2014Report This Post
Expert
posted Hide Post
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
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Gold member
posted Hide Post
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 BEGIN

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


WebFOCUS 7.6
Windows, All Outputs
 
Posts: 96 | Registered: October 13, 2014Report This Post
Expert
posted Hide Post
You may have to post your entire code.

Check the code output from the run and see if OFFNSDAT gets created.


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6347 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
Expert
posted Hide Post
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
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Gold member
posted Hide Post
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
 
Posts: 96 | Registered: October 13, 2014Report This Post
Guru
posted Hide Post
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
 
Posts: 496 | Registered: January 04, 2008Report This Post
Gold member
posted Hide Post
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
 
Posts: 96 | Registered: October 13, 2014Report This Post
Guru
posted Hide Post
quote:
"OFFNSDAT"



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
 
Posts: 496 | Registered: January 04, 2008Report This Post
Gold member
posted Hide Post
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
 
Posts: 96 | Registered: October 13, 2014Report This Post
Gold member
posted Hide Post
I figured it out. I had to change the last TABLE OUT from TELLESTAFFROSTER TO OFFNSDAT.


WebFOCUS 7.6
Windows, All Outputs
 
Posts: 96 | Registered: October 13, 2014Report 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] Connecting Databases

Copyright © 1996-2020 Information Builders