7 Replies Latest reply: Oct 16, 2017 10:27 AM by Antonio Mancini RSS

    Issue with where not exists()

    omar bensalem

      Hi guys, stalwar1

       

      Isn't this supposed to return :

      alert2, closed

      alert4, commented

      ?

      What I want to have, is if the alert is commented and closed; i just keep the closed line.


      Alertes:

       

      load alerte, date(Date#(date,'DD/MM/YYYY'),'DD/MM/YYYY') as date, status;

      load * Inline [

      alerte, date, status

      alerte1, 16/10/2017, non commented

      alerte2, 15/10/2017, commented

      alerte2, 15/10/2017, closed

      alerte3, 15/10/2017, non commented

      alerte4, 15/10/2017, commented

       

      ];

       

       

      NoConcatenate

      alertesNonCommentées:

      load * Resident Alertes where status='non commented';

       

       

      NoConcatenate

      alertesCommentéesClosed:

      load * Resident Alertes   where status<>'non commented';

       

       

      NoConcatenate

      alertesClosed:

      load * Resident alertesCommentéesClosed where status='closed'; // returns alert2 , closed

       

       

      load * where not exists(alerte);

      load * Resident alertesCommentéesClosed where status<>'closed'; //return alert2, commented and alert4, commented

       

      // I want to return alert2, closed and alert4,commented

       

      drop table alertesCommentéesClosed;

      drop Table alertesNonCommentées;

      drop Table Alertes;

       

       

      result:

      Capture.PNG

       

      what am I doiing wrong?