5 Replies Latest reply: Sep 27, 2011 6:43 PM by Angus Monro RSS

    using WHERE in LOAD more than once

      Hello community,

      im trying to delete some data from a table.

      the example its about temperature and sensors.

       

      if the data of sensors 11 and 12 has to be deleted on a certain date(s), WHILE is used to delete them on LOAD.

       

      but i didnt find anything in the reference or in the community about using 2 or more WHERE cases while loading data.

      so, if i want do delete ID_sensor 12 and 11, this is not possible at the moment.

       

      Temperatures2:

      LOAD *

      RESIDENT temperatures

      WHERE ID_sensor<>'12' OR timestamp<> '02.08.2011',

      WHERE ID_sensor<>'11' OR timestamp<> '02.08.2011';

       

      OR

       

      Temperatures2:

      LOAD *

      RESIDENT temperatures

      WHERE ID_sensor<>'12' OR timestamp<> '02.08.2011' AND

      /*WHERE*/ ID_sensor<>'11' OR timestamp<> '02.08.2011';

       

      the only solution i found, was:

      1. Load QVD with "WHERE11" case (Table 1, new name)

      2. ResidentLoad with "WHERE 12" case (Table 2, former name of table 1)

      3. droptable (Table 1)

       

      is it possible, to use WHERE more than once per table ?

       

      thanks, alex

        • using WHERE in LOAD more than once
          Sunil Chauhan

          no its not

           

          we can have one where statement per table.

          • using WHERE in LOAD more than once

            Does this work? Using parenthesis should tell it how to group.

             

            Temperatures2:

            LOAD *

            RESIDENT temperatures

            WHERE( ID_sensor<>'12' and timestamp<> '02.08.2011') AND

            ( ID_sensor<>'11' and timestamp<> '02.08.2011');

              • using WHERE in LOAD more than once

                it does work !

                 

                i thought, its not possible to use more expressions in one load.

                 

                LOAD *

                RESIDENT temperatures

                WHERE( ID_sensor<>'12' OR timestamp<> '02.08.2011') AND

                ( ID_sensor<>'11' OR timestamp<> '02.08.2011');

                 

                It deletes all data from sensors 11 and 12 on date 02.08.2011

                 

                thanks,

                this saved a lot of code

                the resident load stunt used 70+ lines of code

              • Re: using WHERE in LOAD more than once
                John Duffy

                Hello.

                 

                How about Where Timestamp <> '02.08.2011' and not match (ID_Sensor,11,12).

                • Re: using WHERE in LOAD more than once
                  Angus Monro

                  Hi Alexander,

                   

                  the trick here is to invert your conditions, just so:

                   

                  Temperatures2:

                   

                  LOAD *

                   

                  RESIDENT temperatures

                   

                  WHERE not

                   

                      ((ID_sensor='12' AND timestamp= '02.08.2011')

                   

                      OR (ID_sensor='11' AND timestamp= '02.08.2011'));

                   

                   

                   

                  This can, of course, then be refactored into

                   

                   

                  Temperatures2:

                   

                  LOAD *

                   

                  RESIDENT temperatures

                   

                  WHERE not (   (ID_sensor='11' or ID_sensor='12') AND timestamp=

                  '02.08.2011'   );

                   

                   

                   

                  If you're considering listing lots of sensors to be excluded, consider

                  replacing " ID_sensor='11' or ID_sensor='12' " with "

                  match(ID_sensor,'11','12')<>0 "

                   

                  And finally, if you're going to want to change the list of sensors on

                  any regular basis, then avoid changing your code each time by listing

                  them in a CSV or XLS file, loading them in, and then using a JOIN or

                  KEEP to filter.  But I imagine you'll need further assistance from this

                  forum if you decide to go down that path.

                   

                  Angus.