5 Replies Latest reply: Aug 27, 2014 3:51 AM by Friedrich Hofmann RSS

    EXISTS - can I specify a table_name?

    Friedrich Hofmann


      Hi,

       

      I have a rather complicated tool for historizing personell_data - comparing every day if any emps have left and if any emps have started.

      In this, I use the >WHERE NOT EXISTS()< several times.

      I just wonder - there are a few RESIDENT LOADs, so there might be confusion with the field_names. I think it works, but I like to keep everything as explicit as possible, just makes everything easier to maintain.

      Can I, in an EXISTS()_clause, specify a table_name along with the field_name so that it's quite clear which field is being referred to?

       

      Thanks a lot!

       

      Best regards,

       

      DataNibbler

        • Re: EXISTS - can I specify a table_name?
          Tresesco B

          You can like(with a dot):

          Where Exists(tablename.Field1, field2)             

            • Re: EXISTS - can I specify a table_name?
              Friedrich Hofmann

              Excellent!

              Thanks! I'll do that right away.

              You know, I am pretty sure it works - in fact, it was working, only a while ago I forgot an EXIT command in the code and so it "ran on empty"... but it's like driving a car: Seeing another car and thinking "I need to break" is not enough, you have to let the other driver know you've seen him and you're going to break.

              I'm going to insert that into the commands so anyone looking at the code lateron will get an idea of what's being done.

                • Re: Re: EXISTS - can I specify a table_name?
                  Friedrich Hofmann


                  Hi Tresesco,

                   

                  I have a wee problem now: In my historization_table (that is the data from yesterday), there are 888 lines - 888 emps, that is.

                  In my "new_data" (data that I drew from the database today) there are 887 - so 1 employee must have left the company since yesterday.

                  I then compare those two tables in both directions to find out about both

                  - employees who are in the today_table, but not in the history_table (new starters)

                  - employees who are in the history_table, but not in the today_table (leavers)

                  <=> This doesn't work. According to the log, that 1 employee is not considered.

                  As always with personell_data, providing sample_data is a bit tricky, but I can provide my code - it used to work until mid-July, so I guess it's something about that WHERE-thing that you told me yesterday - or rather about how I used it

                  I will attach the code so as not to let this post get too big and cumbersome to read.

                  Please have a look and see where the error is - as I said, it used to work, so it is correct in principal. It must be some little thing.

                  Thanks a lot!

                  Best regards,

                   

                  DataNibbler

                   

                  P.S.: I hope you're ok with German, the comments are all in German as we're a German company and way too many Germans don't quite understand English ...

                   

                  P.P.S.: It's definitely that tablename-thingy in the EXISTS() clause - I removed it and it works just as expected.

                    • Re: Re: Re: EXISTS - can I specify a table_name?
                      Tresesco B

                      Hi DataNibbler,

                      I am unfortunately not okay with German(language only. ). However, that might not be a big issue. I preferred to explore with a sample data. Thank you that your issue drove me to a new finding. I tried like:

                       

                      Old:
                      Load ID Inline [
                      ID
                      1
                      2
                      3
                      4];
                      NoConcatenate
                      New:
                      Load ID Inline [
                      ID
                      2
                      3
                      4
                      5
                      ];
                      NoConcatenate
                      Added: 
                      load
                      ID as AddedID 
                      Resident New where not Exists(New.ID, ID);

                      NoConcatenate
                      Deleted:
                      Load
                      ID as DeletedID
                      Resident Old Where not Exists (New.ID, ID); 

                       

                       

                      And that resulted:

                      Screen Shot 08-27-14 at 12.46 PM.PNG.png

                      Screen Shot 08-27-14 at 12.54 PM.PNG.png

                      Alternative(Renaming the field) Tried:
                      Old:
                      Load ID Inline [
                      ID
                      1
                      2
                      3
                      4];
                      NoConcatenate
                      New:
                      Load ID as IDNew Inline [
                      ID
                      2
                      3
                      4
                      5
                      ];
                      NoConcatenate
                      Added:
                      load
                      IDNew as AddedID
                      Resident New where not Exists(ID, IDNew);

                      NoConcatenate
                      Deleted:
                      Load
                      ID as DeletedID
                      Resident Old Where not Exists (IDNew, ID); 

                      Screen Shot 08-27-14 at 12.52 PM.PNG.png

                      Screen Shot 08-27-14 at 12.53 PM.PNG.png

                       

                      To conclude, my findinds(as long as anybody else finds otherwise) are:

                      Exists() - actually doesn't throw any error if the field <first parameter> is a vague one. [That is why there was no error with <tablename>.<field> convention; actually it doesn't take table reference. However, it may sometimes confuse one when it works preceded by a  Qualify key, because that actually generates field name with table reference.]

                      Hence, my alternative way of renaming the fields is the one I recommend. Note, you can keep the original fields in additiona to these new names to maintain the table relationships and delete (by using 'Drop Fields') unwanted fields at the end.