2 Replies Latest reply: Jun 14, 2018 6:27 AM by Praveen Mallela RSS

    How to use WHERE EXISTS() in a SQL CLAUSE?

    Jose Gonzalez

      Hi All, I hope someone can help me

      I have these tables:

       

      LiabilitiesInformation:

          [ fields ]

      SQL SELECT [ fields ]

      FROM DataBase1;

       

      PaymentsMade:

          [ fields ]

      SQL SELECT [fields]

      FROM DataBase1;

       

      Customers:

          [ fields ]

      SQL SELECT [ fields ]

      FROM DataBase1;

       

      But there are customers that already don't have any liability. So, I don't want them.

      I decided loading LiabilitiesInformation named as a TMPLiabilitiesInformation.

      Then, I created a table that be loaded with resident from TMPLiabilitiesInformation WHERE AmountOwed(a field) > 0,

      and I called it LiabilitiesInformation.

      and I dropped TMPLiabilitiesInformation.

      Well, all is fine.

      But I need load data that only exists in the new table LiabilitiesInformation

        • Re: How to use WHERE EXISTS() in a SQL CLAUSE?
          Petter Skjolden

          You could either do it within the SELECT statement of the database or do a filter with where by putting a load in front of the SQL statement for the Customers table. The first option should be more optimal but I don't think you will notice so much difference anyway.

           

          1) The last table could be loaded like this where CustomerID is the field that identifies the customer in the LiabilitiesInformation table and the field has the same name in the Customers table:

           

              LOAD

                [fields]

              WHERE

                 Exists(CustomerID);

              SQL

                 SELECT

                    [fields]

                 FROM

                    <database.table>;

             

          2)

               SQL

                 SELECT

                   [fields]

                 FROM

                    <database.Customer>

                 WHERE

                     CustomerID IN (SELECT DISTINCT CustomerID FROM <database.LiabilitiesInformation>;

          • Re: How to use WHERE EXISTS() in a SQL CLAUSE?
            Praveen Mallela

            Hi Jose,

             

            may be Helpful

             

            [DimProductBusiness]:

            LOAD *

            Where Exists("ProductBusinessKey");

            SQL SELECT *

            FROM "DimProductBusiness";

             

            This might Helpful......

             

            Thanks

            PM