After our university upgraded to WebFOCUS 7.6, several fexes don't return data.
The fexes work in WebFOCUS 7.1. There seems to be an issue with the joins. In the example below, the cross reference field(FTVFUND_ORGN_CODE_DEF) is not a key field.

TABLE FILE FTVORGN
PRINT
FTVORGN_TITLE
BY FTVORGN_ORGN_CODE
BY HIGHEST 1 FTVORGN_NCHG_DATE
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE HOLD AS HOLDORG
END
JOIN
INNER HOLDORG.HOLDORG.FTVORGN_ORGN_CODE IN HOLDORG TO MULTIPLE FTVFUND.FTVFUND.FTVFUND_ORGN_CODE_DEF IN FTVFUND AS J0 END DEFINE FILE HOLDORG DEF_FUND_DATE/YYMD=HDATE( FTVFUND_NCHG_DATE, 'YYMD'); END

TABLE FILE HOLDORG
PRINT
FTVFUND_FUND_CODE
FTVFUND_TITLE
FTVFUND_ORGN_CODE_DEF
FTVORGN_TITLE
BY FTVFUND_PROG_CODE_DEF
WHERE DEF_FUND_DATE GE '20991201';
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT EXL2K
ON TABLE SET STYLE *
UNITS=IN,
PAGESIZE='SCREEN',
LEFTMARGIN=0.000000,
RIGHTMARGIN=0.000000,
TOPMARGIN=0.000000,
BOTTOMMARGIN=0.000000,
SQUEEZE=ON,
ORIENTATION=PORTRAIT,
$
TYPE=REPORT,
FONT='TIMES NEW ROMAN',
SIZE=10,
COLOR='BLACK',
BACKCOLOR='NONE',
STYLE=NORMAL,
$
ENDSTYLE
END

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

I'd start by comparing the access and master files between the two environments, were they recreated for the new environment?

The meta data was not recreated. It was migrated from 7.1.

A couple things to try. First, try commenting out the WHERE clause and add DEF_FUND_DATE as a PRINT field and see if you get any data back. If you do, the problem isn't in the JOIN. Check the values of DEF_FUND_DATE and see what you're getting. I've seen weirder things using HDATE and think that might be a possible cause.

Also wanted to make a note that you are selecting on a virtual field instead of a real field (which is no-no in my book) but sometimes can't be avoided.

It would also be helpful to be able to see the MFDs for FTVORGN and FTVFUND.

I think there's something else happening, because in all the upgrades I've gone through, I've never seen a JOIN just stop working. Not to say it couldn't happen...

quote:

INNER HOLDORG.HOLDORG.FTVORGN_ORGN_CODE IN HOLDORG TO MULTIPLE FTVFUND.FTVFUND.FTVFUND_ORGN_CODE_DEF IN FTVFUND AS J0 END DEFINE FILE HOLDORG

In the above, is it really,

  
FTVFUND AS J0 
END 
DEFINE FILE HOLDORG

Or is it really all on one line?

I was going to ask the same, but was just assuming the carriage returns somehow got lost in the cut-and-paste

Darin Lee & Leah,

No, it is not all on the same line. The carriage returns somehow got lost in the cut-and-paste.

If its a virtual field (if this is the same as a defined field) don't you have to use 'with' in the join syntax?

i don't think this is the problem as the join would have failed before and also can't see that you are using one but thought i'd follow up on what Darin mentioned.

have you tried redoing the code as a match and running it on a subset of records to verify the commonality of the field you need to join on?

Cindy,
First I would run a test against whichever file contains FTVFUND_NCHG_DATE to see if there are values GE '20991201'. You don't need to define a field without the timestamp because you are doing a GE not EQ. (You could be returning all of the rows in your database and running out of space). Put a RECORDLIMIT EQ 10 on your request to limit the number of rows returned.

Second, I would backup the current Master and Access for both files and regenerate them. If you copy the MFD's (synonyms) into Word you can use the compare and merge feature to see if there are differences between the old and new.

Third, check your join fields to make sure they are in the same format. You can get your MFD from your hold by putting &FF HOLDORG after the END.

If you still haven't found the problem I would I would pull the data from FTVFUND where FTVFUND_NCHG_DATE GE '20991201' sorted by FTVFUND_ORGN_CODE_DEF BY FTVFUN_NCHG_DATE and hold it FORMAT FOCUS INDEX FTVFUND_ORGN_CODE_DEF. Then join the two Focus files. OR I would do a SQL pass thru to pull the data.

Since your join was apparently working before the upgrade, you shouldn't have to go to that extreme.

Good Luck

P Brightwell,

The fex will work if I do the following: I pulled the data from FTVFUND where FTVFUND_NCHG_DATE GE '20991201' sorted by FTVFUND_ORGN_CODE_DEF BY FTVFUN_NCHG_DATE and held it FORMAT FOCUS INDEX FTVFUND_ORGN_CODE_DEF. Then joined the two Focus files.
I still don't understand why the old fex worked in 7.1 and is not working in 7.6.

Cindy,

I would open a case if I were you. If you are telling us that nothing changed except the version of the software, then that needs to be investigated to at least find out why the code no longer works.

We had a lot of issues like that when we went from 5.3.3 to 7.6.2. I opened cases on many of them and occasionally I had discovered a bug.

Even if it is not a bug, maybe you will find out why the code is not working.

Then you can tell the rest of us.

quote:

In the example below, the cross reference field(FTVFUND_ORGN_CODE_DEF) is not a key field.

There may be a clue in the above. Since you are joining to ALL on a field that is not a key I am almost surprised it worked in the first place. What database are you using? Can you do a view?

If people are suggesting there's something wrong with the JOIN in the first place, perhaps you should trace the SQL generated by WebFOCUS.

I have the following code in a focexec that I ALWAYS INCLUDE in ALL my focexecs that read SQL tables:

-*-- Set up SQL tracing --------------------------------------------------------

-*-- Deactivate SQL tracing --------------------------------
SET TRACEOFF = ALL

-*-- Show Commands and data exchange between the -----------
-*-- physical and the logical layers of the data adapter
-*SET TRACEON = SQLCALL

-*-- Enable Trace for the SQL Translator -------------------
SET TRACEON = SQLTRANS

-*-- Show SQL statements -----------------------------------
SET TRACEON = STMTRACE//CLIENT

-*-- Show Optimization information -------------------------
SET TRACEON = SQLAGGR//CLIENT

-*-- Show SQL generated statement trace --------------------
-*SET TRACEON = STMTRACE/1/CLIENT

-*-- Show SQL generated sub-statement trace ----------------
-*SET TRACEON = STMTRACE/2/CLIENT

-*-- Disable the trace stamp (Date/Time etc) ---------------
SET TRACESTAMP = OFF

-*-- Set trace line wrapping - # of characters -------------
SET TRACEWRAP = 78

-*-- Activate SQL tracing ----------------------------------
SET TRACEUSER = ON
-RUN

That is an excellent suggestion, Francis. However as a caution, I would not recommend leaving the trace commands active in a production program as it writes the trace files to edatemp and they must be deleted manually. You could wind up using up a lot of space that way.

So, once your program is debugged and working well, I recommend that you comment out the trace commands.

I have been working with IBI Tech Support trying to resolve this problem.
Our WebFOCUS Administrator inserted the following in the edasprof.prf file: ENGINE SQLORA SET ORACHAR VAR.
The fexes that I was having problems with in 7.6 are now working.
The IBI tech stated that there were changes internally on how they read VARCHAR fields -- which unlike fixed length fields, can vary in length. This can cause problems especially when producing HOLD files, or doing -Reads.

Thanks to everyone for your assistance.

We ran into this EXACT same issue moving from 5.3.x to 7.1.x.

This is an excellent example of why it is important to list your platforms, releases, and databases in your signatures. It helps people recognize the problems quicker who have experience with those resources.

Also, the first post in response to the question would have led to this conclusion and saved some time. The MAS files must have been recreated for the new environment and a comparison would have shown the format An in the old MAS and AnV in the new MAS.

I think the amount of lines added by a SQL trace are negligible compared to whatever else gets written to the trace file.