problem using inner join.

[#1]

hello,

i am getting problem in perfoming inner join for the following code.

LOAD "SALE_LOCN_CODE",
"SALE_DOC_TYPE",
"SALE_DOC_#",
"SALE_AMND_#",
"SALE_SRNO_#",
"SALE_FREE_FLAG",
"SALE_ITEM_CODE" ;

SQL SELECT *
FROM "NEWTON_BI1"."MKG_SALE_D" where substr("SALE_ITEM_CODE",2,2) in ('01','02','03','04','05');

LOAD "ITEM_ITEM_CODE",
"ITEM_ITEM_NAME",
"ITEM_IMP_FLG",
"ITEM_UOM_CODE",
"ITEM_BDG_ITEM",
"ITEM_SL_CODE" ;

SQL SELECT a."ITEM_ITEM_NAME"
FROM "NEWTON_BI1"."STR_ITEM_M" a INNER JOIN "NEWTON_BI1"."MKG_SALE_D" b
ON a."ITEM_ITEM_CODE"= b."SALE_ITEM_CODE" ;

the error it displays on reload is :-

Field not found - <ITEM_ITEM_CODE>
SQL SELECT a."ITEM_ITEM_NAME"
FROM "NEWTON_BI1"."STR_ITEM_M" a INNER JOIN "NEWTON_BI1"."MKG_SALE_D" b
ON a."ITEM_ITEM_CODE"= b."SALE_ITEM_CODE" .

please suggest the solution.

is there any other way to get ."ITEM_ITEM_NAME" depending upon the common "ITEM_ITEM_CODE" and ."SALE_ITEM_CODE" .

[#2]

The error is because of your sql statement which only returns "ITEM_ITEM_NAME" where as in your load you have all other fields.

You could just load the item master and rename the item code field to join back to sales data.

LOAD "SALE_LOCN_CODE",
"SALE_DOC_TYPE",
"SALE_DOC_#",
"SALE_AMND_#",
"SALE_SRNO_#",
"SALE_FREE_FLAG",
"SALE_ITEM_CODE" ;

SQL SELECT *
FROM "NEWTON_BI1"."MKG_SALE_D" where substr("SALE_ITEM_CODE",2,2) in ('01','02','03','04','05');

LOAD

"ITEM_ITEM_CODE" AS "SALE_ITEM_CODE",

"ITEM_ITEM_NAME",

"ITEM_IMP_FLG",
"ITEM_UOM_CODE",
"ITEM_BDG_ITEM",
"ITEM_SL_CODE" ;

SQL SELECT *
FROM "NEWTON_BI1"."STR_ITEM_M" ;

Hope this helps

Thanks

Tariq