3 Replies Latest reply: Jul 27, 2014 10:52 PM by Srikanth P RSS

    Data pull from SQL & QVD together

    Scott Magouirk

      I'm trying to pull data from two sources, and store into a single qvd.

       

      I have all of my 2013 data in a static file, saved as a .qvd. I want to add to this 2013 data with up to date 2014 data, which isn't static. I want to pull 2013 data from the .qvd static file, and then add 2014 data with a Oracle SQL query, and save the whole thing as one .qvd, where I can ultimately slice between 2013 and 2014 data with a List Box.

       

      Can this be done? If so, how?

       

      Here's how I'm thinking it would go:

       

      2013_Data:

      LOAD

           2013.Data

      FROM 2013_Data.qvd (qvd); // This is the static 2013 data file

       

      2014_Data:

      LOAD

           SELECT * FROM 2014_Data // This is the SQL that would pull current 2014 data from Oracle

       

      STORE Merged_Data INTO Merged_Data.qvd;

      DROP TABLE Merged_Data;

       

      Does this make sense to anyone? Thanks!

        • Re: Data pull from SQL & QVD together
          Eduardo Sommer

          Change to

           

          Merged_Data:

          LOAD

               2013.Data

          FROM 2013_Data.qvd (qvd); // This is the static 2013 data file

           

          Concatenate LOAD

               SELECT * FROM 2014_Data // This is the SQL that would pull current 2014 data from Oracle

           

          STORE Merged_Data INTO Merged_Data.qvd;

          DROP TABLE Merged_Data;

           

          To store a table, you need to have it in memory (so I named the loaded tables as Merged_Data)

           

          I prefixed the second load with concatenate, since I didn't know if they have the same structure. If the structure of both are the same, you don't need the concatenate prefix.

           

          Eduardo

          • Re: Data pull from SQL & QVD together
            Ajay Krishnan Prabhakaran

            It makes sense. As mentioned by Eduardo if the field names are not the same in the two tables then you would have to explicitly use concatenate() function, if they are the same the it would be Auto-concatenated.

            In case you dont have a year field in the file then you can do this(create a flag to distinguish the two data sets):

             

            Master_Table:

            Load *,

                    ' 2013' as Year

            From qvd_file;

             

            Concatenate

            Load *,

                 '2014' as Year

            From Database;

             

            Store Master_Table into Table.qvd;

             

            Now the field Year will act as a flag.

            • Re: Data pull from SQL & QVD together
              Srikanth P

              Hi Scott, If the two table dont have the same table structure, Please use Force concatenation to combine two table.