4 Replies Latest reply: Oct 21, 2014 9:04 AM by Kevin Pickering RSS

    Left Join in Partial Reload

    Kevin Pickering

      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?

        • Re: Left Join in Partial Reload
          Prashant Sangle

          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

          • Re: Left Join in Partial Reload
            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.

              • Re: Left Join in Partial Reload
                Kevin Pickering

                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.