9 Replies Latest reply: Sep 27, 2011 9:35 AM by Andreas Dippel RSS

    deleting range of data from loaded tables

      hello comunity,

       

      i have some data where some parts have to be deleted.

      i tryied to search, but could not find something suitable to delet data based on ! 2 ! conditions

       

      fe. simply not loading unwanted data

      Load *

      where name <> 'name2'; //would delete all data with name2

       

      mytable:
      LOAD * INLINE

      [ date, name, value
      2001, name1, 1
      2002, name2, 20

      2003, name3, 7

      2004, name2, 10

      ];

       

      is it possible do delete data like:

       

      del where (name)=name2 AND (date)=2004

       

      any suggestions ?

       

      // the inline data is just an example

        • deleting range of data from loaded tables

          If the data you do not want is being loaded from another source simply put

           

          Load *

          Select *

          from path

          Where name<>2 and date<>2004;

           

          If it's an inline couldn't you just simply remove the data you do not want from the script?

            • deleting range of data from loaded tables

              thanks for the input.

              the inline was just an example.

               

              when i try this with a resident load,

              like:

              resident tablename,

              Where name<>name2 and date<>2004;

               

              i get a syntax failure. havent used where before, will sort that out.

               

              thanks

                • Re: deleting range of data from loaded tables

                  I personaly have not used resident load before, but here is the syntax I use for a sequal load:

                   

                   

                  LOAD 
                      company & '-' & glaccount as BalancetoGLA,
                      accountdesc,
                      active,
                      company,
                      glaccount,
                      segvalue1,
                      segvalue2,
                      segvalue3 ;
                  SQL SELECT accountdesc,
                      active,
                      coacode,
                      company,
                      glaccount,
                      segvalue1,
                      segvalue2,
                      segvalue3
                  FROM epicor904.dbo.glaccount
                  Where glaccount<>'';
                  

                   

                  I think it should be the same just with resident wheryou woul insert the where statement after the table name and before the semicolon:

                   

                  FROM Resident epicor904.dbo.glaccount Where glaccount<>'';

                  • deleting range of data from loaded tables
                    Rob Wunderlich

                    Your syntax looks roughly correct. Can you post the entire load statement?

                     

                    -Rob

                      • deleting range of data from loaded tables

                        thanks for all the suggestions, i was occupied with something else for a while.

                        here is an example of what the load looks like

                         

                        // Thermosens
                        sensdata:
                        LOAD
                        ID_sensor,
                        mid(time,7,2) & '.' & mid(time_ALT,5,2) & '.' & left(time,4) as Timestamp,
                        data as temperature

                        FROM

                        $(vQVDPath)datapool.qvd

                        (qvd)

                        Where ID_sensor<>'12' AND temperature<>'-1000' /*timestamp<>02.08.2011*/;

                         

                        # when i load the new table, every data concerning sensor 12 is missing. instead of data from 02.08.2011 (or temeratures @ -1000 which is only a test), everything is left out.

                         

                        so, the "and" seems not to work.

                        -alex

                          • deleting range of data from loaded tables
                            Jonathan Dienst

                            Hi

                             

                            I assume that you commented out the timestamp because it does not work. It does not work because timestamp does not exist in the source data.You also need an OR, because you want to include anything that is not sensor 12, but if it is sensor 12, you want to include dates other than 02.02.2011.

                             

                            If I understood you correctly, you need the where clause to read:

                             

                            Where ID_sensor<>'12' OR (mid(time,7,2) & '.' & mid(time_ALT,5,2) & '.' & left(time,4)) <> '02.08.2011'

                             

                            Regards

                            Jonathan

                              • deleting range of data from loaded tables
                                Jonathan Dienst

                                You could also optimise this a bit like this:

                                 

                                sensdata:

                                LOAD

                                    ID_sensor,

                                    Date(Date#(time, 'YYYYMMDD'), 'DD.MM.YYYY') AS Timestamp,

                                    data as temperature

                                FROM $(vQVDPath)datapool.qvd (qvd)

                                Where ID_sensor<>'12' OR Date(Date#(time, 'YYYYMMDD'), 'DD.MM.YYYY') <> '02.08.2011';

                                 

                                Hope that helps

                                Jonathan

                                  • Re: deleting range of data from loaded tables

                                    @ Jonathan, thanks, this would be nice, if it changes 'YYYYMMDDHHMM' to 'DD.MM.YYYY' and 'HH:MM'.

                                    i will try that, when the deleting part works.

                                     

                                    this works fine

                                    Where ID_sensor<>'12' OR (mid(time,7,2) & '.' & mid(time_ALT,5,2) & '.' & left(time,4)) <> '02.08.2011'

                                     

                                    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.

                                     

                                    the only solution i found, was:

                                    1. Load QVD with "Where 11" 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 ?

                                     

                                    //EDIT

                                     

                                    IT IS possible

                                     

                                    LOAD *

                                    RESIDENT temperatures

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

                                    AND ( ID_sensor<>'11' 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

                                • deleting range of data from loaded tables
                                  Rob Wunderlich

                                  I would code it like this:

                                   

                                  Where NOT (ID_sensor='12' AND temperature='-1000')

                                   

                                  I suppose this would work as well:

                                  Where (ID_sensor<>'12' AND temperature<>'-1000')

                                   

                                  The key in either case is the parens.

                                   

                                  -Rob