Hi all,
I'm trying to make a huge join between tables, those here under are some examples of them.

   
T_ORDER						
YEAR	MONTH	DAY	TYPE_IN	TYPE_OUT	CONTRACT	QTY
2013	6	1	A	N/A		XXXX		10
2013	6	1	N/A	B		XXXX		2
2013	6	1	N/A	A		YYYY		4
2013	6	1	C	N/A		YYYY		6
2013	6	2	N/A	A		ZZZZ		8
2013	6	2	F	N/A		ZZZZ		9

----------------------------------------------------------------------------

T_COMPANY	
CONTRACT	COMPANYNAME
XXXX		Xx Xxxxxxx
YYYY		Yyyyyy
ZZZZ		Zz & Zz
CCCC		C. Cccc
FFFF		Ffff Fffff

----------------------------------------------------------------------------

T_SHIP				
YEAR	MONTH	DAY	TYPE	SHIPPED
2013	6	1	A	12
2013	6	1	B	-5
2013	6	1	C	 8
2013	6	2	A	-7
2013	6	2	F	 9

----------------------------------------------------------------------------

T_TYPEDESC	
TYPE	DESCRIPTION
A	Aaaaaaa
B	Bbbbbbb
C	Cccccc
D	Ddddddd
E	Eeeeeee
F	Fffffff
	
----------------------------------------------------------------------------

THE_TABLE_i_would_like									
YEAR	MONTH	DAY	TYPE_IN	TYPE_OUT	TYPE	CONTRACT	QTY	COMPANYNAME	SHIPPED	DESCRIPTION
2013	6	1	A	N/A		A	XXXX		10	Xx Xxxxxxx	12	Aaaaaaa
2013	6	1	N/A	B		B	XXXX		 2	Xx Xxxxxxx	-5	Bbbbbbb
2013	6	1	N/A	A		A	YYYY		 4	Yyyyyy		12	Aaaaaaa
2013	6	1	C	N/A		C	YYYY		 6	Yyyyyy		 8	Cccccc
2013	6	2	N/A	A		A	ZZZZ		 8	Zz & Zz		-7	Aaaaaaa
2013	6	2	F	N/A		F	ZZZZ		 9	Zz & Zz		 9	Fffffff


----------------------------------------------------------------------------

in T_ORDER i have defined a field
TYPE/A12= IF TYPE_IN EQ 'N/A' THEN TYPE_OUT ELSE TYPE_IN;

then i do the first JOIN
JOIN TYPE WITH TYPE_IN IN T_ORDER TAG a TO ALL TYPE IN T_TYPEDESC TAG B AS ORDESC

end here come troubles...
the defined filed TYPE is TYPE_OUT or TYPE_IN, so WITH have to be TYPE_OUT or TYPE_IN?
if i continue with this join and do


TABLE FILE T_ORDER
PRINT TYPE DESCRIPTION
ON TABLE HOLD AS HORDESC
END

the result is an hold file with 100 rows more or less
but the original tables are of 43000 rows for T_ORDER and 9700 rows for T_TYPEDESC

TYPE in T_ORDER is A12
TYPE in T_TYPEDESC is A12

if i join them with a MATCH FILE everything will work fine and i have an hold of 43000 rows with the DESCRIPTION


what is the problem?

then how i can join other tables over the first?
i've tried to do like follow

JOIN TYPE WITH TYPE_IN IN T_ORDER TAG a TO ALL TYPE IN T_TYPEDESC TAG B AS ORDESC
JOIN CONTRACT IN T_ORDER TAG a TO ALL CONTRACT IN T_COMPANY TAG B AS ORDCOM

TABLE FILE T_ORDER
PRINT TYPE DESCRIPTION COMPANYNAME
ON TABLE HOLD AS HORDESC
END

The result is:
(FOC003) THE FIELDNAME IS NOT RECOGNIZED: COMPANYNAME


So i'm stuck
with the command MATCH FILE .... i can "JOIN" every one of the above tables to create the "THE_TABLE_i_would_like"


hoping to be clear, thanks for any suggestion

This message has been edited. Last edited by: <Kathryn Henning>,

Have you tried performing this JOIN in the GUI? If not, please try that, it's really a quite fine tool and accurate.

no i had not tried the GUI

but now yes and it's give me another error

 
-*COMPONENT=Join_J001
JOIN TYPE WITH T_ORDER.T_ORDER.AREA IN T_ORDER
TO MULTIPLE T_TYPEDESC.T_TYPEDESC.TYPE IN T_TYPEDESC TAG J001 AS J001
END

i've not mentioned AREA in T_ORDER before because i'm not interested in Area in the table I need, but WF GUI has put the AREA field after WITH command.

and this is the error
(FOC376) SYNTAX ERROR OR MISSING ELEMENT IN JOIN/COMBINE COMMAND:

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