2 Replies Latest reply: Oct 23, 2012 7:27 AM by Marcelo Gittermann RSS

    JOIN and replace where exists

    Marcelo Gittermann

      Hello,

       

      I have a list of my total Orders. I will later be adding a column to show if they have been changed or not so to start I add it to begin with and set it to zero for all orders. Let's say it's like the below table.

       

      ORDERS:

      LOAD * INLINE [

          ORDER_NO, _CHANGED

          1, 0

          2, 0

          3, 0

          4, 0

           5, 0

      ];

       

      I also have another table where I have orders that meet a certain criteria. They are in the table CHECK and some have been changed, others haven't.

       

      CHECK:

      LOAD * INLINE [

                ORDER_NO, _CHANGED

                1, 1

                2, 1

                3, 0

      ];

       

      If I use left join the column _CHANGED will have the values that are in table ORDERS. I would like my result to have 1's where they have been changed and zeroes where they haven't. I would like to have the resulting table:

       

      1, 1

      2, 1

      3, 0

      4, 0

      5, 0

       

      How do I make that work using joins?

      (I know one solution would be to create a different column and after a join add ( + ) both columns in a new column but I want to avoid loading it again below. Is it possible?)