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

[#1]

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;

[jagan · Accepted solution]

Hi,

Try this script

TempT1:

load * inline [
loc,id,qty1
'it.02.03','p1',10
'it.02.03','p2',20];

LEFT JOIN (TempT1)
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
];

Data:
NoConcatenate
LOAD
*
RESIDENT TempT1
WHERE loc = fromloc OR loc = toloc;

DROP TABLE TempT1;

Regards,

Jagan.

[#3]

Many thanks Jagan for enlightening me., that works well. (1) Is it possible not to join two tables?  (2) Will you please point to me where I am wrong in the looping? I cannot see table t3 in the output of my original script.

thanks again,

looking forward

[jagan]

Hi

Using For each will raise lot of performance issues, better avoid if it possible.  But  without joining it is difficult to arrive this.  Try like this

T1:

load * inline [
loc,id,qty1
'it.02.03','p1',10
'it.02.03','p2',20];

TempT1:

LOAD

     loc AS TempLoc,

     id

RESIDENT T1;

LEFT JOIN (TempT1)
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
];

Data:
NoConcatenate
LOAD
*
RESIDENT TempT1
WHERE TempLoc= fromloc OR TempLoc= toloc;

DROP TABLE TempT1;

Regards,

Jagan.

[#5]

Thanks Jagan, brilliant idea. That helps me a lot. Bingo.

Regards,

Jon