Using Table joins to create a formula in Load

[#1]

I'm attempting to pull fields from 3 separate tables in my database, and then perform a calculation on these fields (there are 5) within the Load statement.

For example:

Table 1:

Field A

Field B

Table 2:

Field A

Field C

Field D

Table 3:

Field A

Field E

Right now I'm doing the following (which I know is wrong)

[Join]:

LOAD

Field A,

Field B

SQL SELECT *

FROM Table1;

OUTER JOIN

LOAD

Field A,

Field C,

Field D,

RESIDENT [Table2];

OUTER JOIN

LOAD

Field A,

Field E

RESIDENT [Table 3]

WHERE A < 10;

OUTER JOIN

LOAD

A*B*C*D*E as [Result]

RESIDENT [Join]

GROUP BY "Field A";

Any suggestions would be greatly appreciated! Thank you,

PeterDGA

[johnw]

Well, your field names need quotes or brakets. Your final outer join has no key field to join on, has the wrong field names, and groups for no apparent reason. Well, I'm assuming that Field A is a unique key. If it isn't, then probably all of your joins are wrong instead of just the last one. But there's no reason you'd be using a unique key in a numeric calculation. I suppose this is just an example, though, so I'll pretend it makes sense that Field A is a unique key AND a value you want to use in a calculation.

LEFT JOIN ([Join])
LOAD
"Field A"
,"Field A"*"Field B"*"Field C"*"Field D"*"Field E" as "Result"
RESIDENT [Join]
;

[#3]

I included the quotations in my code, and Field A is indeed a unique key and not used in the final calculation.