2 Replies Latest reply: Mar 23, 2012 3:08 PM by Jonathan Dienst RSS

    Where Exists clarification

      I  understand how the where exists works, but what I have now seen for the firsttime is a where exists with 2 fields in it, Where exists (field1,field2).  Can someone tell me what the where exists is doing below?

       

      Material_MATNR is a field in the Fact table and then the 1st block of code below uses it to create ActiveMaterial

      Left Join (Fact)
      LOAD distinct
      %KEY_VBRP,
      If(isNull(Material_SMATN),Material_MATNR,Material_SMATN) & '/' &[SalesOrganization_VKORG] as %ACTIVEMATNR%VKORG,
      If(isNull(Material_SMATN),Material_MATNR,Material_SMATN) as ActiveMaterial
      Resident Fact;

       

      Now Fact table has Material_MATNR and ActiveMaterialand then the code below comes in next. So is it saying, load this code where Material_MATNR and ActiveMaterialexists while it loads Material_MATNR as ActiveMaterialat the same time?

       

      Material:
      LOAD
      Material_MATNR as ActiveMaterial,
      [Language Key_SPRAS],
      [Material Description_MAKTX]
      FROM\\ghos2280\repository$\03.MASTER\MATERIAL\DATA\MAKT.qvd (qvd)
      WHERE EXISTS (ActiveMaterial,Material_MATNR);

        • Where Exists clarification
          Jason Michaelides

          This is explained fully in the F1 help. Basically, records will be loaded into the Material table from MAKT.qvd only where Material_MATNR exists in the previously loaded ActiveMaterial field.

           

          Hope this helps,

           

          Jason

          • Where Exists clarification
            Jonathan Dienst

            Sean

             

            With 2 parameters, Exists will evaluate the second parameter (which can be a field or an expression) and return true if the value exists in the field name in the first parameter.

             

            In your example, the second load statement will only load records where the Material_MATNR was already loaded by the first load statement (as ActiveMaterial). The script is functionally the same as prefixing the second load with a Left Join or Left Keep qualifier.

             

            Regards

            Jonathan