4 Replies Latest reply: Dec 20, 2011 4:18 PM by John Witherspoon RSS

    Data load

    Josh Duenyas
    Josh Duenyas Dec 20, 2011 3:10 PM

      Hi all

       

      Is it possible to link 2 already loaded tables and create a third table?

      The reason is taht one of the tables requires long time to load and I don't want to call it again for another load.

       

      Here is a simplified case:

       

      TableA:

      Load OrderID,

             customerName,

             QuoteID;

      SELECT *

      FROM ORDERS;

       

      TableB:

      Load QuoteDate,

             Amount,

             QuoteID;

      SELECT *

      FROM QUOTES;

       

      And now what should the structure be to create TableC?

       

      TableC:

      Load CustomerName,

             QuoteDate,

             Amount;

      Resident TableA INNER JOIN Resident TableB ON TableA.QuoteID = TableB.QuoteID (???)

       

       

      Thanks

        • Data load
          Lucian Cotea
          Lucian Cotea Dec 20, 2011 3:19 PM (in response to Josh Duenyas)

          TableC:

          noconcatenate LOAD

             CustomerName,

              QuoteID

          RESIDENT TableA;

           

          inner join (TableC)

          LOAD

              QuoteID,

              Amount

          RESIDENT TableB;

          • Re: Data load
            John Witherspoon
            John Witherspoon Dec 20, 2011 3:22 PM (in response to Josh Duenyas)

            If you do nothing, tables A and B will be automatically connected by QuoteID.  No need to load table C.  If you really want them in a single table:

             

            TableC:
            Load OrderID,
                   customerName,
                   QuoteID;
            SELECT *
            FROM ORDERS;

             

            INNER JOIN (TableC)
            Load QuoteDate,
                   Amount,
                   QuoteID;
            SELECT *
            FROM QUOTES;

             

            DROP FIELD QuoteID;

             

            Edit: And I mean do this INSTEAD of loading tables A and B.

              • Data load
                Josh Duenyas
                Josh Duenyas Dec 20, 2011 4:15 PM (in response to John Witherspoon )

                Thank you Jon for your reply.

                I follow your positings and figuered I will "hear" from you.

                 

                Alas, I do need the other 2 tables on their own and the 3rd one should be separated. The structure of my data (currently some 35 different tables from many complex views in SQL) are too complex to wade and weed through.

                 

                When time is right I believe I should revisit the whole structure and do the right things to it.

                 

                Thanks anyways.

                  • Re: Data load
                    John Witherspoon
                    John Witherspoon Dec 20, 2011 4:18 PM (in response to Josh Duenyas)

                    jduenyas wrote:

                     

                    Alas, I do need the other 2 tables on their own and the 3rd one should be separated.


                    OK, then do what Lucian suggested, but rename the fields as you load them in, or use QUALIFY to do it automatically.  Otherwise, all the fields will connect to each other since they have the same names.