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,

Would probably need to see some master files and sample data to know which fields and data are coming from which files.

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.

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.

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.

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)?

Also, check the primary keys defined for the table in the two environments.

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.