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 suggestionThis message has been edited. Last edited by: <Kathryn Henning>,
WebFOCUS Version 8.2.0.1 Windows Server 2012
WebFOCUS Version 5.3.3 Windows 2000 Oracle 8i
June 11, 2013, 11:11 AM
Doug
Have you tried performing this JOIN in the GUI? If not, please try that, it's really a quite fine tool and accurate.
June 11, 2013, 12:37 PM
ExoR
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,