I have a hold file H_INV which holds some basic information from 3 conditional file selections.
I need to join the hold file to other files get the rest of the information for my report, but not sucessful. Can someone please look at the following dump and point out why? Thanks alot.
Hua

-SET &S_ENTITY = IF 'ALL' EQ ' ' THEN 'ALL' ELSE UPCASE(10,ALL,'A10');
 -SET &S_NUMBER = IF 'ALL' EQ ' ' THEN 'ALL' ELSE UPCASE(15,ALL,'A15');
 -SET &T_FDATE =  IF '02/01/09'  EQ ' ' THEN 0
 -                                                 ELSE '1' | EDIT(02/01/09,'$$$$$$99')|EDIT(02/01/09,'99')|EDIT(02/01/09,'$$$99');
 -SET &T_TDATE =  IF '02/06/09'  EQ ' ' THEN '1' | 090211
 -                                                 ELSE '1' | EDIT(02/06/09,'$$$$$$99')|EDIT(02/06/09,'99')|EDIT(02/06/09,'$$$99');
 -SET &S_TYPE =   IF 'ALL            ' EQ 'ALL' THEN 'INVOICE' ELSE 'INVOICE';
 -* -----------------------------------------------------------------------
 -* Save the invoice transaction IDs of the given selection to a hold file
 -* -----------------------------------------------------------------------
 -* 1) select by cheque number
 -IF 'INVOICE' NE 'CHEQUE' THEN GOTO SEL_PO
 -SEL_PO
 -IF 'INVOICE' EQ 'INVOICE' THEN GOTO SEL_INV
 -SEL_INV
 TABLE FILE YAA4REP
 PRINT A4ASCD A4AKNB A4CANB A4C0VA A4C1VA A4ADCD A4PLNA A4AGDT A4AHDT A4ATVA A4SFCD A4LXNB A4L8NB A4FJDT
 WHERE 'ALL       ' EQ 'ALL' OR A4ADCD EQ 'ALL       ';
 WHERE ('ALL            ' EQ 'ALL' AND A4FJDT GE 1090201 AND A4FJDT LE 1090206)  OR A4PLNA EQ 'ALL            ';
 ON TABLE HOLD AS H_INV FORMAT FOCUS INDEX A4ASCD A4AKNB A4SFCD A4LXNB A4L8NB
 END
 -RUN
 1
 0 NUMBER OF RECORDS IN TABLE=     1973  LINES=   1973
 0
 -SEL_DONE
 -*--------------------------------------------------------------------------------------------------
 -* H_INV has all the neccessary invoices, now join the YAD9REP & UARNCPP to get the cheque#/PO/Recv#
 JOIN CLEAR *
 JOIN
 LEFT_OUTER H_INV.H_INV.A4SFCD AND H_INV.H_INV.A4LXNB
 AND H_INV.H_INV.A4L8NB IN H_INV TO MULTIPLE YAD9REP.YAD9REP.D9F7CD
 AND YAD9REP.YAD9REP.D9LXNB AND YAD9REP.YAD9REP.D9L8NB IN YAD9REP  AS J4
 END
 JOIN
 LEFT_OUTER H_INV.H_INV.A4ASCD AND H_INV.H_INV.A4AKNB IN H_INV
 TO MULTIPLE UARNCPP.UARNCPP.RNASCD AND UARNCPP.UARNCPP.RNAKNB IN UARNCPP
 AS J5
 END
 TABLE FILE H_INV
 HEADING
 ""
 FOOTING
 ""
 ON TABLE NOTOTAL
 END
 (FOC370) THE FIELDNAME USED IN JOIN CANNOT BE FOUND IN THE FILE:
 H_INV.H_INV.A4SFCD
 BYPASSING TO END OF COMMAND



  

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

quote:

ON TABLE HOLD AS H_INV FORMAT FOCUS INDEX A4ASCD A4AKNB A4SFCD A4LXNB A4L8NB
. . .
JOIN
LEFT_OUTER H_INV.H_INV.A4SFCD AND H_INV.H_INV.A4LXNB . . .

The qualification is incorrect. The segment names produced by HOLD FORMAT FOCUS are SEG01 etc. Correct (or remove) the filename.segname. from the fields in the join.

Thank you Jack. I fixed it. It went further, but the result is not ideal, WF changed my join from OUTTER JOIN to INNER JOIN and why is that?

 JOIN
 LEFT_OUTER A4SFCD AND A4LXNB
 AND A4L8NB IN H_INV TO MULTIPLE D9F7CD
 AND D9LXNB AND D9L8NB IN YAD9REP  AS J4
 END 

(FOC32456) ANSI-COMPLIANCE ENFORCED BY CHANGING OUTER JOIN TO INNER
0 NUMBER OF RECORDS IN TABLE= 687 LINES= 687



Hua

quote:

(FOC32456) ANSI-COMPLIANCE ENFORCED BY CHANGING OUTER JOIN TO INNER

Does the MFD of YAD9REP (or UARNCPP) include any joins?

If you do ? 32456

you get:

quote:

FOC32456) ANSI-COMPLIANCE ENFORCED BY CHANGING OUTER JOIN TO INNER
A left outer join in the root-path of an inner join has been changed into an inner join.

which may help.

[/QUOTE]

Does the MFD of YAD9REP (or UARNCPP) include any joins?[/QUOTE]

No, both are simple physical files.

I had 2 joins initially, to simplify the problem, I kept only one join which is YAD9REP.

H_INV to YAD9REP is 1:n where n= 0, 1
H_INV to UARNCPP is 1:m where m>0 relationship

Francis, I tried ? 32456 still don't get it.

I massaged the key values to non-blank or non=zero before join, still give me the inner join.

 DEFINE FILE H_INV
A4SFCDX/A10=IF A4SFCD EQ ' ' THEN 'NOTFOUND' ELSE A4SFCD;
A4LXNBX/P8=IF A4LXNB EQ 0 THEN 9999999 ELSE A4LXNB;
A4L8NBX/P6=IF A4L8NB EQ 0 THEN 999999 ELSE A4L8NB;
END
TABLE FILE H_INV
PRINT
     FOCLIST
     A4ASCD
     A4AKNB
     A4CANB
     A4C0VA
     A4C1VA
     A4ADCD
     A4PLNA
     A4AGDT
     A4AHDT
     A4ATVA
     A4SFCD
     A4LXNB
     A4L8NB
     A4FJDT
     A4SFCDX
     A4LXNBX
     A4L8NBX
ON TABLE NOTOTAL
ON TABLE HOLD AS H_INV2 FORMAT FOCUS INDEX A4ASCD A4AKNB A4SFCDX A4LXNBX A4L8NBX
END
JOIN CLEAR *
JOIN
 LEFT_OUTER A4SFCDX AND A4LXNBX
 AND A4L8NBX IN H_INV2 TO MULTIPLE D9F7CD
 AND D9LXNB AND D9L8NB IN YAD9REP  AS J4
 END
JOIN
 LEFT_OUTER A4ASCD AND A4AKNB IN H_INV2 
 TO MULTIPLE RNASCD AND RNAKNB IN UARNCPP
 AS J5
END
TABLE FILE H_INV2
PRINT
     A4ASCD
     A4AKNB
     A4CANB
     A4C0VA
     A4C1VA
     D9LQNB
	 A4SFCDX
	 A4LXNBX
	 A4L8NBX
HEADING
""
FOOTING
""
ON TABLE NOTOTAL
END
 

quote:

A left outer join in the root-path of an inner join has been changed into an inner join

Do you have another join?

quote:

Do you have another join?

It doesn't seem matter, because I tried 1 or 2 joins the result is the same.

I tried omitting the field D9LQNB which is from the YAD9REP to show fields only in H_INV, then all 1973 records showed as if there is no join.

Hua

Try some reading:

Specifying an Inner or Left Outer Join

Francis,

Thank you very much for direct me to the link.
All I missed is SET ALL=ON. I seen it somewhere, just have to remember to use it.

Thanks again to you & Jack. Have a great day!

Hua

"The SET ALL command globally determines how all joins are implemented.
If the SET ALL=ON command is issued, all joins are treated as outer joins.
With SET ALL=OFF, the default, all joins are treated as inner joins."

(So your Outer or Inner keywords serve absolutely no function, unless you've set ALL=PASS?!)


If SET ALL=OFF was the reason for changing Outer to Inner, what does that have to do with "ANSI-COMPLIANCE"?

(FOC32456) ANSI-COMPLIANCE ENFORCED BY CHANGING OUTER JOIN TO INNER  

Jack, I was quick to jump to conclusion when I see the desired records shown on the report. I checked the background source code, this message still there.

Hua

are both databases Focus?

Are you sure you can join two tables (not being SQL) with more than one field?
As far as I know this can only work with two (or more) SQL like tables.
When using FOCUS databases you have to create a combined key from the two or more fields you have now.

quote:

are both databases Focus?

Frank, the host file H_INV is a FOCUS hold file
YAD9REP & UARNCPP are both DB2 files

Hua

Since what you describe is a hetergeneous join, I'd recommend that you join the 2 DB2 tables and create a HOLD FORMAT FOCUS and index the key.

Then join the FOCUS file to it.

That might eliminate some of your problems.

Ginny,

I can not do as you sugguested, because they don't mixed, they can only linked by invoice(H_INV)
YAD9REP - Possibly holding the cheque# of an invoice,
UARNCPP - PO info which invoice is created for.

Good night, and good day for some of you.

Hua

Then try to do it the other way around: create the H_INV file as a hold format db2.
Then let db2 take care of the joins - two advantages of this: 1. it becomes a homogenous join and 2. it will be much faster.
You do need to be allowed to create a db2 table as well as delete it on the fly, the last because it is highly recommended to remove the temp table after use, which is not done automatically, as with other hold formats like alpha or focus.

quote:

a hetergeneous join

My head was quite wrapped up yesterday. This is not about multiple joins because I was experimenting a single join and the message was there.

This is what I observed:
1) I do get the outer-join result when SET ALL=ON, regardless of the message(FOC32456), as long as the fields in x-ref file are not used in the WHERE clauses.
2) If any of the hosting records can not be found in x-ref file, then the message appears in the background.
3) I changed the join-fields to non-blank/non-zero might help a bit, according to this. (I still don't get it)

I am closing this case now, but certainly welcome any suggestions as why this is happening.


Thank you all for helping.

Hua

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

Apparently, in v7.7 a new setting is available:

SET ALL=SQL

Though I just don't understand why "a setting... can be added to your procedure so the left outer join stays a left outer join".