Hi,
I am new to FOCUS. Have some doubt regarding joins. Please help.

I would like to know the syntax for Left outer join. And the alternate ways for performing Left outer join in focus reporting Language.

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

Sandesh-
Have you looked in the Technical Documentation Library? See button above?

Hey thanks for the information. I will look at it.

I looked at the Document. But I am not sure what should I do in the below scenario.

Here is the master file.

FILENAME = EEISTAT, SUFFIX = FOC,$

SEGNAME = HDRSEG, SEGTYPE = S1, $
FIELDNAME = MLIEEID ,ALIAS=EEID , A7 ,FIELDTYPE=I,$ TRANSFE

SEGNAME = EEDATA, SEGTYPE = U, PARENT = HDRSEG ,$
FIELDNAME = EECLIENT ,ALIAS=CCLIENT , I5L ,FIELDTYPE=I,$ CABS CL
FIELDNAME = EELASTNAME ,ALIAS=LNAME , A25 ,$ LAST NAME
FIELDNAME = EEFIRSTNAME ,ALIAS=FNAME , A14 ,$ FIRST NAME
FIELDNAME = EEINITLCD ,ALIAS=INITIAL , A1 ,$ MIDDLE INITIAL
FIELDNAME = EENICKNAME ,ALIAS=NICKNM , A11 ,$ NICKNAME

SEGNAME = EECMT2, SEGTYPE = S1, PARENT = HDRSEG ,$
FIELDNAME = EECMTTYPE ,ALIAS= , A08 ,$ CMT TYPE
FIELDNAME = EEBLCMT2DT,ALIAS= , I6YMD ,$ BILL COMMENT ADD DATE
DEFINE EEBLCMT2SD/YMD = EEBLCMT2DT;
FIELDNAME = EECMTTXT ,ALIAS= , A60 ,$ EE CMT
$TYP = EEEMAIL1, EECMTTXT CONTAINS EE'S E-MAIL SIZE 50 CHAR


Description:- Earlier I had two mnemonics for EECMTTYPE 1. EEEMAIL 2. SVCNUMTYP

Now I have four MNEMONIC TYPES
1. COSTCNTR
2. DESTCOSTCT
3. EEEMAIL
4. SVCNUMTYP

code from program:-

TABLE FILE HOLD0B
PRINT MLIEEID AS EEID
ON TABLE HOLD AS HOLD00
END
-RUN
-*
-STEP75
-TYPE STEP75: USING COMBINED FILE GATHER DATA FROM EEISTAT

JOIN CLEAR *
JOIN EEID IN HOLD00 TO MLIEEID IN EEISTAT AS J75.

DEFINE FILE HOLD00
EESURVDT/YMD = &YMD;
EEEMAIL/A50 = IF EECMTTYPE EQ 'EEEMAIL1' THEN
SUBSTR(60,EECMTTXT,1,50,50,EEEMAIL) ELSE ' ';
EEBILLCMT40/A40 = SUBSTR(60,EEBILLCMT,1,40,40,EEBILLCMT40);
END
-*
TABLE FILE HOLD00
PRINT EEID
MLIEEID EELASTNAME EEFIRSTNAME EECLIENT
EEASGNCODE EERENTSTCD EESSN EESURVDT
EEEMAIL EENEWBUSFONE
EEBILLCMT40 EEEMPIDCD EEDEPTIDCD
BY EEID

What was happening earlier:-

HOLD00 file is getting the EEID from some other focus file and when I was joning it with MLIEEID of EEISTAT I was getting the EEEMAIL if its present else I was populating spaces.

As of now:- Since we have added two more mnemonics when I join HOLD00 and EEISTAT I am getting the record COSTCNTR or DESTCOSTCT ahead of EEMAIL hence even if the EEMAIL is present its not showing up in the report. I am getting the EEMAIL only if there is no COSTCNTR and DESTCOSTCT.

In this scenario what should I do Please help. And please let me know if you need any more information

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

Hi Sandesh,

The manuals/documentation as suggested in another topic regarding outer joins may be of interest as well.

Hope this helps.

Cheers,

Kerry

Thanks Kerry for the information. I think I can implement the same using a MATCH statement. Thanks a lot once again.

Happy new year

You are welcome Sandesh.

Happy New Year.