4 Replies Latest reply: Dec 3, 2012 7:08 PM by guillaumew RSS

    Most efficient way to split tables

      Hi Qview users,


      What is the most efficient way to split one table containing a mixed bag of what I will call master data into separate entities to be consumed by QV?


      For instance, consider a table that has ID (key) / TYPE (key) / VALUE.


      For instance, 1/ COUNTRY / United States, and 2/ COUNTRY / Canada, would both belong to this table, so would 3/ CURRENCY / USD, and 4/ CURRENCY / CAD.


      How could currencies and countries be splitted efficiently, that is without multiplying queries to the data source?



        • Re: Most efficient way to split tables
          Jens Frederik Kristiansen



          If your focus is on reducing the number of queries to the data source, you could start by loading the complete table into QV, and use residents load from there on to split the table by using simple selective where queries or doing some looping on the TYPE field.



            • Re: Most efficient way to split tables

              Hi Jens,


              That's what I have done. It's working. I could not find how to do inserts on resident tables, see the first bullet point below.


              These are the two scenarios considered:


              • Row-based inserts

                        ForvValueNo = 1 toNoOfRows('mytable')
                             LET vConstantValue = peek('TYPE', $(vValueNo)-1,'mytable')

                             <IF TYPE = 'COUNTRY' THEN INSERT TO RESIDENT TABLE>

              • Multiple LOADs on a large LOAD

                        [Master Table]:

                        SQL SELECT <select statement>

                        [Country Table]:
                        Load Company,Division, ConstantValue,Description resident [Master Table]
                        where ConstantValue = 'COUNTRY';

            • Re: Most efficient way to split tables
              Gysbert Wassenaar

              It sounds like your source table is what's called a generic database. So, you could try using a generic load:


              generic select * from sometable;