5 Replies Latest reply: Sep 17, 2014 3:48 AM by anbu cheliyan RSS

    MINUS

      Hi, I need to load, in ETL phase, a table that is the result of a MINUS (logically meaning) between 2 tables.

      Which is the right command for this statement?

       

      Thanks in advance

      Steven

        • Re: MINUS
          Martyn Lloyd

          You mean records that exist in one or other, but not both?

           

          Regards,

          M.

            • Re: MINUS

              Yes,

              I need to get records that are only in one table and not in the other one.

               

              Regards

              Steven

                • Re: MINUS
                  anbu cheliyan

                  This script performs TAB2 - TAB1

                   

                  TAB1:

                  load * inline [

                  country, sales

                  Brazil, 234

                  US, 432

                  Germany, 121

                  India, 444];

                   

                  NoConcatenate

                  TAB2:

                  load *inline [

                  country1, sales1

                  US, 412

                  Germany, 488

                  India, 224

                  China,101];

                   

                   

                  NoConcatenate

                  Final:

                  Load * Resident TAB2 Where Not Exists(country,country1);

                   

                   

                  Drop Tables TAB1,TAB2;

                    • Re: MINUS
                      anbu cheliyan

                      TAB1:

                      load * inline [

                      country, sales

                      Brazil, 234

                      US, 432

                      Germany, 121

                      India, 444];

                       

                      NoConcatenate

                      TAB2:

                      Load * Where Not Exists(country);

                      load * inline [

                      country, sales

                      US, 412

                      Germany, 488

                      India, 224

                      China,101];

                       

                      Drop Table TAB1;

                • Re: MINUS
                  Massimo Grossi

                  I think you have to join the tables and then filter records (bold)

                   

                  t1:

                  load * inline [

                  f1, f2, f3

                  a,1,100

                  a,1,101

                  b,2,200

                  c,3,300

                  z,20,2000

                  ];

                   

                  t2:

                  NoConcatenate load * inline [

                  f1, f2, f3

                  a,1,101

                  b,2,200

                  c,3,301

                  d,4,400

                  ];

                   

                  tmp:

                  NoConcatenate load *, 1 as t1 Resident t1;

                  join (tmp) load *, 1 as t2 Resident t2;

                   

                  DROP Table t1, t2;

                   

                  final:

                  NoConcatenate load * Resident tmp where t1=1 and IsNull(t2);

                   

                  DROP Table tmp;