Update Values in Resident table

[#1]

Somebody please help me on this:

I have a table in Load script like this.

FACT:

Load MyId,

       X,

       Y;

SQL xxxxxxxx;

Left Join (FACT)

Load MyId,

        //Append Columns

        Z;

WHERE Exists (MyId, MyId);

SQL xxxxxxx;

Since it is a left join, I need to update the Z = Y if it is NULL. How can I do this???

[#2]

please help~~~

[manas_bn · Accepted solution]

Hi Sharon,

I am not sure if this is the right method with regard to performance, may be someone more experienced can help, but you can try the following:

After the Left join is done,

Rename Table Fact to TEMP_Fact;

Fact:

Noconcatenate

load MyID, X, Y, //Other columns

if(isnull(Z),Y,Z) as Z

Resident TEMP_Fact;

Drop table TEMP_Fact;

Cheers!

Manas

[#4]

It works~~~ thank you so much!!!!

[jagan]

Hi,

Try like this

FACT_Temp:

Load MyId,

       X,

       Y;

SQL xxxxxxxx;

Left Join (FACT_Temp)

Load MyId,

        //Append Columns

        Z;

SQL xxxxxxx;

FACT:

LOAD

   MyId,

X,

Y,

Z AS Ztemp,

If(IsNull(Z), Y, Z) AS Z,

,

,

,

RESIDENT FACT_Temp;

DROP TABLE FACT_Temp;

Hope this helps you.

Regards,

Jagan.