Some calculations

ANSHULA · Post by **ANSHULA** » Thu Jul 01, 2004 5:22 am

If value by which you want to multiply aggregator results is one column, one row output of the select query fired on oracle table table2 and input data is in table1, then

1. When you select data from table1, select value from table2 also...
so, that output of select query will have all required columns from table1 plus one extra column - 'value' from table2,
2. In the aggregator stage, make 'value' column as one of the key columns and also, include in 'group by' clause.
3. And then do multiplication in the transformer after aggr. stage.

chucksmith · Post by **chucksmith** » Thu Jul 01, 2004 7:58 am

Which part does not work?

Is the aggregated answer correct?

Is the lookup of the multiplication factor working?

Is the mulitplication in the final transformer giving incorrect results?

Data examples might help. As I understand your problem, you are dealing with three tables, each in a different database.

WoMaWil · Post by **WoMaWil** » Fri Jul 02, 2004 2:54 am

You have found the first step of your solution yourself: There is no join to one of the other two tables for parametro, so take the following steps:

(1) Use only the first two tables:

SELECT
TO_CHAR(BALANCE_AS.FEC_DIA_GAS,'YYYY-MM-DD HH24:MI:SS'),
PUNTO_SISTEMA.COD_PUNTO_SISTEMA,
BALANCE_AS.CAN_EXTRACCION,
FROM
BALANCE_AS,
PUNTO_SISTEMA,
WHERE
BALANCE_AS.COD_INFRAESTRUCTURA = PUNTO_SISTEMA.COD_INFRAESTRUCTURA_S
AND
BALANCE_AS.IND_PROVISIONAL = 'E'
AND
TO_CHAR(BALANCE_AS.FEC_DIA_GAS,'YYYY-MM') = '#MES#' ;

And put the rest into a lookup:

SELECT
TO_NUMBER(PARAMETRO.VALOR,'9,999') AS PARAMETRO
FROM
PARAMETRO
WHERE
PARAMETRO.COD_PARAMETRO = 'PMART'
AND
PARAMETRO.FEC_BAJA IS NULL ;

That should work.

Wolfgang

rreyespe · Post by **rreyespe** » Fri Jul 02, 2004 3:05 am

but if after do that, after the first step I need to go through an aggregator stage, so I can group by all the cantities (BALANCE_AS.CAN_EXTRACCION), and after that I have to multily the result.

In this step I have new problems, if I try to do it in a transformer stage, again, there is not links between the previous result and the look up table, so I find myself in the same problem.

ANSHULA · Post by **ANSHULA** » Fri Jul 02, 2004 3:35 am

try same query using SQL Plus...Does this query run properly on SQL prompt ?

rreyespe · Post by **rreyespe** » Fri Jul 02, 2004 3:49 am

yes, it is works

ANSHULA · Post by **ANSHULA** » Fri Jul 02, 2004 6:47 am

check the SQL type for all the output stage columns... Make sure that character column from SELECT statement is not getting loaded in the numeric output column.