1 Reply Latest reply: Apr 16, 2013 3:14 AM by Michael Ionkin RSS

    Load with sql join over two tables of the same datasouce

    Michael Ionkin

      Hello @all

       

      I want to reload a report every 5 min. In order not to overload the datasource I thought of doing  sql query on the data.

       

      My idea is to do sth like this:

       

      Load*;

      SQL Select

      Table1.Field1,

      Table1.Field2,

      Table2.Field1,

      Max(Table2.Field2) as Tbl2F2

       

      From ds1.Table1, ds1.Table2

      Where Table1.Field1=Table2.Field1

      Group By Table1.Field1, Table1.Field2, Table2.Field1;

       

       

      Is this possible and if yes where is the difference in the performance to the following code?

       

      Load*;

      SQL Select

      Field1,

      Max(Field2) as Tbl2F2

       

      From  ds1.Table2

      Group By Table2.Field1;

       

      Left Join

       

      Load*;

      SQL Select

      Field1,

      Field2

       

      From  ds1.Table1;

        • Re: Load with sql join over two tables of the same datasouce
          Michael Ionkin
          
          MAX_MAC_STA:
          Load *;
          SQL SELECT 
              lJobRef,
              lMacRef,
              Max(lMacStaRef) as [letzter Status]
              
          FROM TIG1.dbo."MAC_STA"
          Group By lMacRef, lJobRef;
          
          Outer Join
          
          Load *;
          SQL SELECT 
              lJobRef,
              lMacRef,
              Max(lMacStaRef) as [letzter Status]
              
          FROM TIG1.dbo."MAC_STA"
          Where (iMState In (2,5))
          Group By lMacRef, lJobRef;
             
          left join 
          
          Load *; 
          SQL SELECT dActShots as [Schussmenge Status Ist],
              iMState,
              iPState,
              lMacStaRef as [letzter Status],
              tBegin as [Status Beginn],
              tEnd as [Status Ende]
          FROM TIG1.dbo."MAC_STA";
          
          left Join
          LOAD *;
          SQL SELECT dActShots as [Schussmenge Job Ist],
              dActSpeed as Zykluszeit,
              dSetShots as [Schussmenge Job Soll],
              lJobRef,
              sName as Auftrag,
              sType,
              tActBegin as Auftragsbeginn,
              tActEnd as Auftragsende
          FROM TIG1.dbo.JOB;
          

          Maybe you can help me with this code. My aim is to do as much as possible on the db side. I dont want to load a whole table in order to do joins on QV side.

          I wanted first to do a join between a resident table and a db, but these a two different "source" systems...

           

          The code is working fine like this, but as i said it probably loads to much data in qv. And as I have to repeat this every 5 min , I dont want to load too much

           

          Thx in advance. chesterluck