Joining SQL Queries in Load and manipulate in memory before QVD

[#1]

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.

[Gysbert_Wassenaar]

Something like this:

Table1:

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;

JOIN(Table1)

select visitnum as [Visit ID],

cost3 as [Cost 3]

cost4 / 5.64 as [Cost 4]

from db2;

Result:

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

Resident Table1;

drop table Table1;

STORE Result into result.qvd;

[Anonymous · Accepted solution]

Brian,

Something like this:

table_tmp:
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;

INNER JOIN (table_tmp)

SQL select visitnum as [Visit ID],
cost3 as [Cost 3]
cost4 / 5.64 as [Cost 4]
from db2;

table:

LOAD
[Visit ID],
Name,
Location,
[Cost 1] / [Cost 3]*[Cost4] as [Cost Total 1]
RESIDENT table_tmp;
DROP TABLE table_tmp;

STORE table into table.qvd (qvd)

[#4]

OK, syntax should be something like

Data:

LOAD visitid AS [Visit ID],

name as [Name],

location as [Location],

cost1 * 3.23 as [Cost 1],

cost2 * 5.33 as [Cost 2]

from db1;

left join (Data)

LOAD visitnum as [Visit ID],

cost3 as [Cost 3],

cost4/5.64 as [Cost 4]

from db2;

FinalTable:

NoConcatenate

LOAD *,

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

RESIDENT Data;

DROP TABLE Data;

STORE FinalTable INTO FinalTable.QVD;