join problem

[Anonymous]

Dear all,

I hope somebody can put me on the right track. The following script works fine and loads the entire table MYTABLE

TABLE1:

load *.....

FROM MYTABLE.qvd (qvd)

WHERE  ISNULL(FIELD1);

TABLE2:

left join(TABLE1)

load *,...

FROM MYTABLE.qvd (qvd)

WHERE NOT ISNULL(FIELD1);

Well, in order to solve some RAM problem, I decided to split MYTABLE into MYTABLE_MONTH. So I changed a bit the script(see below), but it doesn't work well, because I obtain two tables (TABLE1 and TABLE2 plus synthetic key table. I just tried to load all the MYTABLE_*  files.

TABLE1:

load *.....

FROM MYTABLE_*.qvd (qvd)

WHERE  ISNULL(FIELD1);

TABLE2:

left join(TABLE1)

load *,...

FROM MYTABLE_*.qvd (qvd)

WHERE NOT ISNULL(FIELD1);

Help please.

Thanks in advance for your support

[swuehl]

I believe the TABLE2 Load will try to join one input table after the other to the TABLE1 resulted from the previous Load.

But all joins of the TABLE2 after the first input table will already encounter a resulting Table with additional fields, that's why it doesn't work as intended. (To be a bit more detailed here, we would need to see your full script and we would need to know a bit more about the fields in MYTABLE.QVD).

You can try to first load all files for TABLE2 into one resident table, then join, but this will be again mostly the same where you started from.

[nagaiank]

Try the following script. You will get two tables as qvd's, TABLE1 with FIELD1 null and the TABLE2 with FILED1 not null.

TABLE1:

load *.....

FROM MYTABLE.qvd (qvd)

WHERE  ISNULL(FIELD1);

Store TABLE1 into TABLE1.qvd (qvd);

Drop Table TABLE1;

TABLE2:

NoConcatenate

load *,...

FROM MYTABLE.qvd (qvd)

WHERE NOT ISNULL(FIELD1);

Store TABLE2 into TABLE2.qvd (qvd);

Drop Table TABLE2;