4 Replies Latest reply: Oct 28, 2013 7:52 PM by jon hero RSS

    how can I limit result of 1 table based on the other table field value?

      Dear friends,

      Will you please help me out? t1 is a stock on hand table, loc is location, id is product code, qty1 is stock on hand. t2 is a location transfer transaction list. fromloc and toloc means stock is transfered from 'fromloc' to location 'toloc' for product id, qty. what I want is returning rows from t2 only when fromloc or toloc in t2 is matching loc in t1 per product code id. E.g. for product 'p1', loc 'it.02.03', the rows returned from t2 will be

      '02','it.02.03','p1',20

      'it.02.03','03','p1',20.

      Hopefully you understand what I try to do. Another way I can think is 'Exists' function, but I do not figure how to do it.

      (In normal SQL, it may be resolved by nested SQL statement). thanks, jon

       

      t1:

      load * inline [

      loc,id,qty1

      'it.02.03','p1',10

      'it.02.03','p2',20];

       

      t2:

      load * inline [

      fromloc, toloc,id,qty

      '02','it.02.03','p1',20

      'it.02.03','03','p1',20

      '02','it.02.04','p1',10

      '02','03','p1',10

      '02','02d','p1',10

      '02','it.02.03','p2',20

      '03','it.03.02','p2',15

      'it.02.03','03','p2',20

      ];

       

       

      for i=0 to NoOfRows('t1')-1

          let vloc=peek('loc',$(i),'t1');

          let vid=peek('id',$(i),'t1');

          for j=0 to NoOfRows('t2')-1

              let vfromloc=peek('fromloc',$(j),'t2');

              let vtoloc=peek('toloc',$(j),'t2');

              let vid2=peek('id',$(j),'t2');

              if $(vid)=$(VID2) then

                  if $(vfromloc)= $(vloc) or $(vtoloc)=$(vloc) then

                  t3:

                  load '$(vfromloc)' as fromloc, '$(vtoloc)' as toloc, id, qty resident t2;           

                  endif;

              endif;           

          next j;

      next i;

       

      drop table t2;