JOIN And SUM not working

[#1]

Hi,

Im using 2 tables with both the column FCL in it. The first column has an ID (KREDIETBEHEERDER) which i want to use as key of my fact table. The other one has an amount in it. An example.

VRNT_KREDIETBEW_SALDO:

KREDIETBEHEERDER              FCL

1                                                100

2                                                 200

3                                                 896

4                                                 976

FIS_VKREDIETBEWAKING.KREDIETBEHEERDER:

BEDRAG_BEGROOT              FCL

10,00                                       100

20,00                                       100

30,00                                       896

40,00                                       976

What i want a fact table with the total amount per FCL, but the FCL must be stored in another table (the dimension). I made this script:

FactBudget:
LOAD

SUM(BEDRAG_BEGROOT) AS Total;

SQL SELECT FIS_VRNT_KREDIETBEW_SALDO.BEDRAG_BEGROOT, FIS_VKREDIETBEWAKING.KREDIETBEHEERDER, FIS_VKREDIETBEWAKING.FCL
FROM PRODFIS.FIS_VKREDIETBEWAKING, BITFIS.FIS_VRNT_KREDIETBEW_SALDO
WHERE FIS_VKREDIETBEWAKING.FCL = FIS_VRNT_KREDIETBEW_SALDO.FCL
GROUP BY FCL;

DimBudget:

SQL SELECT FCL

FROM PRODFIS.FIS_VKREDIETBEWAKING

This isn't working, i can't get the total BEDRAG_BEGROOT per FCL.

Does anyone know how to make it work?

[martin59]

Hi,

You have to do this :

FactBudget:

SQL SELECT Sum(b.BEDRAG_BEGROOT) as Total,

a.FCL

FROM PRODFIS.FIS_VKREDIETBEWAKING a, BITFIS.FIS_VRNT_KREDIETBEW_SALDO b
WHERE a.FCL = b.FCL
GROUP BY a.FCL;

DimBudget:

SQL SELECT FCL

FROM PRODFIS.FIS_VKREDIETBEWAKING

Hope that helps you,

Martin Favier

[martin59]

Hi,

You have to do this :

FactBudget:

SQL SELECT Sum(b.BEDRAG_BEGROOT) as Total,

a.FCL

FROM PRODFIS.FIS_VKREDIETBEWAKING a, BITFIS.FIS_VRNT_KREDIETBEW_SALDO b
WHERE a.FCL = b.FCL
GROUP BY a.FCL;

DimBudget:

SQL SELECT FCL

FROM PRODFIS.FIS_VKREDIETBEWAKING

Hope that helps you,

Martin Favier

[#4]

Thanks for your quick response.

Thats indeed working, but FCL turns out to be a key. KREDIETBEHEERDER must be the only key in the two tables.

Ike this:

FactBudget:

KREDIETBEHEERDER (KEY)

Total

DimBudget

KREDIETBEHEERDER (KEY)

FCL

[#5]

Just drop the field FCL or give it different names in the 2 tables.

e.g.

Drop field FCL;

[martin59]

Ok, so you have to do this :

FactBudget:

SQL SELECT Sum(b.BEDRAG_BEGROOT) as Total,

a.KREDIETBEHEERDER

FROM PRODFIS.FIS_VKREDIETBEWAKING a, BITFIS.FIS_VRNT_KREDIETBEW_SALDO b
WHERE a.FCL = b.FCL
GROUP BY a.KREDIETBEHEERDER;

DimBudget:

SQL SELECT KREDIETBEHEERDER,

     FCL

FROM PRODFIS.FIS_VKREDIETBEWAKING

Hope that helps you

Martin Favier

[martin59]

Ok, so you have to do this :

FactBudget:

SQL SELECT Sum(b.BEDRAG_BEGROOT) as Total,

a.KREDIETBEHEERDER

FROM PRODFIS.FIS_VKREDIETBEWAKING a, BITFIS.FIS_VRNT_KREDIETBEW_SALDO b
WHERE a.FCL = b.FCL
GROUP BY a.KREDIETBEHEERDER;

DimBudget:

SQL SELECT KREDIETBEHEERDER,

     FCL

FROM PRODFIS.FIS_VKREDIETBEWAKING

Hope that helps you

Martin Favier

[martin59 · Accepted solution]

Ok, so you have to do this :

FactBudget:

SQL SELECT Sum(b.BEDRAG_BEGROOT) as Total,

a.KREDIETBEHEERDER

FROM PRODFIS.FIS_VKREDIETBEWAKING a, BITFIS.FIS_VRNT_KREDIETBEW_SALDO b
WHERE a.FCL = b.FCL
GROUP BY a.KREDIETBEHEERDER;

DimBudget:

SQL SELECT KREDIETBEHEERDER,

     FCL

FROM PRODFIS.FIS_VKREDIETBEWAKING

Hope that helps you

Martin Favier

[#9]

It's working, thanks!