7 Replies Latest reply: Apr 6, 2010 8:06 PM by John Witherspoon RSS

    Add a new column to a loaded table

    sourabhbhatia

      I am loading data in tables from QVD files and I want to add a new column, which will be a concatenation of two columns in the same table.

      Can someone suggest how to accomplish this?

       

       

      SET LoadTables='ABC','XYZ','PQR';

      FOR EACH Table in $(LoadTables)

      $(Table): LOAD * FROM $(Table).qvd (qvd);

      NEXT



      I want to add a new column in first table (ABC).

        • Add a new column to a loaded table
          Vlad Gutkovsky

          Well, since you are doing this with FOR loop iterations, I don't think it's possible (unless of course you want to do the same thing for each table). But if you had just one table, ABC for example, you can do it with a preceding load like this:

          ABC:
          LOAD
          *,
          FieldA & FieldB as Field C
          ;
          LOAD
          *
          FROM ABC.qvd (qvd);

          Regards,

            • Add a new column to a loaded table
              sourabhbhatia

              Vlad,

               

              Thanks for replying.

              I want to add the column in one of the tables only (say ABC) after the for loop.

              After the loop exits, I know that I have a table loaded with the name ABC. Is there a way to do it in an already loaded table?

                • Add a new column to a loaded table
                  Vlad Gutkovsky

                  Sure. You can do it like this (after the for loop).

                  ABC2:
                  LOAD
                  *,
                  FieldA & FieldB as FieldC
                  RESIDENT ABC;

                  drop table ABC;
                  rename table ABC2 to ABC;

                  Regards,

                    • Add a new column to a loaded table
                      John Witherspoon

                      If the table has a unique key, you can save some memory and possibly time by not creating a temporary duplicate table:

                      LEFT JOIN ([ABC])
                      LOAD
                      "Unique Key of ABC"
                      ,FieldA & FieldB as FieldC
                      RESIDENT ABC
                      ;

                      Also, if you go with the duplicate table approach, you'll probably need to do a NOCONCATENATE LOAD.

                        • Add a new column to a loaded table

                          If ABC doesn't have a unique key, you can do this:

                          LEFT JOIN ([ABC])

                          LOAD FieldA

                          , FieldB

                          , FieldA & FieldB AS FieldC

                          RESIDENT ABC

                          GROUP BY FieldA, FieldB;

                            • Add a new column to a loaded table
                              Johan Hessler

                              ddonia: may I ask why u use a group by clause in your example?

                                • Add a new column to a loaded table
                                  John Witherspoon

                                   


                                  blaise wrote:ddonia: may I ask why u use a group by clause in your example?


                                  If you don't do the group by, you can get create duplicate rows in the left join. Consider this table. The unique key of Row has been added merely for illustrative purposes. We won't be using it directly.

                                  Row, FieldA, FieldB
                                  1, X, Y
                                  2, X, Y

                                  If you do the left join without the group by, Row 1 will match both Row 1 and Row 2. Row 2 will match both Row 1 and Row 2. You end up with this:

                                  Row, FieldA, FieldB, FieldC
                                  1, X, Y, XY
                                  1, X, Y, XY
                                  2, X, Y, XY
                                  2, X, Y, XY

                                  That almost certainly isn't what you want. I make this or similar join mistakes with alarming frequency even though I should know better by now. At least I recognize the symptoms a lot faster these days, "Oh, messed up my join". One way I check for it is that most of my tables DO have a unique key. If that key is only in that one table, and a count of that key is different from a count distinct of that key, I know I messed up my table. You can also sometimes watch it happen in the log, with a table increasing in rows when it shouldn't be.

                                  The group by prevents this from happening, because it will only join one copy of the (X, Y, XY) row, giving you what you wanted:

                                  Row, FieldA, FieldB, FieldC
                                  1, X, Y, XY
                                  2, X, Y, XY