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] Report is not pulling the data

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] Report is not pulling the data
 Login/Join
 
kpiracha
Gold member
posted
Hello, I have report with the following code.
JOIN CLEAR *
END
JOIN
F060116.F060116.ADDRESS_NUMBER
IN F060116
TO MULTIPLE F0116.F0116.ADDRESS_NUMBER
IN F0116 TAG J001
AS J001
END
JOIN
F060116.F060116.JOB_STEP AND F060116.F060116.JOB_TYPE
IN F060116
TO MULTIPLE F08001.F08001.JOB_STEP AND F08001.F08001.JOB_TYPE
IN F08001 TAG J002
AS J002
END
TABLE FILE F060116
PRINT
HOME_COMPANY
ADDRESS_NUMBER
EMPLOYEE_TAX_ID
ALPHA_NAME
HOME_BUSINESS_UNIT
JOB_TYPE
JOB_STEP
DESCRIPTION
WHERE PAY_STATUS EQ '0';
-*WHERE JOB_TYPE EQ '001140';
-*WHERE JOB_STEP EQ '410B';
ON TABLE PCHOLD FORMAT EXL2K
END


When I execute the report without using the 2 WHERE clauses:
-*WHERE JOB_TYPE EQ '001140';
-*WHERE JOB_STEP EQ '410B';

The above report should pull all the data where 'PAY_STATUS' EQ '0 listing the JOB_TYPE '001140' and JOB_STEP '410B' in it but it does not.
When I uncomment the 2 WHERE clauses, I do get the data for JOB_TYPE '001140' and JOB_STEP '410B'. Its like I have to literally tell it to pull data for the specific job type. What am I doing wrong. Please help!!.

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


WebFOCUS 7.6.9
Windows 2003
HTML, Plain Text
 
Posts: 96 | Registered: March 15, 2010Report This Post
Darin Lee
Virtuoso
posted Hide Post
Would probably need to see some master files and sample data to know which fields and data are coming from which files.


Regards,

Darin


In FOCUS since 1991
WF Server: 7.7.04 on Linux and Z/OS, ReportCaster, Self-Service, MRE, Java, Flex
Data: DB2/UDB, Adabas, SQL Server Output: HTML,PDF,EXL2K/07, PS, AHTML, Flex
WF Client: 77 on Linux w/Tomcat
 
Posts: 2298 | Location: Salt Lake City, Utah | Registered: February 02, 2007Report This Post
GamP
Virtuoso
posted Hide Post
It could be that the fields actually are longer then the test value you're using - so there could be extra spaces to either left or right side. Which is probably why Darin asked to see the master file(s) involved.


GamP
- Using AS 8.2.01 on Windows 10 - IE11.
in Focus since 1988
 
Posts: 1961 | Location: Netherlands | Registered: September 25, 2007Report This Post
Dan Satchell
Virtuoso
posted Hide Post
I would look at the SQL traces for execution of the code, both with and without the WHERE clauses, to see how the WHERE clauses affect the code that is passed to the database.


WebFOCUS 7.7.05
 
Posts: 1213 | Location: Seattle, Washington - USA | Registered: October 22, 2007Report This Post
kpiracha
Gold member
posted Hide Post
Good idea. I ran the trace and found what the problem was. In Test the traces showed the ORDER BY field as follows:

ORDER BY
T1."YATINC",T2."ALAN8",T2."ALEFTB",T3."WWAN8",T3."WWTYC";

In production the traces showed the order by field as follows;

ORDER BY
T1."YAAN8",T2."ALAN8",T2."ALEFTB",T3."WWAN8",T3."WWIDLN";

I checked the metadata in both servers. In production the access file showed Key = 3 and first 3 fields in the metadata were
"YAAN8"
"ALAN8"
"ALEFTB"

In Test server the access file also showed key = 3 but but the first 3 fields were:
"YATINC"
"ALAN8",
"ALEFTB"

Question: How come in one metadata the first field is YAAN8 and in the other it is YATINC.

We created metadata using 'New Synonyme'. Is there anything that tells it to set certain fields on the top.


WebFOCUS 7.6.9
Windows 2003
HTML, Plain Text
 
Posts: 96 | Registered: March 15, 2010Report This Post
PBrightwell
Master
posted Hide Post
quote:
How come in one metadata the first field is YAAN8 and in the other it is YATINC


Are you hitting the production data in both cases. It sounds like you need to talk to your DBA, that the underlying (ORACLE, SQL) DB is not defined the same in your Dev environment as it is in your Production environment. Are you possibly hitting a view in one (or a different view)?


Pat
WF 7.6.8, AIX, AS400, NT
AS400 FOCUS, AIX FOCUS,
Oracle, DB2, JDE, Lotus Notes
 
Posts: 755 | Location: TX | Registered: September 25, 2007Report This Post
Dan Satchell
Virtuoso
posted Hide Post
Also, check the primary keys defined for the table in the two environments.


WebFOCUS 7.7.05
 
Posts: 1213 | Location: Seattle, Washington - USA | Registered: October 22, 2007Report This Post
kpiracha
Gold member
posted Hide Post
Ran the trace and found that the SQL command being sent from the TEST server had different Order By clause compared to PRODUCTION server. After further testing we found that the issue had to do with the MetaData in one of the tables. In PRODUCTION, when metadata was created, the acx file showed only one primary key as 'Key = 1' and in the metadata of that table, the first key was 'YAAN8'. In TEST server, the access file showed 3 keys as 'Key = 3' and in the metadata of that table, the first 3 fields didn't contain 'YAAN8'. We had to manually update the TEST metadata by moving the 'YAAN8' field as the top field so that the Order By clause inludeS it first. Once that was done. Issue was fixed.

We asked IBI as to why the metadata had different order of field names. They said it was a known issue with DB2 and fixed in WebFOCUS 7.6.11.


WebFOCUS 7.6.9
Windows 2003
HTML, Plain Text
 
Posts: 96 | Registered: March 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] Report is not pulling the data

Copyright © 1996-2020 Information Builders