3 Replies Latest reply: Jul 22, 2013 12:46 PM by Nigel West RSS

    Joining SQL Queries in Load and manipulate in memory before QVD

      Here is the scenario. I have several sql pulls that I need to make into memory, joining them together and then doing calculations based on those fields before writing out a qvd file. For example.

       

       

      Let's say I need to.

       

       

      1) select visitid as [Visit ID],

      name as [Name],

      location as [Location],

      cost1 * 3.23 as [Cost 1],

      cost2 * 5.33 as [Cost 2]

      from db1;

       

      2) select visitnum as [Visit ID],

      cost3 as [Cost 3]

      cost4 / 5.64 as [Cost 4]

      from db2;

       

      Then I want to do calculations on these fields such as

       

      [Cost 1] / [Cost 3]*[Cost4] as [Cost Total 1]

       

       

      This is a very simplified example but it should do the trick...

       

      What I am wanting to do is load the first sql into memory, then join the second sql in memory and then do the calculations on the columns (using the column names instead of field names and finally produce a single qvd file. I think you have to use resident tables and joins, but I am not clear on the syntax to get this done... Any help with detailed scripting syntax would be a lifesaver. Thanks ahead of time.