Left join Error

[#1]

HI All,

somehone can help me please.

If I use a field readed on first Table the script send an error

Rem --------------Table Orders-------------;

Orders:

load *,

date#(BPYYMD,'YYYYMMDD') AS DataCalcolata

DEJART as Article,

DEPREZ as Price,

DEARTC as DescArt,

SQL SELECT *

FROM ORDERLINE;

Rem --------------Table Cost -------------;

TableCost:

LOAD

StartDate,

addmonths((StartDate-1),12,1) as EndDate,

FOTRHD as CostTransport,

FOTRVA as Eur_Mult;

LOAD *,

date#(FODATA,'YYYYMMDD') as StartDate;

SQL SELECT *

FROM COSTITRS;

where FODATA>=$(vStartSql);

ERROR: "Field PRICE not found if I don't use the field PRICE the script works well.

I have reduced the script, I hope to have rewrite the field's name correctly, anyway the problem is to use a field reading during

the SQL SELECT * FROM ORDERLINE

Rem -------------- Join Orders -------------;

LEFT JOIN (Orders)

LOAD

StartDate + IterNo() -1 AS DataCalcolata,

(PRICE +CostTransport) as PriceOrder,

((100-Eur_Mult)/100) as EurPercentage,

RESIDENT TableCost

WHILE StartDate + IterNo() - 1 <=EndDate;

Many Thanks

Claudio

[Anonymous]

Claudio,
You're loading data from TableCost - so the fields here can be used only if they exist in TableCost, no matter to what table you're joining it. After join you can use fields in the resulting table.

[#3]

Hi Michael

many thanks for your reply

I know that at the end the fields will be all avaiables, but for saveing time and processor i would like precalcolate the result during the script

To do this probabliyI have to do another load (after the left join, load) from resident table and calcoulate them

Thanks again

Claudio

[Anonymous]

Correct, you can do it in the script as well, but it could be done after you join the source tables, not at the moment of joinining.