Hello All,

I'm getting surprising sort results if I use a DEFINE based JOIN with a field that has the same name in the host file and the cross-referenced file. I'm not sure if this is a bug or a misunderstanding on my part as to how DEFINE based JOINs work. I can demo this issue with the CAR file.

  

-* SET UP THE EXAMPLE.
SET ASNAMES=ON
-RUN

USE
/udisk/car.foc
END
-RUN

-* Create a file with a subset of all the COUNTRY fields (ITALY).
TABLE FILE CAR
SUM
 SALES AS TOT_SALES
BY COUNTRY
WHERE COUNTRY EQ 'ITALY'
ON TABLE HOLD AS HOLD01 FORMAT FOCUS INDEX COUNTRY
END
-RUN

-* Rename the COUNTRY field in the CAR file to PLACE.
TABLE FILE CAR
PRINT
 CAR
BY COUNTRY AS PLACE
ON TABLE HOLD AS HOLD02 FORMAT ALPHA
END
-RUN

-* Done with the setup. Now use a DEFINE based join to rename PLACE to COUNTRY in the
-* host file. This creates an ambiguous situation where both the host file and cross-
-* referenced file contain a field with the name COUNTRY. When you sort the data using
-* the COUNTRY field, WebFOCUS chooses to sort using the field in the cross-referenced
-* file instead of the DEFINEd field in the host file. 
-*
-* I would have expected WebFOCUS to resolve the ambiguity by choosing the field in the
-* host file since that is what happens with other JOINs. A co-worker modified the master
-* file for the host file, putting the define in the master file and the sort worked as 
-* expected, so the problem only seems to occur when you use "DEFINE FILE filename".

-*
-* Sort field ambiguity resolved - Surprise! COUNTRY in HOLD01 chosen.
JOIN CLEAR
JOIN COUNTRY WITH PLACE IN HOLD02 TO COUNTRY IN HOLD01
DEFINE FILE HOLD02
COUNTRY/A10 = PLACE;
END
TABLE FILE HOLD02
HEADING
"Sort field ambiguity resolved - Surprise! COUNTRY in HOLD01 chosen."
" "
PRINT
 CAR
 TOT_SALES
BY COUNTRY
END

we see the following results (only the country name ITALY shows):

  
SYSPRINT.HTS
  Sort field ambiguity resolved - Surprise! COUNTRY in HOLD01 chosen.

  COUNTRY     CAR               TOT_SALES
  -------     ---               ---------
              JAGUAR                    0
              JENSEN                    0
              TRIUMPH                   0
              PEUGEOT                   0
              DATSUN                    0
              TOYOTA                    0
              AUDI                      0
              BMW                       0
  ITALY       ALFA ROMEO            30200
              MASERATI              30200
SYSPRINT.SCR

I would have expected to see the following:

  
SYSPRINT.HTS
  Ambiguity resolved - Expected ... COUNTRY in host chosen.

  COUNTRY     CAR               TOT_SALES
  -------     ---               ---------
  ENGLAND     JAGUAR                    0
              JENSEN                    0
              TRIUMPH                   0
  FRANCE      PEUGEOT                   0
  ITALY       ALFA ROMEO            30200
              MASERATI              30200
  JAPAN       DATSUN                    0
              TOYOTA                    0
  W GERMANY   AUDI                      0
              BMW                       0
SYSPRINT.SCR

So is this a bug, or is there something about the internal logic of define based joins that I don't understand?

We've tested this code on WebFOCUS 7.1, 7.7 and the behaviour is the same for us.
-James

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

Maybe it exists but I've never seen a written rule as to how [Web]FOCUS would resolve field name ambiguities and, being as paranoid as I am, I wouldn't rely on the assumption that host table field names take precedence over x-ref ones *unless* it's documented (as I said, I've never seen it).

I find your experiment very interesting for the purpose of testing. In an actual Production environment you would obviously take measures to avoid those apparently unreliable results by getting rid of any field name ambiguities; that's what the MASTER.SEGMENT. prefix is for.

We tried to explicity resolve the ambiguity with the sort as follows:

  
BY HOLD02.COUNTRY

and got the same surprising result.
-James

I would open a case with TechSupport, this looks like a bug.

I guess you have a workaround though, don't use the same column name.

If, for whatever reason, you absolutely have to do things this way, then this code worked for me in the final table file:

JOIN CLEAR
JOIN LEFT.COUNTRY WITH PLACE IN HOLD02 TAG LEFT TO COUNTRY IN HOLD01
DEFINE FILE HOLD02
   LEFT.COUNTRY/A10 = PLACE ;
END
TABLE FILE HOLD02
HEADING
"Qualifying COUNTRY throughout the code ensures that we get proper sort field"
" "
   PRINT
      CAR
      TOT_SALES
   BY LEFT.COUNTRY   
END
  

BTW, nice repro & explanation.

EricH

I'm not very surprised.
I think it has to do with the WITH statement which probably causes the DEFINE field to be in a lower place than the JOINED field on the list of fields.
I know it seems strange but this is what my intuition (of 30 years) tells me.
For whatever it is worth...

quote:

resolving sort field ambiguity with DEFINE based JOINs

I have opened a case with IBI.