6 Replies Latest reply: Jun 18, 2014 10:30 AM by Jan Heinrichsdorff RSS

    left keep

      Hi experts

       

      I would like to join the following 3 tables: job, jleistg, divbez. I managed to join job and jleistg in a way that if there is no value in "datum_auftrag" all other values that are linked are not shown. Now I would like to additionally link divbz in a way that if "Jobkriterium" = 'Internes Projekt all the linked values in all tables are not shown. How do I do this? I do not seem to get the link (left keep) right...

       

       

      SQL SELECT bez as Jobkriterium,

          beznr as Jobkritnr

      FROM EASY.divbez where Jobkriterium <> 'Internes Projekt';

       

      left Keep (divbez)

       

      SQL SELECT "job_nummer",

          jobnr,

          "datum_auftrag",

          Year ("datum_auftrag") as Jahr,

          Month ("datum_auftrag") as Monat,

          jobkategorie

      FROM EASY.job where "datum_auftrag" is not null;

       

      left Keep (job)

       

      SQL SELECT "auf_datum",

          "auf_fw",

          "auf_kurs",

          "auf_wert",

           "kv_fw",

          "kv_kurs",

          "kv_wert",

          bez as Leistungstext,

          jobnr,

          "auf_anzahl",

          ltyp,

          storniert,

          optional,

          lartnr 

      FROM EASY.jleistg;

       

      Thank you very much for your help!

        • Re: left keep
          Gysbert Wassenaar

          The fields Jobkriterium and Jobkritnr don't exist in the other tables, so your first left keep does not do anything at all. You're merely creating a cartesian product between the first table and the other two.

          • Re: left keep

            Hi, Thank you for your reply. You are right, the fields in divbez do not exist in other fields. I link them through another table. I attached a screenshot of my table architecture. I generally have problems with the "Left keep" when I want to connect more than two tables. I also tried to fuse the ables using "outer join". This also did not do the job. The "feft keep" works perfectly for joining "job" and "jleistg". All values that are associated to a field that have no "auf_datum" are exlcuded. I would like to do the same with "Jobkriterium". All values that are ssociated to an entry in "Jobkriterium" lis 'Internes Projekt' should be excluded. Thank you so much for your help!ScreenShot107.jpg