4 Replies Latest reply: Jun 26, 2012 7:25 AM by Jonathan Dienst RSS

    More than one column in Exists condition

      Hi,

       

      I need to compare more than one column using exists function...

       

      Fact:
      //To get the new records
      LOAD Day_No,
           Cntry_Cd,
            Sales,
           Insert_Day_No

      FROM
      POC.xls
      (biff, embedded labels, table is Exists2$)
      where Insert_Day_No>1;

       

      concatenate

      //to get the previous day's records
      LOAD Day_No,
           Cntry_Cd,

           Sales,
           Insert_Day_No

      FROM
      POC.xls
      (biff, embedded labels, table is Exists1$)
      where not exists (Day_No,Day_No)

      and not exists(Cntry_Cd,Cntry_Cd);

       

      I need to check whether the combination of Day_No and Cntry_Cd is available in the resultset of the upper query.

       

      But this is not working as expected...Please help me on this....

        • More than one column in Exists condition
          Goran Korsgren

          You need to concatenate the two columns to be compared.

          A good idea is also to include a "special" character between the concatenation

          to avoid that the combination of Day_No "1" and Cntry_Cd "21" is confused with the combination of Day_No "12" and Cntry_Cd "1".

           

          So something like this will do:

           

          Fact:

          //To get the new records

          LOAD Day_No,

               Cntry_Cd,

                Sales,

               Insert_Day_No,

               Day_no&'¤'&Cntry_Cd As ComboKey

          FROM

          POC.xls

          (biff, embedded labels, table is Exists2$)

          where Insert_Day_No>1;

           

           

           

          concatenate

          //to get the previous day's records

          LOAD Day_No,

               Cntry_Cd,

               Sales,

               Insert_Day_No,

               Day_no&'¤'&Cntry_Cd As ComboKey

          FROM

          POC.xls

          (biff, embedded labels, table is Exists1$)

          where not exists (ComboKey,Day_no&'¤'&Cntry_Cd);

           

          Drop Field ComboKey; // If you don't want to use it anymore

           

           

          Hope this helps!

          • More than one column in Exists condition

            Use where not (exists (Day_No,Day_No)

            and exists(Cntry_Cd,Cntry_Cd));

              • Re: More than one column in Exists condition

                did you mean

                "not (exists (Day_No,Day_No) 

                and

                not exists(Cntry_Cd,Cntry_Cd));"?

                i'm not sure it is correct.

                 

                regarding the ComboKey - is it the best practice?

                  • Re: More than one column in Exists condition
                    Jonathan Dienst

                    The combokey and the "exists(..) and exists(..)' give different results, so the choice depends on what you require.

                     

                    The combokey tests for the existence of the combination of values of field1 and field2. This test is correct when the combinations are important (ie field1 and field2 are related in some way, however loosely), and I expect would be the more common requirement.

                     

                    The separate exists clauses test for existence of a value of field1, regardless of the value of field2 and the the existence of a value of field2 regardless of field1. This test is correct if field1 and field2 need to be tested individually, and there is no direct relationship between them.

                     

                    Hope I made that clear ...

                    Jonathan