Left Join in Partial Reload

[kevpickering]

Hi,

I'm not sure if what I'm trying to achieve is possible. I'm trying to incorporate a LEFT JOIN on a resident table that has been Replaced in a Partial Reload. The Replace and Left Join are required in both Full and Partial reloads.

Here's a simple script to test it out:-

Teams:

REPLACE LOAD * INLINE [

     Team, Code

     Wigan, W1

     Dracs, D1

     Rhinos, R1

];

LEFT JOIN (Teams)

LOAD * INLINE [

     Code, Color

     W1, Cherry and White

     D1, Red and Gold

     R1, Blue and Amber

];

Venues:

LOAD * INLINE [

     Code, Venue

     W1, DW Stadium

     D1, Stade de Gilbert

     R1, Headingley

];

When I execute Reload, everything is fine, 3 tables are loaded and table viewer shows 3 tables as expected.

When I execute a Partial Reload, the data model now has synthetic keys with an extra table of Teams-1 added. What I am hoping to achieve is that the Teams table would be replaced, the extra field added with the LEFT JOIN and the data model of 3 tables remaining.

This is a very simple script to test the methodology, my actual script is a bit more complex, but if I can't get the above to work..

Is a LEFT JOIN to a replaced RESIDENT table possible during a partial reload?

[PrashantSangle]

Hi,

In your posted script there is only 2 tables how you are getting 3 table?

and If you are using Resident table then write

NoConcatenate before tableName

and after that drop the original one if you dont want it.

Regards

[Gysbert_Wassenaar]

I don't think so. A left join creates a new table from two source tables. When you partially reload only the replace load is executed. The other tables remain. That includes the joined Teams table (that contains the color field) from the normal reload. That's also the reason the replace load creates Teams-1 during a partial reload. And since it has all its fields in common with the Teams table you get a synthetic key.

[kevpickering]

Thanks for getting back.

My apologies, that's a typo. Yes, 2 tables not 3

I'll also rephrase my question.

Is it possible to Replace a table in Partial Reload and then LEFT JOIN to it to add more fields?

[kevpickering · Accepted solution]

Well, I've managed to achieve it....

The successful script....

IF IsPartialReload() THEN

     DROP TABLE Teams;

END IF;

Teams:

ADD LOAD * INLINE [

     Team, Code

     Wigan, W1

     Dracs, D1

     Rhinos, R2

];

LEFT JOIN (Teams)

ADD LOAD * INLINE [

     Code, Color

     W1, Cherry and white

     D1, Red and Gold

     R2, Blue and Amber

];

Venues:

LOAD * INLINE [

     Code, Venue

     W1, DW Stadium

     D1, Stade de Gilbert

     R2, Headingley

];

Note the explicit dropping of the table in Partial reload, followed by REPLACE ADD LOAD to recreate the Teams table and then LEFT JOIN ADD LOAD to add the extra Color field.

Thanks all for your input.