8 Replies Latest reply: Apr 20, 2012 7:24 AM by Jason Michaelides RSS

    Special join

      Hello everyone,

       

      we have 2 tables:

       

      tableNewNumbers:

      numberstate
      123active
      234active

       

      tableOldNumbers:

      numberstate
      234deleted
      888active

       

       

      The state of all numbers in "tableOldNumbers" which do not exist in "tableNewNumbers" should be changed to 'removed'.

       

      Our result table should be:

      numberstate
      123active
      234

      active

      888removed
        • Special join
          Jason Michaelides

          Data:

          LOAD

               Number

               ,State

          FROM Table1....;

           

          CONCATENATE (Data)

           

          LOAD

               Number

               ,'removed'     AS     State

          FROM Table2

          WHERE NOT EXISTS(Number);

           

          Hope this helps,

           

          Jason

            • Re: Special join

              Thanks for your quick answer.

              This was also our idea, but the field "state" has already a value in Table2 which has to be updated.

               

              The result of your advice is:

              numberstate
              123active
              234active
              234deleted
              888active
                • Re: Special join

                  Hi Sleepa,

                   

                     what  Jason Michaelides suggested is working fine .

                  Kindly find the attached application.

                   

                   

                  Regards,

                  Sampath Kumar G

                    • Re: Special join

                      Slight modification. If in the real world you are trying to find out how to join two tables because you are pulling in some different fields then you can do this. Otherwise, if the tables are the same then Jason's way is fine.

                       

                      Data:

                      LOAD

                           Number

                           ,State

                      FROM Table1....;

                      LEFT JOIN LOAD

                           Number

                           ,State

                           ,SomeOtherField

                      FROM Table2;

                       

                      CONCATENATE (Data)

                       

                      LOAD

                           Number

                           ,'removed'     AS     State

                           ,SomeOtherField

                      FROM Table2

                      WHERE NOT EXISTS(Number);

                       

                       

                      Seems pretty strange wanting to join on state though, maybe this isn't what you're really trying to do.

                    • Special join
                      Jason Michaelides

                      Are you sure you are loading tableNewNumbers first?  To use your exact table names in my script:

                       

                      Data:

                      LOAD

                           Number

                           ,State

                      FROM tableNewNumbers....;

                       

                      CONCATENATE (Data)

                       

                      LOAD

                           Number

                           ,'removed'     AS     State

                      FROM tableOldNumbers

                      WHERE NOT EXISTS(Number);

                       

                      If 234 existis already in tableNewNumbers it will not be loaded from tableOldNumbers.  Likewise, if 888 does not exist in tableNewNumbers then it will be loaded from tableOldNumbers.

                       

                      Jason

                  • Re: Special join
                    Arun Prasadh Narasimhalu

                    Hi Sleepa,

                     

                    What Jason and Sampath said is right. I also tried that and i got the perfect output as per yours. Check it out once again.

                    Regards,

                    Arun Prasadh.N.