Hi All,

I've searched in all the forums for any help on this error and didn't find any. Has anyone had one before? This error occurs only when I add a BY field using defined fields DIVPRODUCT and/or BASEPROD. If I use LINECODE or LOTNUMBER, all is okay. Below is the source:

DEFINE FILE RV_PRISM_LOTHISTORY
NDATEX2/MDYY = HDATE(EVENTSTARTDATETIME, 'MDYY');
DIVPRODUCT/A30 = PROCESSTYPE||PROCESSCODE||PROCESSID;
BASEPROD/A35 = PROCESSTYPE||PROCESSCODE||PROCESSID||MANAGEMENTCODE;
END

TABLE FILE RV_PRISM_LOTHISTORY
PRINT
LINECODE
LOTNUMBER
NDATEX2
DIVPRODUCT
BASEPROD
INVENTORYWAFERS
SOURCELOTNUMBER
BY DIVPRODUCT
WHERE RECORDLIMIT EQ 100
END

Any help would be appreciated. Thanks.

Just concentrating on DIVPRODUCT:

Is it possible that PROCESSTYPE, PROCESSCODE, PROCESSID and MANAGEMENTCODE are not all in the same path? By path, I mean this:

Assume your master looks lsomething ike this:

FILENAME=RV_PRISM_LOTHISTORY, SUFFIX=FOC , $
SEGMENT=ROOT, SEGTYPE=S1, $
FIELDNAME=ROOTKEY, ALIAS=KEYFIELD, A5,$
SEGMENT=PROCESS, SEGTYPE=S1, PARENT=ROOT, $
FIELDNAME=PROCESSTYPE, ALIAS=PT, A10, $
FIELDNAME=PROCESSCODE, ALIAS=PC, A10, $
FIELDNAME=PROCESSID, ALIAS=PI, A5, $
SEGMENT=MGMT, SEGTYPE=S1, PARENT=ROOT, $
FIELDNAME=MANAGEMENTCODE, ALIAS=MC, A5, $

In this table, the process and management segments are on different paths. Meaning that they have no real realtionship to one another (look in your Describing Data manual). ust printing fields from separate paths will likely yield a nonsensical report (good example: salary history is one segment subordinate to employee id, and education history is another. What kind of report would you end up with is you reported both together?) FOCUS gets upset when you issue WHERE statements against fields in separate paths or you try to sort using fields in separate paths. Or you try to sort using a field from path A when you've printed fields from path B. It's confusing at first, but you get used to it.

Also, every time you define a field, FOCUS associates that field with a segment. If you don't tell it what segment, it assigns it to a segment in which one of the fields used to build the defined field resides (Again, the Describing Data manual). It tries for the lowest segment. Again, using my example, it is possible that it assigned DIVPRODUCT to the PROCESS SEGMENT and BASEPROD to the MGMT segment. In which case you would be printing from one segment and sorting from another.

Whatever is going on, it would seem to be something akin to what I have described. Either that, or a FOCUS bug.

Hi dwf,

Thanks for the reply. All the fields look like they are on the same path (same segment). I forgot to mention that I used joins to get the information. It's structured this way:

TABLE1 (history table - multiple entries per line - lot combination)
Line (k)
Lot (k)

TABLE2 (current info only - 1 row per line - lot combination)
Line (k)
Lot (k)
Process Type
Process Code
Process ID
Management Code

I looked at the code and it was joined as TABLE1 to MULTIPLE TABLE2. When I changed the join to TABLE1 TO UNIQUE TABLE2, I no longer get the error.

Dan

I'm not sure I have the full picture yet, but when you join unique, the joined to segment becomes what FOCUS calls a unique segment. Functionally, that's as if that segment was part of the segment from which you joined. No possibility of the kind of error you've been getting. Hmmmm. Try joining table2 to multiple table1. From your notes, it looks like that's the correct join structure anyway.

This may be a shot in the dark but I've seen cases like this as well. When you are in painter and create a define field the way you have you will see it show up at the very bottom of the list. I think that happens because during a join the structure wants to know which segment that define belongs to (therefore showing up in that segment and not at the bottom). I think it is the WITH clause where you would just assign one of those fields used so it knows which segment it belongs. I would pick the segment that is at the lowest level of the data structure and try that.

It is most likely getting confused when trying to determine what/where to do the define.

warning.. I am not an expert

You haven't mentioned what kind of tables these are. FOCUS databases? SQL DBMS?

If these are SQL DBMS, why not trace the SQL generated by WebFOCUS?

Add this to the code:

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

I tend to agree with Johnny5 on using a WITH in the define, if you are sure your path is truly vertical and all the fields in your print statement are in the 'same path'.

Many thanks for all the people who replied. As per my earlier thread, we were able to get around it with using a join unique. When I get a chance, I will try out the other suggestions posted.

Dan