Load/Create Missing Values

[#1]

Hi All,

I have two resident tables

[SLAES] and [PRODUCT]

The product table is missing few items. I would like to add some dummy records and call them unknown as shown in new [PRODDUCT] table

whatI tried to do was

left join(PRODUCT)

load PRODUCT_ID, 'UNKNOWN' as NAME resident [Sales] where /* Need help here */ NOT exists (PROD_ID) in [PRODUCT] Table

How do I do this?

Thanks,

Aji Paul.

[#2]

Here is a workaround I have , but very complex unnecessarily .

Experts would you please review and suggest and alternative?

[Sales]:
load * inline
[ITM_ID,PROD_ID,QTY,AMT
12,P12,1,20
13,P15,2,70
14,P98,3,30];

[PRODUCT_TEMP]:
load * inline
[PROD_ID,NAME
P12,Book
P15,DVD
p75,CD];


[PRODUCT_TEMP2]:
NoConcatenate Load DISTINCT PROD_ID
Resident Sales;
join(PRODUCT_TEMP2)
load PROD_ID, NAME
resident PRODUCT_TEMP;

drop table PRODUCT_TEMP;


[Product]:
NoConcatenate load PROD_ID, if(isnull(NAME), 'UNKNOWN', NAME) as NAME
Resident PRODUCT_TEMP2;

drop table PRODUCT_TEMP2;

[hic · Accepted solution]

Sales:

Load PROD_ID, ... from Sales;

Products:

Load PROD_ID, PROD_ID as PROD_ID2, NAME from Product;

Concatenate (Products)

Load distinct PROD_ID, 'UNKNOWN' as NAME

          Resident Sales

          Where not Exists(PROD_ID2, PROD_ID);

HIC

[#4]

Thank you HIC