Hello Everyone,

I was trying to create a Define Field in a WebFocus Procedure, however I realized that one of the conditions invovled would have to check if a certain column's value is inside a separate table.

Example:
DEFINE FILE TABLE1
TROUBLED_FLAG/A1 = IF COLUMN-A "INSIDE (TABLE FILE TABLE2 PRINT COLUMN-A END)" THEN 'Y' ELSE 'N';
END

can you JOIN some key field in TABLE1 to same key field in TABLE2?

I have tried the joins but it has been giving me this error:

(FOC1070) VALUE FOR JOIN 'FROM' FIELD OUT OF SEQUENCE. RETRIEVAL ENDED

Here is an example of the code that the MRE assistant generated for a define based join. In this case, the school is not in any order in the RBHS..., however RBHS... is in the main key order of student. You may have to extract and hold then join to get the files in an order that FOCUS will accept.
[code begins]
JOIN
RTRTTBL_UNO_PROD.RTRTTBL.RT005_RTRT AND RTRTTBL_UNO_PROD.RTRTTBL.RT010_RTRT
IN RTRTTBL_UNO_PROD
TO MULTIPLE RTPGTBL_UNO_PROD.RTPGTBL.RT005_RTPG AND RTPGTBL_UNO_PROD.RTPGTBL.RT010_RTPG
IN RTPGTBL_UNO_PROD TAG J001
AS J001
END
JOIN
J001.RTPGTBL.RT010_RTPG
IN RTRTTBL_UNO_PROD
TO MULTIPLE RBHSTBL_UNO_PROD.RBHSTBL.RB005_RBHS
IN RBHSTBL_UNO_PROD TAG J002
AS J002
END
JOIN
SCHOOL WITH RB110
IN RTRTTBL_UNO_PROD
TO MULTIPLE AIRTTBL_UNO_PROD.AIRTTBL.AI010_AIRT
IN AIRTTBL_UNO_PROD TAG J003
AS J003
END
DEFINE FILE RTRTTBL_UNO_PROD
-* DEFINE BEGIN SCHOOL
SCHOOL/A7 = EDIT (RB110,'$$9999999');
-* DEFINE END SCHOOL
END
TABLE FILE RTRTTBL_UNO_PROD
[code ends]

Thanks. That helped.

Ben,

Another method of achieving this is to use a decode against a file. The downside is that there is a limitation of about 32000 chars (I think) in the external file, but it might prove useful for you -

SET HOLDLIST = PRINTONLY
TABLE FILE CAR
BY CAR
WHERE COUNTRY EQ 'W GERMANY'
   OR COUNTRY EQ 'ENGLAND'
   ON TABLE SAVE
END
-RUN

DEFINE FILE CAR
  WANT_IT/A1 = DECODE CAR(SAVE ELSE 'N');
END
TABLE FILE CAR
SUM RCOST
    DCOST
    WANT_IT
 BY COUNTRY
 BY CAR
 BY MODEL
-*WHERE WANT_IT NE 'N'
END
-RUN

T