3 Replies Latest reply: Jul 28, 2014 3:38 AM by Ian Tsang RSS

    Best way to pull data in from the same table

    Ian Tsang

      Hi there

       

      I have a question on what the best way to pull data into QV is. I have 4 tables I need to bring in, e.g. 2 of them are common and 2 unique:

       

      At the moment I am pulling the data in via OLDDB queries.

       

      A number of queries reuse the same SQL.

       

      Currently, I left join to other tables to get the data I need.

       

      To reduce the calls on the db, is there an easier way to do this? I would like to run the queries for Table2_tmp and Table3_tmp once, and left join when I need them.

       

      Any help/advice gratefully received!

       

      Table1_tmp: 
      SQL SELECT * FROM Table1;
      LEFT JOIN (Table1_tmp)
      Table2_tmp:
      SQL SELECT * FROM Table2;
      LEFT JOIN (Table1_tmp)
      Table3_tmp:
      SQL SELECT * FROM Table3;
      MASTER:
      LOAD *
      RESIDENT Table1_tmp;
      DROP TABLE Table1_tmp;
      Table4_tmp: 
      SQL SELECT * FROM Table4;
      LEFT JOIN (Table1_tmp)
      Table2_tmp:
      SQL SELECT * FROM Table2;
      LEFT JOIN (Table1_tmp)
      Table3_tmp:
      SQL SELECT * FROM Table3;
      CONCATENATE (MASTER)
      LOAD *
      RESIDENT Table4_tmp;
      DROP TABLE Table4_tmp;