Hi, All Don't Blame me for not Finding. I searched, but I'm a poor lonesome etc ... 2 (Master) Files have to be Joined. The Source File is a FIX file : FRIBSEL The Target File is a SQLDS Multi-Segment file. TIECTR being the First Segment. 2 Fields of the Target File have to be reached : TIECTR.COD_FED and TIECTR.REF_CTR In The Source File, the Fields have to be Defined before the Join can take Place. FED_SEL and CTR_SEL are the Sources of this Virtual(?) Join --- 1st Try --- ---------------------------------------------------------------------- FOCUS 7.3.4 17.51.54 10/08/2009 CHTIE12 LINE 5 1067.05
JOIN FED_SEL AND CTR_SEL WITH CPT_SEL IN FRIBSEL (INF32073) ERROR AT OR NEAR LINE 5 IN PROCEDURE CHTIE12 FOCEXEC (FOC199) COMBINAISON DE WITH ET AND ET INVALIDE DANS JOIN: WITH
TO ALL TIECTR.COD_FED AND TIECTR.REF_CTR IN DMVTTLCR UNKNOWN FOCUS COMMAND TO (INF32074) BYPASSING TO END OF COMMAND END The AND and WITH are not Correctly Set by me So I Try another way : --- 2nd Try --- ---------------------------------------------------------------------- FOCUS 7.3.4 17.24.11 10/08/2009 CHTIE12 LINE 7 1067.05
JOIN FILE FRIBSEL AT CPT_SEL TO ALL FILE DMVTTLCR AT TIECTR.COD_FED WHERE FED_SEL EQ TIECTR.COD_FED ; WHERE CTR_SEL EQ TIECTR.REF_CTR ; END (INF32073) ERROR AT OR NEAR LINE 12 IN PROCEDURE CHTIE12 FOCEXEC (FOC258) NOM DE CHAMP OU ELEMENT DE CALCUL NON RECONNU: FED_SEL Focus Does not Understand he Has to Wait for the Define ... Please Help Cordially and FocuselyThis message has been edited. Last edited by: Kerry,
Focus Mainframe 7.6.11 Dev Studio 7.6.11 and !!! PC Focus, Focus for OS/2, FFW Six, MSO
October 08, 2009, 12:50 PM
GinnyJakes
Well, I don't know why the first way didn't work though you didn't post your define.
At any rate, what you are doing is called a heterogeneous join and by definition that is pretty inefficient. You might be better off extracting the columns you need from file 2, the SQLDS one, with a concatenated key and hold the results in a temp focus file with an index on the concatenated key. From the first file then, hold the contents with its concatenated key and then join flat to FOCUS. Does that make sense?
I'm assuming that all of your field formats are matching up. Otherwise you'll have to specify the formats of the fields that you are joining from and to. Also, if FED_SEL and STR_SEL are not real fields in your fixed file FRIBSEL then you've left out the DEFINE step.
The syntax is pretty straightforward - just like it's described in the manual for define-based joins:
JOIN FED_SEL WITH CPT_SEL AND CTR_SEL WITH CPT_SEL IN FRIBSEL TO ALL COD_FED AND REF_CTR IN DMVTTLCR AS JOIN0 END DEFINE FILE FRIBSEL FED_SEL/format=expression; (to match COD_FED) CPT_SEL/format=expression; (to match REF_CTR) END TABLE FILE FRIBSEL ..
Regards,
Darin
In FOCUS since 1991 WF Server: 7.7.04 on Linux and Z/OS, ReportCaster, Self-Service, MRE, Java, Flex Data: DB2/UDB, Adabas, SQL Server Output: HTML,PDF,EXL2K/07, PS, AHTML, Flex WF Client: 77 on Linux w/Tomcat
October 09, 2009, 06:26 AM
Baillecl
Thanks for the answers,
I'd like to make a 2 point message 1) DB2 is frightfully an 'Owner's Solution' who thinks that in his World HIS Rules are the Universal Law How can Focus ( whose DB2 Access Motor is Great) can play an heterogeneous game "Extract ... with a concatenated key" : Please Help 2) I retried trying to be as close as (precious) Darin's advice as I could This point is at the end oft the message
1) My question towards DB2 ( The choice of my Enterprise and a Safe Production SGBD) Outside of the DB2 world, I process with Hold Files ( seldom Format Focus) And I marry this HOLD file through Join (often) and Match (sometimes) My Enterprise, as many, do not allow "ON TABLE HOLD AS TOTO FORMAT SQLDS" which would turn me in the DB2 marvellous(!) World ( and no longer heterogeneous ) In so far as I Know &DB2.&TABLE ( like MVS temorary files) do not exist So, the best way I know to induce FIX world in DB2 Word is WHERE TOTO IN FILE FSELTOTO But I don't know how to play the Game With Concatanated Key
2) Everything makes me believe that Focus does not appreciate our way of combining WITH and AND in the JOIN ---------------------------------------------------------------------- FOCUS 7.3.4 09.54.58 10/09/2009 CHTIE12 LINE 12 1067.05 JOIN CLEAR *
JOIN FED_SEL WITH CPT_SEL AND CTR_SEL WITH CPT_SEL IN FRIBSEL (INF32073) ERROR AT OR NEAR LINE 13 IN PROCEDURE CHTIE12 FOCEXEC (FOC376) ERREUR DE SYNTAXE OU ELEMENT ABSENT DANS LA COMMANDE JOIN: TO ALL UNKNOWN FOCUS COMMAND TO (INF32074) BYPASSING TO END OF COMMAND TIECTR.COD_FED AND TIECTR.REF_CTR IN DMVTTLCR END
DEFINE FILE FRIBSEL FED_SEL/A02 WITH CPT_SEL = 'xx' ; CTR_SEL/A50 = RIB_SEL ; END And of Course, Focus doesn't recognize any linked Field TABLE FILE FRIBSEL BY FED_SEL BY RIB_SEL PRINT TIECTR.COD_FED (INF32073) ERROR AT OR NEAR LINE 28 IN PROCEDURE CHTIE12 FOCEXEC (FOC003) NOM DE CHAMP ABSENT DU DICTIONNAIRE TIECTR.COD_FED (INF32074) BYPASSING TO END OF COMMAND
I can swear that in (MASTER) File DMVTTLCR, TIECTR is the First Segment SEGNAME=TIECTR,SEGTYPE=S0,$ FIELD=COD_FED ,COD_FED ,A2 ,A2 ,MISSING=OFF,$ .../... FIELD=REF_CTR ,REF_CTR ,A50 ,A50 ,MISSING=OFF,$
I also must say that COD_FED can be found in Other Segments too
Of course, if FED_SEL and CTR_SEL were real Fields ( no WITH, no Define ) Everything would be OK. And If I preferred Crystal Report to Focus, You wouldn't be bored by me ... Joining out of a Define is Great, and I Love it ...
Focus Mainframe 7.6.11 Dev Studio 7.6.11 and !!! PC Focus, Focus for OS/2, FFW Six, MSO