Left join with where clause in load

[#1]

TabA

a, b

1, '2011-01-01'

2, '2011-02-01'

2, '2011-03-01

TabB

c, d, e

1, '2011-01-01', '2011-01-31'

2, '2011-01-01', '2011-03-31'

Sample in the sql command:

select *

  from taba

left join tabb on a = c and b >= d and b <= e

How  can i do this in the load?

[SunilChauhan]

TabA:

select a,b

from taba

TabB:

select c as a,

c

d,

e

from tabB

TabC:

Load

a,d,e,c

resident TabB

left join

Load

a,b

resident TabA;

TabD:

Load

a,b,d,e

resident TabC

where a = c and b >= d and b <= e;

drop tablesTabB,TabA,TabC ;

[#3]

I tried to load this script but not functioned.

TabA:

LOAD * INLINE [

a, b

1, '2011-01-01'

2, '2011-02-01'

2, '2011-03-01'

];

TabB:

LOAD c as a, c, d, e INLINE [

c, d, e

1, '2011-01-01', '2011-01-31'

2, '2011-02-01', '2011-01-01'

];

TabC:

Load

a,d,e,c

resident TabB;

left join Load

a,b

resident TabA;

TabD:

Load

a,b,d,e

resident TabC

where a = c

and b >= d

and b <= e;

drop tables TabB,TabA,TabC ;

[johnw]

I think this?

TabA:
LOAD * INLINE [
a, b
1, '2011-01-01'
2, '2011-02-01'
2, '2011-03-01'
];
LEFT JOIN (TabA)
LOAD c as a, c, d, e INLINE [
c, d, e
1, '2011-01-01', '2011-01-31'
2, '2011-02-01', '2011-01-01'
];
INNER JOIN (TabA)
LOAD *
RESIDENT TabA
WHERE b >= d AND b <= e;

[#5]

Hi John,

I need something a little different.

TabA:

LOAD * INLINE [

a, b

1, '2011-01-01'

2, '2011-02-01'

2, '2011-03-01'

2, '2011-04-01'

];

LEFT JOIN (TabA)

LOAD c as a, c, d, e INLINE [

c, d, e

1, '2011-01-01', '2011-01-31'

2, '2011-02-01', '2011-03-31'

];

LEFT JOIN (TabA)

LOAD *

RESIDENT TabA

WHERE b >= d AND b <= e;

I need the result below:

1, '2011-01-01',  '2011-01-01', '2011-01-31'

2, '2011-02-01',  '2011-02-01', '2011-03-31'

2, '2011-03-01',  '2011-02-01', '2011-03-31'

2, '2011-04-01',  null, null

Thanks

[lironbaram · Accepted solution]

hei

try this one

TabA:

LOAD * INLINE [

a, b

1, 01/01/2011

2,01/02/2011

2, 01/03/2011

2, 01/04/2011

];





LEFT JOIN (TabA)

LOAD c as a, c, d, e INLINE [

c, d, e

1, 01/01/2011, 31/01/2011

2, 01/02/2011,31/03/2011

];




LEFT JOIN (TabA)
LOAD b,
d as F,
e as G
RESIDENT TabA
WHERE b >= d AND b <= e;

drop fields d,e;

RENAME Field F to d;
RENAME Field G to e;

[#7]

Thanks guys!