4 Replies Latest reply: Aug 31, 2017 10:32 AM by Tom Neal RSS

    Where not Exists not working

    Tom Neal

      Hello,

       

      I am attempting to load data to a table (Missing Products) which does not exist in an existing table (Table1).

       

      Problem is the Missing products table loads in all values regardless if they exist in Table1 or not.

       

      I was under the impression a where not exists(Field) would do the trick however since the result is not as expected maybe I've misunderstood the function.


      Table1:
      Load
      BriefRefNo as P_ID,
      ProductDescription as P_Desc,
      BriefRefNo&' | '&ProductDescription as ProductKey
      FROM
      [Products.qvd]
      (
      qvd);

      MissingProducts:
      Load ProductKeyNo,
      ProductKey
      P_Comment
      where Not Exists(ProductKey);
      Load
      ProductKeyNo,
      ProductKeyNo&' | '&P_Comment as ProductKey,
      trim(P_Comment) as P_Comment
      where not IsNull(P_Comment);
      Load
      BriefRefNo as ProductKeyNo,
      if(right(Comment, 8) = 'Inactive', TextBetween(Comment, 'Product', 'Changed to Inactive'), TextBetween(Comment, 'Product', 'Changed to Active'))  as P_Comment
      FROM
      [ChangeHistory.qvd]
      (
      qvd)
      where WildMatch(Comment, '*active*');

       

       

      The Missing Products table returns everything in that QVD instead of what's not in Table1

      Anyone have idea where I'm going wrong here?

      Thanks,

      Tom

       

        • Re: Where not Exists not working
          Andrew Walker

          Hi Tom,

          Try this:

           

          Table1:

          Load

          BriefRefNo as P_ID,

          ProductDescription as P_Desc,

          BriefRefNo&' | '&ProductDescription as ProductKey

          FROM

          [Products.qvd]

          (qvd);

           

           

          MissingProducts:

          Load ProductKeyNo,

          ProductKey, 

          P_Comment;

          //where Not Exists(ProductKey);

          Load

          ProductKeyNo,

          ProductKeyNo&' | '&P_Comment as ProductKey,

          trim(P_Comment) as P_Comment

          where not IsNull(P_Comment)

          and Not Exists(ProductKey, ProductKeyNo&' | '&P_Comment);

          Load

          BriefRefNo as ProductKeyNo,

          if(right(Comment, 8) = 'Inactive', TextBetween(Comment, 'Product', 'Changed to Inactive'), TextBetween(Comment, 'Product', 'Changed to Active'))  as P_Comment

          FROM

          [ChangeHistory.qvd]

          (qvd)

          where WildMatch(Comment, '*active*');

           

          Cheers

           

          Andrew

          • Re: Where not Exists not working
            Peter Cammaert

            Are you sure both ProductKey values are formatted identically? Did you check leading/trailing spaces or different comment values?

            • Re: Where not Exists not working
              Anand Chouhan

              You can try this also comment extra preceding load why this required you don't required that.

               

                Table1:
              Load
              BriefRefNo as P_ID,
              ProductDescription as P_Desc,
              BriefRefNo&' | '&ProductDescription as ProductKey
              FROM
              [Products.qvd]
              (
              qvd);

              MissingProducts:
              //Load ProductKeyNo,
              //ProductKey, 
              //P_Comment;
              //where Not Exists(ProductKey);
              Load
              ProductKeyNo,
              ProductKeyNo&' | '&P_Comment as ProductKey,
              trim(P_Comment) as P_Comment
              where not IsNull(P_Comment) and Not Exists(ProductKey,ProductKeyNo&' | '&P_Comment);

              Load
              BriefRefNo as ProductKeyNo,
              if(right(Comment, 8) = 'Inactive', TextBetween(Comment, 'Product', 'Changed to Inactive'), TextBetween(Comment, 'Product', 'Changed to Active'))  as P_Comment
              FROM
              [ChangeHistory.qvd]
              (
              qvd)
              where WildMatch(Comment, '*active*');