| Title: | FOCUS, from INFORMATION BUILDERS |
| Moderator: | ZAYIUS::BROUILLETTE |
| Created: | Thu Feb 19 1987 |
| Last Modified: | Mon May 05 1997 |
| Last Successful Update: | Fri Jun 06 1997 |
| Number of topics: | 615 |
| Total number of notes: | 1779 |
This is how I want to join 3 tables:
JOIN <field1> IN <table1> TO <field1> IN <table2> AS J1
JOIN <field2> IN <table1> TO <field2> IN <table3> AS J2
However <field2> is defined in the .MAS files of <table1> &
<table3> and is used only for reporting.
BEFORE issuing the joins, I can: >>TABLE FILE <table1_or_2>
>>?F
and see <field2> in the list of fields for either table. I
can also query on <field2>, I know it is a properly defined
field in the .MAS files!
The problem occurs when I issue the second JOIN statement..
I get the following error message:
(FOCUS370) FIELDNAME USED IN JOIN NOT FOUND IN FILE : <field2>
This seems very simple to understand... but as I said before
I know that <field2> is defined and available!
I would be happy to send the necessary files over to recreate
the problem on your system... Can anyone help me ???
%^{
thanks, Nancy
| T.R | Title | User | Personal Name | Date | Lines |
|---|---|---|---|---|---|
| 433.1 | Switch second join and do a match | AIMHI::CIONI_L | Fri Jul 12 1991 14:05 | 8 | |
How about keeping the first join and switchin the second to be the opposite way (reverse order of join) and do a MATCH between the two join tables? That's the way I would do it. LisaC | |||||
| 433.2 | picture this | MILPND::MADDEN | Tue Jul 16 1991 12:44 | 4 | |
do a CHECK FILE filename PICTURE Do you see the retrieval path
with all the fields you want? You refered to field2 defined in
.MAS, if you are referring to a DEFINED field as opposed to an actual
database field, you must be using v6.1.
| |||||
| 433.3 | Joins on Defined Fields? | MILPND::DANDREA | lather rinse repeat; infinite loop | Mon Jul 22 1991 16:09 | 16 |
Was on Vacation but Unfortunately the problem waited for me to return!
Re. -1, >>CHECK FILE table1 PICTURE shows the first join perfectly
>>CHECK FILE table2 PICTURE gives the same error...
...FIELDNAME USED IN JOIN NOT FOUND IN FILE
I am using version 6.1.0 Can joins be based on defined fields????????
(Regarding .1, I tried to change the order of the joins and use MATCH
but was unsuccessful, the required final output limits my use of
MATCH but thank you for the suggestion.)
HELP??? Thx, NLD
| |||||
| 433.4 | will the real code please stand up | MILPND::MADDEN | Mon Jul 22 1991 18:44 | 1 | |
need actual join code and masters. please post. | |||||
| 433.5 | code & masters | MILPND::DANDREA | Lighten Up | Thu Aug 08 1991 16:45 | 76 |
>> join NEW_ORG_CD in REDBK_BD to NEW_ORGNZN_CD in FLAT_ORG as j1
>> table file REDBK_BD
(FOC370) FIELDNAME USED IN JOIN NOT FOUND IN FILE : NEW_ORG_CD
below please file the master files for REDBK_BD and FLAT_ORG. As
you can see, NEW_ORG_CD does exist in REDBK_BD... Maybe JOINS cannot
be performed over two defined fields???????? Any help will be
truly appreciated. Thanks.
Nancy
REDBK_BD.MAS
FILE=REDBK_BD, SUFFIX=RDB,
SEGNAME=REDBK_BD, SEGTYPE=S0,$
FIELD=ORG_UNIT_CD ,ALIAS=ORG_UNIT_CD ,
USAGE=A3 ,ACTUAL=A3 ,MISSING=OFF,$
FIELD=ORG_TYPE_CD ,ALIAS=ORG_TYPE_CD ,
USAGE=A1 ,ACTUAL=A1 ,MISSING=OFF,$
FIELD=SUBMSN_MO ,ALIAS=SUBMSN_MO ,
USAGE=P6 ,ACTUAL=I4 ,MISSING=OFF,$
FIELD=SUBMSN_YR ,ALIAS=SUBMSN_YR ,
USAGE=P6 ,ACTUAL=I4 ,MISSING=OFF,$
FIELD=FYR ,ALIAS=FYR ,
USAGE=P6 ,ACTUAL=I4 ,MISSING=OFF,$
FIELD=REDBK_LNITM_CD ,ALIAS=REDBK_LNITM_CD ,
USAGE=A4 ,ACTUAL=A4 ,MISSING=OFF,$
FIELD=BDGT_FRCST_TYPE_CD ,ALIAS=BDGT_FRCST_TYPE_CD ,
USAGE=A1 ,ACTUAL=A1 ,MISSING=OFF,$
FIELD=ENTRY_TYPE ,ALIAS=ENTRY_TYPE ,
USAGE=A3 ,ACTUAL=A3 ,MISSING=OFF,$
FIELD=SYS_FUNCN_CD ,ALIAS=SYS_FUNCN_CD ,
USAGE=A10 ,ACTUAL=A10 ,MISSING=ON ,$
FIELD=EMPLYE_CORP_BADGE_NO ,ALIAS=EMPLYE_CORP_BADGE_NO ,
USAGE=A6 ,ACTUAL=A6 ,MISSING=ON ,$
FIELD=LAST_MANTND_DT ,ALIAS=LAST_MANTND_DT ,
USAGE= YMD ,ACTUAL= DATE ,MISSING=ON ,$
FIELD=Q1_AMT ,ALIAS=Q1_AMT ,
USAGE=P15.2 ,ACTUAL=I8.2 ,MISSING=ON ,$
FIELD=Q2_AMT ,ALIAS=Q2_AMT ,
USAGE=P15.2 ,ACTUAL=I8.2 ,MISSING=ON ,$
FIELD=Q3_AMT ,ALIAS=Q3_AMT ,
USAGE=P15.2 ,ACTUAL=I8.2 ,MISSING=ON ,$
FIELD=Q4_AMT ,ALIAS=Q4_AMT ,
USAGE=P15.2 ,ACTUAL=I8.2 ,MISSING=ON ,$
DEFINE NEW_ORG_CD/A4 = ORG_UNIT_CD||ORG_TYPE_CD;$
FLAT_ORG.MAS
FILE=FLAT_ORG, SUFFIX=RDB,
SEGNAME=FLAT_ORG, SEGTYPE=S0,$
FIELD=LVL_0_ORG_UNIT_CD ,ALIAS=LVL_0_ORG_UNIT_CD ,
USAGE=A3 ,ACTUAL=A3 ,MISSING=ON ,$
FIELD=LVL_0_ORG_TYPE_CD ,ALIAS=LVL_0_ORG_TYPE_CD ,
USAGE=A1 ,ACTUAL=A1 ,MISSING=ON ,$
FIELD=LVL_1_ORG_UNIT_CD ,ALIAS=LVL_1_ORG_UNIT_CD ,
USAGE=A3 ,ACTUAL=A3 ,MISSING=ON ,$
FIELD=LVL_1_ORG_TYPE_CD ,ALIAS=LVL_1_ORG_TYPE_CD ,
USAGE=A1 ,ACTUAL=A1 ,MISSING=ON ,$
FIELD=LVL_2_ORG_UNIT_CD ,ALIAS=LVL_2_ORG_UNIT_CD ,
USAGE=A3 ,ACTUAL=A3 ,MISSING=ON ,$
FIELD=LVL_2_ORG_TYPE_CD ,ALIAS=LVL_2_ORG_TYPE_CD ,
USAGE=A1 ,ACTUAL=A1 ,MISSING=ON ,$
FIELD=ORG_UNIT_CD ,ALIAS=ORG_UNIT_CD ,
USAGE=A3 ,ACTUAL=A3 ,MISSING=ON ,$
FIELD=ORG_TYPE_CD ,ALIAS=ORG_TYPE_CD ,
USAGE=A1 ,ACTUAL=A1 ,MISSING=ON ,$
DEFINE NEW_ORGNZN_CD/A4 = ORG_UNIT_CD||ORG_TYPE_CD;$
| |||||
| 433.6 | half a loaf is better than none | MILPND::MADDEN | Tue Aug 13 1991 19:23 | 6 | |
You can only join a defined field from the host file like
JOIN NEW_ORG_CD WITH ORG_UNIT_CD IN REDBK_BD TO dbfield IN FLAT_ORG
one solution is add the field NEW_ORG_CD to FLAT_ORG
Good luck
Richard
| |||||