August 21, 2008, 04:30 AM
<ludo>Mutilple Join Question
Hi all,
After using the search funciton I haven't found yet the answer to my question.
I'll try to explain my problem. I have two table : produit and tiers.
The first one contain two fields and I need to retrieve data from these fields in the second table.
Table produit:
IDPrdoduit
Emetteur (IDTiers)
Structureur (IDTiers)
...
Table Tiers
IDTiers
Nom
code
Rating
..
My join is
JOIN EMETTEUR IN PRODUIT TO IDTIERS IN TIERS AS J0
JOIN STRCUTUREUR IN PRODUIT TO IDTIERS IN TIERS AS J1
I tried unsuccessfully to output the field rating corresponding to the field emetteur and the one correspondign to the structureur
TABLE FILE PRODUIT
PRINT
J0RATING
J1RATING
BY IDPRODUIT
END
But i have an error regarding the use of hte alias J0 and J1.
So how I could output the rating corresponding to my joined fields ?
Thx a lot
Ludo
August 21, 2008, 05:04 AM
nubido a search in help or on the forum for TAG...
basically you use the TAG command to alias(prefix) your fields whilst joining them but check the documentation for the exact syntax and usage.
August 21, 2008, 07:21 AM
Danny-SRLLudo,
Are you using Focus Files? If so, it should work. See the example below:
-* File ludo1.fex
TABLE FILE CAR
WRITE
MAX.MODEL
BY SEATS
ON TABLE HOLD AS TIERS FORMAT FOCUS INDEX SEATS
END
DEFINE FILE CAR
EM_SEAT/I3=IF MODEL CONTAINS '4 DOOR' THEN 5 ELSE IF MODEL CONTAINS '2 DOOR' THEN 4 ELSE 2;
ST_SEAT/I3=IF MPG GT 20 THEN 5 ELSE IF MPG GT 15 THEN 4 ELSE 2;
END
TABLE FILE CAR
PRINT EM_SEAT ST_SEAT
BY COUNTRY BY CAR
ON TABLE HOLD AS PRODUIT FORMAT FOCUS
END
JOIN EM_SEAT IN PRODUIT TO SEATS IN TIERS AS M_
JOIN ST_SEAT IN PRODUIT TO SEATS IN TIERS AS T_
TABLE FILE PRODUIT
PRINT M_MODEL T_MODEL
BY COUNTRY BY CAR
END
August 21, 2008, 07:59 AM
<ludo>Hi thx for your reply.
Is it only works with focus file ? In fact I'm using Oracle Tables ... shall I hold these tables onto a focus file as you did in you example ?
Ludo
August 21, 2008, 08:28 AM
nubidid you try the TAG? that should work regardless of source
August 21, 2008, 08:56 AM
<ludo>nubi,
I'm looking for it but I can find the right sample as the one of Danny-SRL.
August 21, 2008, 09:07 AM
Danny-SRLLudo,
Here is the TAG syntax:
-* File ludo2.fex
TABLE FILE CAR
WRITE
MAX.MODEL
BY SEATS
ON TABLE HOLD AS TIERS FORMAT FOCUS INDEX SEATS
END
DEFINE FILE CAR
EM_SEAT/I3=IF MODEL CONTAINS '4 DOOR' THEN 5 ELSE IF MODEL CONTAINS '2 DOOR' THEN 4 ELSE 2;
ST_SEAT/I3=IF MPG GT 20 THEN 5 ELSE IF MPG GT 15 THEN 4 ELSE 2;
END
TABLE FILE CAR
PRINT EM_SEAT ST_SEAT
BY COUNTRY BY CAR
ON TABLE HOLD AS PRODUIT FORMAT FOCUS
END
JOIN EM_SEAT IN PRODUIT TAG P TO SEATS IN TIERS TAG E AS M_
JOIN ST_SEAT IN PRODUIT TAG P TO SEATS IN TIERS TAG S AS T_
TABLE FILE PRODUIT
PRINT E.MODEL S.MODEL
BY COUNTRY BY CAR
END
August 21, 2008, 10:02 AM
<ludo>Allrigth it seems to work
Thanks
August 21, 2008, 11:15 AM
Leahquote:
J0RATING
J1RATING
As J0 and 1 weren't 'tags', (but you do need a period 'J0.' if they were), you can also use the table name followed by a period. BUT IN YOUR CASE you need the tags as you are doing a join on different fields to the same table