Hello. I've got a question around joining two oracle tables through master files. I have a budget_center field that in one system is reported as 5 character and the rest of our world uses 4 characters. It is the same thing with an extra character in the front.

I have defined a field on my employee master that will strip that leading space (using substring) and when I join it will not let me. The fields are the same data type and length.

What am I missing?

I don't think you can join two Oracle tables based on a defined field that is a string manipulation.

I believe it will only work for selection criteria.

It seems like it is in fact processing the join first and then deals with the define. It will let me join the two but will not return any data. Running each piece individually gets me the data that I should see but they won't link up.

It works ok if I do the first piece in the of emp to job codes in the report and hold it. Then if I join back to the org structure it works. However, this all happens post join and define. Looks like I may need budget_center_A and budget_center_B

Have you tried turning on the SQL trace to see what SQL is generated and if there are any messages from the WebFOCUS SQL translator?

I would set XRETRIEVAL to OFF to test the code.

Put this before the report code:

SET XRETRIEVAL = OFF

SET TRACEOFF = ALL
SET TRACEON = SQLTRANS
SET TRACEON = STMTRACE//CLIENT
SET TRACEON = SQLAGGR//CLIENT
SET TRACESTAMP = OFF
SET TRACEWRAP = 78
SET TRACEUSER = ON
-RUN

No. But then again I can't get the files to join up in the master so I have not written a report off of them either.

The only way I have gotten my answer set back is to join emp -> job codes and HOLD and then HOLD to organization and then I'm all set. But that is an extra step I wish to avoid on behalf of all the developers who will use this object.

All of those trace commands makes me realize I have a lot to learn here.

Curious if there was ever an answer about DEFINE-BASED JOINS in a Master File. I have a project where I could sure use one.

If you are using SQL based data then why not get a view set up where the field you require defined is already part of the view? That way you won't have to rely upon WF defines.

Then again, why not go the whole hog and get the join structure set up as a view .....

If your DBAs are none to keen on setting up views (and some are not I know) then just do the whole thing in SQL within your fex.

T