8 Replies Latest reply: Feb 24, 2016 12:21 PM by Stefan Wühl RSS

    Where not exist

    Laura Sébille

      Hello everyone,

       

      Currently, I have a script in with these 2 blocks.

       

      Table1:
      Load
      key as B
      *;
      Select

      .. from TableA;

       

      Table2:
      Load
      key as B
      *;
      Select

      .. from TableA;

       

       

      With different rules in the "select" but both tables have the same structure.

      I want to keep all the lines that are in Table1 and add the lines from Table2 for which "key" is not in Table1.

       

      I wanted to use "where not exists". But since My Load for the second table is based on a "select", I cannot directly use it. So I wanted to add a third bloc like  LOAD * resident Table2 where not exits (key,key)  But I don't know how I can specify that I only want to exclude the "key" that are already in Table1 (without taking into account the Table2 which will be load before ).

      Thank you for you help

       

      Have a good day

      Laura

        • Re: Where not exist
          Lucian Cotea

          First, tables will be concatenated automatically because they have the same structure. Assuming this is what you want, you can add a preload to the second table:

           

          Table1:
          Load
          key as B
          *;
          Select

          .. from TableA;



          Table2: // actually this will concatenate to Table1, so there;s no point in naming

          Load *

          where not exits (B)

          ;

          Load
          key as B
          *;
          Select

          .. from TableA;

          • Re: Where not exist
            Stefan Wühl

            You can probably do the filtering directly on the DB server using SQL, but if you want to use QV scripting:

             

            Table2:
            Load
            key as B,
            *

            WHERE NOT EXISTS(B, key);
            Select

            .. from TableA;


            Take care that EXISTS() will also consider keys / B values loaded so far in Table2 load.

            • Re: Where not exist
              Clever Anjos

              You can write your where condition right above your SQL

              Table1:
              Load
              key as B
              *;
              Select

              .. from TableA;

               

              Table2:
              Load
              key as B
              *

              where not exists(key,B);
              Select

              .. from TableA;

              • Re: Where not exist
                bobbyraj santhiogu

                HI,

                You should try this:

                 

                Table1:
                Load
                key as B,
                *;
                Select

                .. from TableA;

                 

                Table2:
                Load
                key as B_temp,
                *;
                Select

                .. from TableA;



                CONCATENATE (Table1)

                LOAD

                *,

                B_temp as B

                RESIDENT Table2

                Where not exists(B,B_temp);


                DROP TABLE Table2;

                DROP FIELD B_temp;

                 

                • Re: Where not exist
                  Laura Sébille

                  Thank you everyone.

                  Once I have loaded my tables, I need to sum one fields group by other.

                  But :

                  Load xx, sum(A)

                  where not exists (key,key)

                  group by xx;

                  LOAD *

                  resident Table2;

                   

                  doesn't work...

                    • Re: Where not exist
                      Stefan Wühl

                      What do you mean with 'doesnt't work', do you get a script error message or do you get results different from your expectation?

                       

                      Could you post a small sample QVW that demonstrates your issue (you could load some mock up data using INLINE,  you don't need to use your real data)?

                    • Re: Where not exist
                      Laura Sébille

                      The problem is when I do :

                      Load A, sum(XX)

                      group by A;

                      Table1:
                      Load
                      key as B
                      *;
                      Select

                      .. from TableA;

                      Table2:
                      Load
                      key as B
                      *

                      where not exists(key,B);
                      Select

                      .. from TableA;

                       

                       

                      The sum and group by applies only on Table1 (Before Table1 and Table2 are automatically concatenated) and so in te end I get 2 tables (with asynthetic table) .

                       

                      How can I specify I want the sum and group b apply on both Table1 and Table2 (concatenanated). I have tried brackets but it doesn't work.

                       

                      Thank you for your help

                        • Re: Where not exist
                          Stefan Wühl

                          Really not sure what you want to achieve here. You could try adding the GROUP BY and the SUM() also to the second table load, if all field names are identical, the table should get auto-concatenated. You can also use CONCATENATE LOAD prefix to force concatenation.

                           

                          Besides this, I think your exists(key, B) is not correct, since B is not a field from your input records, right?