You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by MULS PATRICK <pa...@datassur.be> on 2009/03/06 11:15:39 UTC
Outer join misses rows, behaves more like inner join.
Hello,
I've been trying to get global totals for a table joined to the totals by company. Looking at the first company (00014) I notice that the global total for codrec 'T' and rubriek '4' is missing. I've tried changing the order they are selected to use LEFT as well as RIGHt OUTER JOINs, but the result is always the same.
SQL outer join having missing rows:
select r.*, t.aantal totAantal, t.kostvraag totKvraag, t.mttva totMttva, t.kostvergd totKvergd, t.ztva totZtva from
(select a.ciebcc Cie, a.period, a.codrec C, a.rubriek R,
sum(case when a.tot50 = 0 then 1.0 else 0.5 end) aantal,
sum(case when a.tot50 = 0 then a.todec else a.tot50 end) kostvraag,
sum(a.ztva) ztva,
sum(a.attri + case when a.rubriek = '2' and a.codrec = 'T' then a.mttva else 0.0 end) kostvergd,
sum(a.mttva) mttva
from record90s a
where a.period = (select parmstring from params where parmtype = 'periodKas')
group by a.ciebcc, a.period, a.codrec, a.rubriek
) r
right outer join
(select b.period, b.codrec, b.rubriek,
sum(case when b.tot50 = 0 then 1.0 else 0.5 end) aantal,
sum(case when b.tot50 = 0 then b.todec else b.tot50 end) kostvraag,
sum(b.ztva) ztva,
sum(b.attri + case when b.rubriek = '2' and b.codrec = 'T' then b.mttva else 0.0 end) kostvergd,
sum(b.mttva) mttva
from record90s b
where b.period = (select parmstring from params where parmtype = 'periodKas')
group by b.period, b.codrec, b.rubriek
) t
on r.period = t.period
and r.c = t.codrec
and r.r = t.rubriek
where cie = '00014';
CIE |PERIOD|C|R|AANTAL|KOSTVRAAG |ZTVA |KOSTVERGD |MTTVA |TOTAA&|TOTKVRAAG |TOTMTTVA |TOTKVERGD |TOTZTVA
----------------------------------------------------------------------------------------------------------------------
00014|200901|R|1|1607.0|2191773.38|301053.20|2191773.38|301053.20|19903&|27289260.22|3809145.73|27289260.22|3809145.73
00014|200901|R|2|131.5 |471509.34 |104688.17|471509.34 |104688.17|1932.0|6509670.94 |1452124.11|6509670.94 |1452124.11
00014|200901|R|3|9.0 |114574.95 |13433.09 |114574.95 |13433.09 |65.0 |929992.76 |130981.36 |929992.76 |130981.36
00014|200901|R|4|6.0 |91882.81 |1168.32 |91882.81 |1168.32 |36.0 |473693.14 |25931.23 |473693.14 |25931.23
00014|200901|T|1|1780.5|2411849.31|322914.52|2462206.03|318533.43|19903&|27289260.22|3809177.90|28277826.96|3809145.73
00014|200901|T|2|126.0 |433931.18 |81295.02 |363851.11 |81295.02 |1932.0|6509670.94 |1452124.11|5521114.81 |1452124.11
00014|200901|T|3|7.0 |108220.37 |8802.33 |108220.37 |8802.33 |65.0 |929992.76 |130981.36 |929992.76 |130981.36
MISSING:
00014|200901|T|4|0.0 |0.00 |0.00 |0.00 |0.00 |36.0 |473693.14 |25931.23 |473693.14 |25931.23
7 rows selected
SQL left side of join:
select a.ciebcc cie, a.period, a.codrec, a.rubriek,
sum(case when a.tot50 = 0 then 1.0 else 0.5 end) aantal,
sum(case when a.tot50 = 0 then a.todec else a.tot50 end) kostvraag,
sum(a.ztva) ztva,
sum(a.attri + case when a.rubriek = '2' and a.codrec = 'T' then a.mttva else 0.0 end) kostvergd,
sum(a.mttva) mttva
from record90s b
where a.period = (select parmstring from params where parmtype = 'periodKas')
group by a.ciebcc, a.period, a.codrec, a.rubriek
;
CIE |PERIOD|&|RUB&|AANTAL|KOSTVRAAG |ZTVA |KOSTVERGD |MTTVA
----------------------------------------------------------------------------------------------------
00014|200901|R|1 |1607.0|2191773.38 |301053.20 |2191773.38 |301053.20
00014|200901|R|2 |131.5 |471509.34 |104688.17 |471509.34 |104688.17
00014|200901|R|3 |9.0 |114574.95 |13433.09 |114574.95 |13433.09
00014|200901|R|4 |6.0 |91882.81 |1168.32 |91882.81 |1168.32
00014|200901|T|1 |1780.5|2411849.31 |322914.52 |2462206.03 |318533.43
00014|200901|T|2 |126.0 |433931.18 |81295.02 |363851.11 |81295.02
00014|200901|T|3 |7.0 |108220.37 |8802.33 |108220.37 |8802.33
00033|200901|R|1 |423.5 |577768.32 |77409.20 |577768.32 |77409.20
00033|200901|R|2 |46.5 |172482.73 |38288.43 |172482.73 |38288.43
00033|200901|R|3 |2.0 |23359.44 |4183.26 |23359.44 |4183.26
00033|200901|T|1 |425.0 |616691.30 |82977.66 |620792.33 |80882.95
00033|200901|T|2 |41.0 |162887.66 |33827.34 |140646.85 |33827.34
00033|200901|T|4 |3.0 |33422.94 |2175.53 |33422.94 |2175.53
00037|200901|R|1 |1124.0|1570281.01 |219638.79 |1570281.01 |219638.79
00037|200901|R|2 |121.0 |428716.03 |87238.72 |428716.03 |87238.72
00037|200901|R|3 |3.0 |49230.77 |5050.50 |49230.77 |5050.50
00037|200901|R|4 |1.0 |10080.27 |943.60 |10080.27 |943.60
00037|200901|T|1 |1225.0|1597641.35 |238207.68 |1712641.79 |246056.72
00037|200901|T|2 |120.5 |393165.15 |94802.95 |340076.82 |94802.95
00037|200901|T|3 |1.0 |15999.98 |3360.00 |15999.98 |3360.00
00039|200901|R|1 |3483.0|4971965.49 |694675.55 |4971965.49 |694675.55
00039|200901|R|2 |337.5 |1116204.65 |253327.20 |1116204.65 |253327.20
00039|200901|R|3 |11.0 |162475.53 |35283.67 |162475.53 |35283.67
00039|200901|R|4 |11.0 |150630.86 |7229.30 |150630.86 |7229.30
00039|200901|T|1 |3143.0|4198976.45 |556873.38 |4387859.01 |566893.88
00039|200901|T|2 |324.5 |1096781.74 |245493.63 |920423.81 |245493.63
00039|200901|T|3 |9.0 |130687.33 |23251.07 |130687.33 |23251.07
00039|200901|T|4 |5.0 |61324.58 |5704.92 |61324.58 |5704.92
00051|200901|R|1 |971.5 |1333058.82 |188161.46 |1333058.82 |188161.46
00051|200901|R|2 |77.0 |274144.32 |59868.70 |274144.32 |59868.70
00051|200901|R|3 |1.0 |16046.51 |5878.27 |16046.51 |5878.27
00051|200901|T|1 |812.5 |1126609.74 |138810.49 |1169961.20 |141631.33
00051|200901|T|2 |71.5 |241245.80 |46509.60 |199351.02 |46509.60
00051|200901|T|3 |10.0 |133337.08 |15767.27 |133337.08 |15767.27
00051|200901|T|4 |1.0 |14739.46 |0.00 |14739.46 |0.00
00058|200901|R|1 |545.5 |690930.17 |100656.36 |690930.17 |100656.36
00058|200901|R|2 |51.0 |142972.59 |34006.58 |142972.59 |34006.58
00058|200901|R|3 |5.0 |89749.18 |12286.53 |89749.18 |12286.53
00058|200901|R|4 |1.0 |14617.14 |2485.28 |14617.14 |2485.28
00058|200901|T|1 |624.5 |883522.01 |108282.75 |914719.11 |105641.14
00058|200901|T|2 |68.0 |231253.47 |48147.00 |193243.98 |48147.00
00058|200901|T|3 |1.0 |10582.68 |0.00 |10582.68 |0.00
00058|200901|T|4 |1.0 |14314.91 |1352.83 |14314.91 |1352.83
00067|200901|R|1 |1122.5|1530591.45 |217603.01 |1530591.45 |217603.01
00067|200901|R|2 |105.5 |332040.68 |72403.22 |332040.68 |72403.22
00067|200901|R|3 |4.0 |53617.24 |0.00 |53617.24 |0.00
00067|200901|R|4 |2.0 |28888.30 |2455.58 |28888.30 |2455.58
00067|200901|T|1 |892.5 |1275733.20 |175016.47 |1260508.31 |165306.20
00067|200901|T|2 |92.0 |275159.55 |59238.37 |239194.53 |59238.37
00067|200901|T|3 |3.0 |38873.66 |5925.43 |38873.66 |5925.43
00067|200901|T|4 |5.0 |56019.15 |4075.60 |56019.15 |4075.60
00079|200901|R|1 |2612.5|3624127.74 |499927.02 |3624127.74 |499927.02
00079|200901|R|2 |266.0 |891375.21 |190304.24 |891375.21 |190304.24
00079|200901|R|3 |6.0 |86307.20 |8384.25 |86307.20 |8384.25
00079|200901|R|4 |6.0 |67781.08 |2691.63 |67781.08 |2691.63
00079|200901|T|1 |2512.5|3481327.79 |511185.02 |3586156.34 |502305.67
00079|200901|T|2 |265.5 |1003490.46 |235098.78 |845595.09 |235098.78
00079|200901|T|3 |15.0 |220169.62 |44189.92 |220169.62 |44189.92
00079|200901|T|4 |8.0 |127906.84 |5567.47 |127906.84 |5567.47
00087|200901|R|1 |363.5 |542068.32 |76081.12 |542068.32 |76081.12
00087|200901|R|2 |28.5 |98232.20 |25220.82 |98232.20 |25220.82
00087|200901|R|3 |1.0 |12625.41 |2360.40 |12625.41 |2360.40
00087|200901|T|1 |333.5 |444310.24 |54198.86 |464730.42 |55473.11
00087|200901|T|2 |32.0 |107041.65 |22332.14 |95394.28 |22332.14
00087|200901|T|3 |2.0 |22868.57 |0.00 |22868.57 |0.00
00087|200901|T|4 |2.0 |19233.23 |943.60 |19233.23 |943.60
00096|200901|R|1 |1126.5|1484694.67 |203579.44 |1484694.67 |203579.44
00096|200901|R|2 |90.0 |312195.68 |68077.20 |312195.68 |68077.20
00096|200901|R|3 |2.0 |25313.25 |2554.65 |25313.25 |2554.65
00096|200901|R|4 |2.0 |20711.82 |0.00 |20711.82 |0.00
00096|200901|T|1 |1154.5|1642980.48 |220372.74 |1658306.25 |220176.80
00096|200901|T|2 |92.0 |326901.03 |73872.86 |283880.46 |73872.86
00096|200901|T|3 |5.0 |69660.97 |9481.78 |69660.97 |9481.78
00096|200901|T|4 |3.0 |36923.87 |0.00 |36923.87 |0.00
00097|200901|R|1 |830.5 |1079994.58 |145776.36 |1079994.58 |145776.36
00097|200901|R|2 |75.5 |248061.82 |58918.75 |248061.82 |58918.75
00097|200901|R|3 |3.0 |41706.47 |6365.35 |41706.47 |6365.35
00097|200901|R|4 |1.0 |10630.52 |1844.97 |10630.52 |1844.97
00097|200901|T|1 |776.5 |1130426.38 |161946.28 |1185791.65 |161930.44
00097|200901|T|2 |95.0 |258640.40 |58988.31 |231093.47 |58988.31
00097|200901|T|4 |1.0 |11082.00 |0.00 |11082.00 |0.00
00124|200901|R|1 |137.5 |207379.15 |27970.17 |207379.15 |27970.17
00124|200901|R|2 |18.0 |64615.74 |14414.92 |64615.74 |14414.92
00124|200901|T|1 |159.5 |187206.42 |28775.86 |205941.74 |30184.68
00124|200901|T|2 |20.0 |57123.24 |10180.03 |51750.78 |10180.03
00126|200901|R|1 |43.0 |51197.26 |6865.34 |51197.26 |6865.34
00126|200901|R|2 |1.0 |1212.55 |199.51 |1212.55 |199.51
00126|200901|R|4 |1.0 |17307.95 |1644.65 |17307.95 |1644.65
00129|200901|R|1 |93.0 |137898.03 |20709.54 |137898.03 |20709.54
00129|200901|R|2 |12.0 |42877.68 |10404.10 |42877.68 |10404.10
00129|200901|T|1 |69.0 |104875.22 |15049.80 |103428.82 |14120.09
00129|200901|T|2 |5.0 |12083.80 |4025.99 |9723.14 |4025.99
00130|200901|R|1 |4.0 |2224.16 |386.01 |2224.16 |386.01
00130|200901|R|2 |1.0 |3112.00 |824.38 |3112.00 |824.38
00130|200901|T|1 |13.5 |19789.87 |3129.01 |19160.46 |2913.49
00130|200901|T|2 |2.0 |4980.70 |1245.25 |3524.11 |1245.25
00134|200901|R|1 |1.0 |83.03 |14.41 |83.03 |14.41
00134|200901|R|2 |1.0 |963.00 |156.20 |963.00 |156.20
00134|200901|T|1 |1.0 |605.00 |105.00 |1361.75 |222.32
00145|200901|R|1 |437.5 |625857.53 |85971.26 |625857.53 |85971.26
00145|200901|R|2 |50.0 |181251.81 |40257.77 |181251.81 |40257.77
00145|200901|T|1 |426.5 |592989.61 |78031.70 |628889.22 |82451.04
00145|200901|T|2 |49.0 |152836.57 |35511.47 |135456.78 |35511.47
00145|200901|T|3 |1.0 |11381.64 |1374.22 |11381.64 |1374.22
00145|200901|T|4 |1.0 |15357.14 |0.00 |15357.14 |0.00
00165|200901|R|1 |2621.0|3470759.06 |486064.70 |3470759.06 |486064.70
00165|200901|R|2 |263.5 |859048.09 |195358.02 |859048.09 |195358.02
00165|200901|R|3 |12.0 |153087.55 |15531.46 |153087.55 |15531.46
00165|200901|R|4 |2.0 |22999.73 |1008.27 |22999.73 |1008.27
00165|200901|T|1 |2748.5|3687735.74 |525583.91 |3850730.77 |527771.21
00165|200901|T|2 |284.0 |910952.95 |202048.41 |765958.81 |202048.41
00165|200901|T|3 |2.0 |40524.46 |3276.00 |40524.46 |3276.00
00165|200901|T|4 |2.0 |24952.97 |2485.28 |24952.97 |2485.28
00167|200901|R|1 |231.5 |310658.08 |43511.25 |310658.08 |43511.25
00167|200901|R|2 |21.0 |67391.24 |11810.87 |67391.24 |11810.87
00167|200901|R|3 |2.0 |33381.76 |7479.43 |33381.76 |7479.43
00167|200901|T|1 |207.0 |298599.84 |46515.22 |293579.25 |44817.88
00167|200901|T|2 |24.0 |77374.65 |19566.10 |61938.14 |19566.10
00179|200901|R|1 |82.5 |128603.93 |18928.06 |128603.93 |18928.06
00179|200901|R|2 |13.0 |51475.16 |11765.83 |51475.16 |11765.83
00179|200901|R|3 |1.0 |9596.50 |0.00 |9596.50 |0.00
00179|200901|R|4 |1.0 |11614.50 |2002.20 |11614.50 |2002.20
00196|200901|R|1 |554.5 |737630.29 |107057.55 |737630.29 |107057.55
00196|200901|R|2 |60.0 |188348.12 |43093.15 |188348.12 |43093.15
00196|200901|R|3 |2.0 |41343.95 |9636.90 |41343.95 |9636.90
00196|200901|R|4 |2.0 |26548.16 |2457.43 |26548.16 |2457.43
00196|200901|T|1 |704.0 |915412.90 |147649.86 |973342.35 |149453.48
00196|200901|T|2 |60.5 |189579.18 |42231.03 |152487.28 |42231.03
00196|200901|T|3 |1.0 |24644.00 |3255.00 |24644.00 |3255.00
00333|200901|R|1 |9.5 |20601.30 |2037.54 |20601.30 |2037.54
00333|200901|T|1 |4.0 |12043.97 |802.94 |8981.10 |666.95
00333|200901|T|4 |1.0 |11825.35 |0.00 |11825.35 |0.00
00394|200901|R|1 |1.0 |471.61 |81.85 |471.61 |81.85
00435|200901|R|1 |223.0 |292719.05 |42919.75 |292719.05 |42919.75
00435|200901|R|2 |25.0 |95693.96 |20138.27 |95693.96 |20138.27
00435|200901|T|1 |263.0 |347516.99 |58999.14 |371998.25 |59244.60
00435|200901|T|2 |31.0 |82768.26 |17272.53 |71377.01 |17272.53
00435|200901|T|3 |1.0 |10953.00 |1995.00 |10953.00 |1995.00
00497|200901|R|1 |47.0 |65728.75 |9401.21 |65728.75 |9401.21
00497|200901|R|2 |1.0 |2393.00 |472.50 |2393.00 |472.50
00497|200901|R|3 |1.0 |17577.05 |2553.60 |17577.05 |2553.60
00497|200901|T|1 |122.5 |174256.24 |23567.80 |178623.66 |23252.36
00497|200901|T|2 |13.0 |38359.20 |12101.51 |34426.96 |12101.51
00745|200901|R|1 |74.5 |104015.93 |11452.32 |104015.93 |11452.32
00745|200901|R|2 |6.0 |24529.85 |4648.00 |24529.85 |4648.00
00745|200901|T|1 |127.0 |220805.95 |10747.90 |208480.01 |9453.05
00745|200901|T|2 |6.0 |40592.36 |8203.26 |33821.99 |8203.26
00745|200901|T|3 |2.0 |25481.09 |1488.59 |25481.09 |1488.59
00745|200901|T|4 |1.0 |19693.96 |0.00 |19693.96 |0.00
00964|200901|R|1 |159.5 |237472.96 |35818.46 |237472.96 |35818.46
00964|200901|R|2 |11.0 |40335.10 |8468.59 |40335.10 |8468.59
00964|200901|T|1 |175.5 |227983.14 |37998.06 |234106.44 |36744.80
00964|200901|T|2 |9.0 |26089.01 |5569.25 |19580.55 |5569.25
00964|200901|T|3 |1.0 |11497.00 |0.00 |11497.00 |0.00
01037|200901|R|1 |88.0 |112335.46 |13552.11 |112335.46 |13552.11
01037|200901|R|2 |7.0 |29527.43 |8962.15 |29527.43 |8962.15
01037|200901|T|1 |100.0 |116615.97 |17772.06 |137208.69 |19807.02
01037|200901|T|2 |8.0 |25623.54 |6702.07 |19573.94 |6702.07
01037|200901|T|3 |1.0 |11831.55 |1769.25 |11831.55 |1769.25
01037|200901|T|4 |1.0 |12747.90 |1170.42 |12747.90 |1170.42
01093|200901|R|1 |152.0 |202311.81 |26957.10 |202311.81 |26957.10
01093|200901|R|2 |18.0 |65205.84 |15676.84 |65205.84 |15676.84
01093|200901|T|1 |166.5 |272096.57 |30392.52 |269002.57 |29688.48
01093|200901|T|2 |14.0 |45236.73 |9145.87 |36367.18 |9145.87
01093|200901|T|3 |1.0 |8841.02 |0.00 |8841.02 |0.00
01258|200901|R|1 |7.0 |6732.45 |1159.42 |6732.45 |1159.42
01258|200901|R|2 |2.0 |10082.31 |3834.99 |10082.31 |3834.99
01258|200901|T|1 |1.0 |1412.93 |238.46 |1361.76 |222.33
01396|200901|R|1 |101.0 |128000.62 |19902.02 |128000.62 |19902.02
01396|200901|R|2 |17.0 |48950.70 |11824.53 |48950.70 |11824.53
01396|200901|T|1 |150.0 |222310.87 |35144.31 |219103.51 |33361.80
01396|200901|T|2 |7.0 |29489.80 |8861.91 |28107.21 |8861.91
01396|200901|T|3 |1.0 |16014.00 |1995.00 |16014.00 |1995.00
01449|200901|R|1 |64.0 |92711.01 |13151.75 |92711.01 |13151.75
01449|200901|R|2 |12.0 |69699.94 |19235.41 |69699.94 |19235.41
01449|200901|T|1 |91.0 |119686.64 |19852.37 |132819.78 |20754.12
01449|200901|T|2 |6.0 |16099.06 |3936.02 |14529.03 |3936.02
01455|200901|R|1 |311.0 |430359.21 |62932.68 |430359.21 |62932.68
01455|200901|R|2 |31.5 |94820.43 |21554.99 |94820.43 |21554.99
01455|200901|T|1 |319.5 |443803.37 |73927.03 |446676.62 |71090.31
01455|200901|T|2 |34.0 |124661.98 |30679.76 |106984.68 |30679.76
01455|200901|T|4 |1.0 |14148.84 |2455.58 |14148.84 |2455.58
02279|200901|R|1 |144.0 |189234.12 |29202.63 |189234.12 |29202.63
02279|200901|R|2 |21.0 |57261.76 |11369.11 |57261.76 |11369.11
02279|200901|T|1 |260.0 |365001.08 |59592.54 |413346.96 |63739.76
02279|200901|T|2 |18.5 |91441.53 |22747.37 |76670.78 |22747.37
02279|200901|T|3 |1.0 |18424.74 |5050.50 |18424.74 |5050.50
02314|200901|R|1 |14.5 |20337.65 |2048.77 |20337.65 |2048.77
02314|200901|T|1 |11.0 |18006.77 |2687.60 |14423.44 |1889.71
02393|200901|R|1 |50.5 |65388.60 |9561.24 |65388.60 |9561.24
02393|200901|R|2 |6.0 |16569.74 |3661.71 |16569.74 |3661.71
02393|200901|T|1 |56.5 |70741.07 |11931.71 |76414.10 |12135.56
02393|200901|T|2 |8.0 |32610.93 |7022.78 |27407.51 |7022.78
02551|200901|R|1 |41.0 |51295.22 |6927.08 |51295.22 |6927.08
02551|200901|R|2 |4.0 |6370.24 |1650.24 |6370.24 |1650.24
02551|200901|T|1 |46.5 |59695.81 |9865.08 |65173.27 |10361.19
02551|200901|T|2 |5.0 |21269.36 |5467.50 |18677.51 |5467.50
197 rows selected
SQL right side join:
select b.period, b.codrec, b.rubriek,
sum(case when b.tot50 = 0 then 1.0 else 0.5 end) aantal,
sum(case when b.tot50 = 0 then b.todec else b.tot50 end) kostvraag,
sum(b.ztva) ztva,
sum(b.attri + case when b.rubriek = '2' and b.codrec = 'T' then b.mttva else 0.0 end) kostvergd,
sum(b.mttva) mttva
from record90s b
where b.period = (select parmstring from params where parmtype = 'periodKas')
group by b.period, b.codrec, b.rubriek
;
PERIOD|&|RUB&|AANTAL|KOSTVRAAG |ZTVA |KOSTVERGD |MTTVA
----------------------------------------------------------------------------------------------
200901|R|1 |19903&|27289260.22 |3809145.73 |27289260.22 |3809145.73
200901|R|2 |1932.0|6509670.94 |1452124.11 |6509670.94 |1452124.11
200901|R|3 |65.0 |929992.76 |130981.36 |929992.76 |130981.36
200901|R|4 |36.0 |473693.14 |25931.23 |473693.14 |25931.23
200901|T|1 |19903&|27289260.22 |3809145.73 |28277826.96 |3809177.90
200901|T|2 |1932.0|6509670.94 |1452124.11 |5521114.81 |1452124.11
200901|T|3 |65.0 |929992.76 |130981.36 |929992.76 |130981.36
200901|T|4 |36.0 |473693.14 |25931.23 |473693.14 |25931.23
8 rows selected
C:\RDR_TEST\RdrTrans>java org.apache.derby.tools.sysinfo
------------------ Java Information ------------------
Java Version: 1.6.0_11
Java Vendor: Sun Microsystems Inc.
Java home: C:\Program Files\Java\jre6
Java classpath: .; C:\Sun\SDK\jdk\lib\tools.jar;
OS name: Windows XP
OS architecture: x86
OS version: 5.1
Java user name: patrickm
Java user home: C:\Documents and Settings\patrickm
Java user dir: C:\RDR_TEST\RdrTrans
java.specification.name: Java Platform API Specification
java.specification.version: 1.6
--------- Derby Information --------
JRE - JDBC: Java SE 6 - JDBC 4.0
[C:\Program Files\Java\jre6\lib\ext\derby.jar] 10.4.2.1 - (706043)
[C:\Program Files\Java\jre6\lib\ext\derbytools.jar] 10.4.2.1 - (706043)
[C:\Program Files\Java\jre6\lib\ext\derbynet.jar] 10.4.2.1 - (706043)
[C:\Program Files\Java\jre6\lib\ext\derbyclient.jar] 10.4.2.1 - (706043)
------------------------------------------------------
----------------- Locale Information -----------------
------------------------------------------------------
Sincerely,
Patrick Muls
Datassur<http://www.datassur.be/>
0032 25 47 58 59
[cid:image003.gif@01C99E4C.79B3CF20]
Re: Outer join misses rows, behaves more like inner join.
Posted by Stanley Bradbury <St...@gmail.com>.
Hi Patrick -
I am speculating that the where-clause (where cie = '00014') is causing
a problem. If your remove this clause or move it up into the LEFT hand
side query is your record listed.
NOTE that the row missing does not have the column 'cie' at all [ select
b.period, b.codrec, b.rubriek,] and so the column is, in-effect null and
would be eliminated by the condition cie = '00014' when the overall
filter is applied.
MULS PATRICK wrote:
>
> Hello,
>
> I’ve been trying to get global totals for a table joined to the totals
> by company. Looking at the first company (00014) I notice that the
> global total for codrec ‘T’ and rubriek ‘4’ is missing. I’ve tried
> changing the order they are selected to use LEFT as well as RIGHt
> OUTER JOINs, but the result is always the same.
>
> *_SQL outer join having missing rows:_*
>
> select r.*, t.aantal totAantal, t.kostvraag totKvraag, t.mttva
> totMttva, t.kostvergd totKvergd, t.ztva totZtva from
>
> (select a.ciebcc Cie, a.period, a.codrec C, a.rubriek R,
>
> sum(case when a.tot50 = 0 then 1.0 else 0.5 end) aantal,
>
> sum(case when a.tot50 = 0 then a.todec else a.tot50 end) kostvraag,
>
> sum(a.ztva) ztva,
>
> sum(a.attri + case when a.rubriek = '2' and a.codrec = 'T' then
> a.mttva else 0.0 end) kostvergd,
>
> sum(a.mttva) mttva
>
> from record90s a
>
> where a.period = (select parmstring from params where parmtype =
> 'periodKas')
>
> group by a.ciebcc, a.period, a.codrec, a.rubriek
>
> ) r
>
> right outer join
>
> (select b.period, b.codrec, b.rubriek,
>
> sum(case when b.tot50 = 0 then 1.0 else 0.5 end) aantal,
>
> sum(case when b.tot50 = 0 then b.todec else b.tot50 end) kostvraag,
>
> sum(b.ztva) ztva,
>
> sum(b.attri + case when b.rubriek = '2' and b.codrec = 'T' then
> b.mttva else 0.0 end) kostvergd,
>
> sum(b.mttva) mttva
>
> from record90s b
>
> where b.period = (select parmstring from params where parmtype =
> 'periodKas')
>
> group by b.period, b.codrec, b.rubriek
>
> ) t
>
> on r.period = t.period
>
> and r.c = t.codrec
>
> and r.r = t.rubriek
>
> where cie = '00014';
>
> CIE |PERIOD|C|R|AANTAL|KOSTVRAAG |ZTVA |KOSTVERGD |MTTVA
> |TOTAA&|TOTKVRAAG |TOTMTTVA |TOTKVERGD |TOTZTVA
>
> ----------------------------------------------------------------------------------------------------------------------
>
> 00014|200901|R|1|1607.0|2191773.38|301053.20|2191773.38|301053.20|19903&|27289260.22|3809145.73|27289260.22|3809145.73
>
> 00014|200901|R|2|131.5 |471509.34 |104688.17|471509.34
> |104688.17|1932.0|6509670.94 |1452124.11|6509670.94 |1452124.11
>
> 00014|200901|R|3|9.0 |114574.95 |13433.09 |114574.95 |13433.09 |65.0
> |929992.76 |130981.36 |929992.76 |130981.36
>
> 00014|200901|R|4|6.0 |91882.81 |1168.32 |91882.81 |1168.32 |36.0
> |473693.14 |25931.23 |473693.14 |25931.23
>
> 00014|200901|T|1|1780.5|2411849.31|322914.52|2462206.03|318533.43|19903&|27289260.22|3809177.90|28277826.96|3809145.73
>
> 00014|200901|T|2|126.0 |433931.18 |81295.02 |363851.11 |81295.02
> |1932.0|6509670.94 |1452124.11|5521114.81 |1452124.11
>
> 00014|200901|T|3|7.0 |108220.37 |8802.33 |108220.37 |8802.33 |65.0
> |929992.76 |130981.36 |929992.76 |130981.36
>
> MISSING:
>
> 00014|200901|T|4|0.0 |0.00 |0.00 |0.00 |0.00 |36.0 |473693.14
> |25931.23 |473693.14 |25931.23
>
> 7 rows selected
>
> *_SQL left side of join:_*
>
> select a.ciebcc cie, a.period, a.codrec, a.rubriek,
>
> sum(case when a.tot50 = 0 then 1.0 else 0.5 end) aantal,
>
> sum(case when a.tot50 = 0 then a.todec else a.tot50 end) kostvraag,
>
> sum(a.ztva) ztva,
>
> sum(a.attri + case when a.rubriek = '2' and a.codrec = 'T' then
> a.mttva else 0.0 end) kostvergd,
>
> sum(a.mttva) mttva
>
> from record90s b
>
> where a.period = (select parmstring from params where parmtype =
> 'periodKas')
>
> group by a.ciebcc, a.period, a.codrec, a.rubriek
>
> ;
>
> CIE |PERIOD|&|RUB&|AANTAL|KOSTVRAAG |ZTVA |KOSTVERGD |MTTVA
>
> ----------------------------------------------------------------------------------------------------
>
> 00014|200901|R|1 |1607.0|2191773.38 |301053.20 |2191773.38 |301053.20
>
> 00014|200901|R|2 |131.5 |471509.34 |104688.17 |471509.34 |104688.17
>
> 00014|200901|R|3 |9.0 |114574.95 |13433.09 |114574.95 |13433.09
>
> 00014|200901|R|4 |6.0 |91882.81 |1168.32 |91882.81 |1168.32
>
> 00014|200901|T|1 |1780.5|2411849.31 |322914.52 |2462206.03 |318533.43
>
> 00014|200901|T|2 |126.0 |433931.18 |81295.02 |363851.11 |81295.02
>
> 00014|200901|T|3 |7.0 |108220.37 |8802.33 |108220.37 |8802.33
>
> 00033|200901|R|1 |423.5 |577768.32 |77409.20 |577768.32 |77409.20
>
> 00033|200901|R|2 |46.5 |172482.73 |38288.43 |172482.73 |38288.43
>
> 00033|200901|R|3 |2.0 |23359.44 |4183.26 |23359.44 |4183.26
>
> 00033|200901|T|1 |425.0 |616691.30 |82977.66 |620792.33 |80882.95
>
> 00033|200901|T|2 |41.0 |162887.66 |33827.34 |140646.85 |33827.34
>
> 00033|200901|T|4 |3.0 |33422.94 |2175.53 |33422.94 |2175.53
>
> 00037|200901|R|1 |1124.0|1570281.01 |219638.79 |1570281.01 |219638.79
>
> 00037|200901|R|2 |121.0 |428716.03 |87238.72 |428716.03 |87238.72
>
> 00037|200901|R|3 |3.0 |49230.77 |5050.50 |49230.77 |5050.50
>
> 00037|200901|R|4 |1.0 |10080.27 |943.60 |10080.27 |943.60
>
> 00037|200901|T|1 |1225.0|1597641.35 |238207.68 |1712641.79 |246056.72
>
> 00037|200901|T|2 |120.5 |393165.15 |94802.95 |340076.82 |94802.95
>
> 00037|200901|T|3 |1.0 |15999.98 |3360.00 |15999.98 |3360.00
>
> 00039|200901|R|1 |3483.0|4971965.49 |694675.55 |4971965.49 |694675.55
>
> 00039|200901|R|2 |337.5 |1116204.65 |253327.20 |1116204.65 |253327.20
>
> 00039|200901|R|3 |11.0 |162475.53 |35283.67 |162475.53 |35283.67
>
> 00039|200901|R|4 |11.0 |150630.86 |7229.30 |150630.86 |7229.30
>
> 00039|200901|T|1 |3143.0|4198976.45 |556873.38 |4387859.01 |566893.88
>
> 00039|200901|T|2 |324.5 |1096781.74 |245493.63 |920423.81 |245493.63
>
> 00039|200901|T|3 |9.0 |130687.33 |23251.07 |130687.33 |23251.07
>
> 00039|200901|T|4 |5.0 |61324.58 |5704.92 |61324.58 |5704.92
>
> 00051|200901|R|1 |971.5 |1333058.82 |188161.46 |1333058.82 |188161.46
>
> 00051|200901|R|2 |77.0 |274144.32 |59868.70 |274144.32 |59868.70
>
> 00051|200901|R|3 |1.0 |16046.51 |5878.27 |16046.51 |5878.27
>
> 00051|200901|T|1 |812.5 |1126609.74 |138810.49 |1169961.20 |141631.33
>
> 00051|200901|T|2 |71.5 |241245.80 |46509.60 |199351.02 |46509.60
>
> 00051|200901|T|3 |10.0 |133337.08 |15767.27 |133337.08 |15767.27
>
> 00051|200901|T|4 |1.0 |14739.46 |0.00 |14739.46 |0.00
>
> 00058|200901|R|1 |545.5 |690930.17 |100656.36 |690930.17 |100656.36
>
> 00058|200901|R|2 |51.0 |142972.59 |34006.58 |142972.59 |34006.58
>
> 00058|200901|R|3 |5.0 |89749.18 |12286.53 |89749.18 |12286.53
>
> 00058|200901|R|4 |1.0 |14617.14 |2485.28 |14617.14 |2485.28
>
> 00058|200901|T|1 |624.5 |883522.01 |108282.75 |914719.11 |105641.14
>
> 00058|200901|T|2 |68.0 |231253.47 |48147.00 |193243.98 |48147.00
>
> 00058|200901|T|3 |1.0 |10582.68 |0.00 |10582.68 |0.00
>
> 00058|200901|T|4 |1.0 |14314.91 |1352.83 |14314.91 |1352.83
>
> 00067|200901|R|1 |1122.5|1530591.45 |217603.01 |1530591.45 |217603.01
>
> 00067|200901|R|2 |105.5 |332040.68 |72403.22 |332040.68 |72403.22
>
> 00067|200901|R|3 |4.0 |53617.24 |0.00 |53617.24 |0.00
>
> 00067|200901|R|4 |2.0 |28888.30 |2455.58 |28888.30 |2455.58
>
> 00067|200901|T|1 |892.5 |1275733.20 |175016.47 |1260508.31 |165306.20
>
> 00067|200901|T|2 |92.0 |275159.55 |59238.37 |239194.53 |59238.37
>
> 00067|200901|T|3 |3.0 |38873.66 |5925.43 |38873.66 |5925.43
>
> 00067|200901|T|4 |5.0 |56019.15 |4075.60 |56019.15 |4075.60
>
> 00079|200901|R|1 |2612.5|3624127.74 |499927.02 |3624127.74 |499927.02
>
> 00079|200901|R|2 |266.0 |891375.21 |190304.24 |891375.21 |190304.24
>
> 00079|200901|R|3 |6.0 |86307.20 |8384.25 |86307.20 |8384.25
>
> 00079|200901|R|4 |6.0 |67781.08 |2691.63 |67781.08 |2691.63
>
> 00079|200901|T|1 |2512.5|3481327.79 |511185.02 |3586156.34 |502305.67
>
> 00079|200901|T|2 |265.5 |1003490.46 |235098.78 |845595.09 |235098.78
>
> 00079|200901|T|3 |15.0 |220169.62 |44189.92 |220169.62 |44189.92
>
> 00079|200901|T|4 |8.0 |127906.84 |5567.47 |127906.84 |5567.47
>
> 00087|200901|R|1 |363.5 |542068.32 |76081.12 |542068.32 |76081.12
>
> 00087|200901|R|2 |28.5 |98232.20 |25220.82 |98232.20 |25220.82
>
> 00087|200901|R|3 |1.0 |12625.41 |2360.40 |12625.41 |2360.40
>
> 00087|200901|T|1 |333.5 |444310.24 |54198.86 |464730.42 |55473.11
>
> 00087|200901|T|2 |32.0 |107041.65 |22332.14 |95394.28 |22332.14
>
> 00087|200901|T|3 |2.0 |22868.57 |0.00 |22868.57 |0.00
>
> 00087|200901|T|4 |2.0 |19233.23 |943.60 |19233.23 |943.60
>
> 00096|200901|R|1 |1126.5|1484694.67 |203579.44 |1484694.67 |203579.44
>
> 00096|200901|R|2 |90.0 |312195.68 |68077.20 |312195.68 |68077.20
>
> 00096|200901|R|3 |2.0 |25313.25 |2554.65 |25313.25 |2554.65
>
> 00096|200901|R|4 |2.0 |20711.82 |0.00 |20711.82 |0.00
>
> 00096|200901|T|1 |1154.5|1642980.48 |220372.74 |1658306.25 |220176.80
>
> 00096|200901|T|2 |92.0 |326901.03 |73872.86 |283880.46 |73872.86
>
> 00096|200901|T|3 |5.0 |69660.97 |9481.78 |69660.97 |9481.78
>
> 00096|200901|T|4 |3.0 |36923.87 |0.00 |36923.87 |0.00
>
> 00097|200901|R|1 |830.5 |1079994.58 |145776.36 |1079994.58 |145776.36
>
> 00097|200901|R|2 |75.5 |248061.82 |58918.75 |248061.82 |58918.75
>
> 00097|200901|R|3 |3.0 |41706.47 |6365.35 |41706.47 |6365.35
>
> 00097|200901|R|4 |1.0 |10630.52 |1844.97 |10630.52 |1844.97
>
> 00097|200901|T|1 |776.5 |1130426.38 |161946.28 |1185791.65 |161930.44
>
> 00097|200901|T|2 |95.0 |258640.40 |58988.31 |231093.47 |58988.31
>
> 00097|200901|T|4 |1.0 |11082.00 |0.00 |11082.00 |0.00
>
> 00124|200901|R|1 |137.5 |207379.15 |27970.17 |207379.15 |27970.17
>
> 00124|200901|R|2 |18.0 |64615.74 |14414.92 |64615.74 |14414.92
>
> 00124|200901|T|1 |159.5 |187206.42 |28775.86 |205941.74 |30184.68
>
> 00124|200901|T|2 |20.0 |57123.24 |10180.03 |51750.78 |10180.03
>
> 00126|200901|R|1 |43.0 |51197.26 |6865.34 |51197.26 |6865.34
>
> 00126|200901|R|2 |1.0 |1212.55 |199.51 |1212.55 |199.51
>
> 00126|200901|R|4 |1.0 |17307.95 |1644.65 |17307.95 |1644.65
>
> 00129|200901|R|1 |93.0 |137898.03 |20709.54 |137898.03 |20709.54
>
> 00129|200901|R|2 |12.0 |42877.68 |10404.10 |42877.68 |10404.10
>
> 00129|200901|T|1 |69.0 |104875.22 |15049.80 |103428.82 |14120.09
>
> 00129|200901|T|2 |5.0 |12083.80 |4025.99 |9723.14 |4025.99
>
> 00130|200901|R|1 |4.0 |2224.16 |386.01 |2224.16 |386.01
>
> 00130|200901|R|2 |1.0 |3112.00 |824.38 |3112.00 |824.38
>
> 00130|200901|T|1 |13.5 |19789.87 |3129.01 |19160.46 |2913.49
>
> 00130|200901|T|2 |2.0 |4980.70 |1245.25 |3524.11 |1245.25
>
> 00134|200901|R|1 |1.0 |83.03 |14.41 |83.03 |14.41
>
> 00134|200901|R|2 |1.0 |963.00 |156.20 |963.00 |156.20
>
> 00134|200901|T|1 |1.0 |605.00 |105.00 |1361.75 |222.32
>
> 00145|200901|R|1 |437.5 |625857.53 |85971.26 |625857.53 |85971.26
>
> 00145|200901|R|2 |50.0 |181251.81 |40257.77 |181251.81 |40257.77
>
> 00145|200901|T|1 |426.5 |592989.61 |78031.70 |628889.22 |82451.04
>
> 00145|200901|T|2 |49.0 |152836.57 |35511.47 |135456.78 |35511.47
>
> 00145|200901|T|3 |1.0 |11381.64 |1374.22 |11381.64 |1374.22
>
> 00145|200901|T|4 |1.0 |15357.14 |0.00 |15357.14 |0.00
>
> 00165|200901|R|1 |2621.0|3470759.06 |486064.70 |3470759.06 |486064.70
>
> 00165|200901|R|2 |263.5 |859048.09 |195358.02 |859048.09 |195358.02
>
> 00165|200901|R|3 |12.0 |153087.55 |15531.46 |153087.55 |15531.46
>
> 00165|200901|R|4 |2.0 |22999.73 |1008.27 |22999.73 |1008.27
>
> 00165|200901|T|1 |2748.5|3687735.74 |525583.91 |3850730.77 |527771.21
>
> 00165|200901|T|2 |284.0 |910952.95 |202048.41 |765958.81 |202048.41
>
> 00165|200901|T|3 |2.0 |40524.46 |3276.00 |40524.46 |3276.00
>
> 00165|200901|T|4 |2.0 |24952.97 |2485.28 |24952.97 |2485.28
>
> 00167|200901|R|1 |231.5 |310658.08 |43511.25 |310658.08 |43511.25
>
> 00167|200901|R|2 |21.0 |67391.24 |11810.87 |67391.24 |11810.87
>
> 00167|200901|R|3 |2.0 |33381.76 |7479.43 |33381.76 |7479.43
>
> 00167|200901|T|1 |207.0 |298599.84 |46515.22 |293579.25 |44817.88
>
> 00167|200901|T|2 |24.0 |77374.65 |19566.10 |61938.14 |19566.10
>
> 00179|200901|R|1 |82.5 |128603.93 |18928.06 |128603.93 |18928.06
>
> 00179|200901|R|2 |13.0 |51475.16 |11765.83 |51475.16 |11765.83
>
> 00179|200901|R|3 |1.0 |9596.50 |0.00 |9596.50 |0.00
>
> 00179|200901|R|4 |1.0 |11614.50 |2002.20 |11614.50 |2002.20
>
> 00196|200901|R|1 |554.5 |737630.29 |107057.55 |737630.29 |107057.55
>
> 00196|200901|R|2 |60.0 |188348.12 |43093.15 |188348.12 |43093.15
>
> 00196|200901|R|3 |2.0 |41343.95 |9636.90 |41343.95 |9636.90
>
> 00196|200901|R|4 |2.0 |26548.16 |2457.43 |26548.16 |2457.43
>
> 00196|200901|T|1 |704.0 |915412.90 |147649.86 |973342.35 |149453.48
>
> 00196|200901|T|2 |60.5 |189579.18 |42231.03 |152487.28 |42231.03
>
> 00196|200901|T|3 |1.0 |24644.00 |3255.00 |24644.00 |3255.00
>
> 00333|200901|R|1 |9.5 |20601.30 |2037.54 |20601.30 |2037.54
>
> 00333|200901|T|1 |4.0 |12043.97 |802.94 |8981.10 |666.95
>
> 00333|200901|T|4 |1.0 |11825.35 |0.00 |11825.35 |0.00
>
> 00394|200901|R|1 |1.0 |471.61 |81.85 |471.61 |81.85
>
> 00435|200901|R|1 |223.0 |292719.05 |42919.75 |292719.05 |42919.75
>
> 00435|200901|R|2 |25.0 |95693.96 |20138.27 |95693.96 |20138.27
>
> 00435|200901|T|1 |263.0 |347516.99 |58999.14 |371998.25 |59244.60
>
> 00435|200901|T|2 |31.0 |82768.26 |17272.53 |71377.01 |17272.53
>
> 00435|200901|T|3 |1.0 |10953.00 |1995.00 |10953.00 |1995.00
>
> 00497|200901|R|1 |47.0 |65728.75 |9401.21 |65728.75 |9401.21
>
> 00497|200901|R|2 |1.0 |2393.00 |472.50 |2393.00 |472.50
>
> 00497|200901|R|3 |1.0 |17577.05 |2553.60 |17577.05 |2553.60
>
> 00497|200901|T|1 |122.5 |174256.24 |23567.80 |178623.66 |23252.36
>
> 00497|200901|T|2 |13.0 |38359.20 |12101.51 |34426.96 |12101.51
>
> 00745|200901|R|1 |74.5 |104015.93 |11452.32 |104015.93 |11452.32
>
> 00745|200901|R|2 |6.0 |24529.85 |4648.00 |24529.85 |4648.00
>
> 00745|200901|T|1 |127.0 |220805.95 |10747.90 |208480.01 |9453.05
>
> 00745|200901|T|2 |6.0 |40592.36 |8203.26 |33821.99 |8203.26
>
> 00745|200901|T|3 |2.0 |25481.09 |1488.59 |25481.09 |1488.59
>
> 00745|200901|T|4 |1.0 |19693.96 |0.00 |19693.96 |0.00
>
> 00964|200901|R|1 |159.5 |237472.96 |35818.46 |237472.96 |35818.46
>
> 00964|200901|R|2 |11.0 |40335.10 |8468.59 |40335.10 |8468.59
>
> 00964|200901|T|1 |175.5 |227983.14 |37998.06 |234106.44 |36744.80
>
> 00964|200901|T|2 |9.0 |26089.01 |5569.25 |19580.55 |5569.25
>
> 00964|200901|T|3 |1.0 |11497.00 |0.00 |11497.00 |0.00
>
> 01037|200901|R|1 |88.0 |112335.46 |13552.11 |112335.46 |13552.11
>
> 01037|200901|R|2 |7.0 |29527.43 |8962.15 |29527.43 |8962.15
>
> 01037|200901|T|1 |100.0 |116615.97 |17772.06 |137208.69 |19807.02
>
> 01037|200901|T|2 |8.0 |25623.54 |6702.07 |19573.94 |6702.07
>
> 01037|200901|T|3 |1.0 |11831.55 |1769.25 |11831.55 |1769.25
>
> 01037|200901|T|4 |1.0 |12747.90 |1170.42 |12747.90 |1170.42
>
> 01093|200901|R|1 |152.0 |202311.81 |26957.10 |202311.81 |26957.10
>
> 01093|200901|R|2 |18.0 |65205.84 |15676.84 |65205.84 |15676.84
>
> 01093|200901|T|1 |166.5 |272096.57 |30392.52 |269002.57 |29688.48
>
> 01093|200901|T|2 |14.0 |45236.73 |9145.87 |36367.18 |9145.87
>
> 01093|200901|T|3 |1.0 |8841.02 |0.00 |8841.02 |0.00
>
> 01258|200901|R|1 |7.0 |6732.45 |1159.42 |6732.45 |1159.42
>
> 01258|200901|R|2 |2.0 |10082.31 |3834.99 |10082.31 |3834.99
>
> 01258|200901|T|1 |1.0 |1412.93 |238.46 |1361.76 |222.33
>
> 01396|200901|R|1 |101.0 |128000.62 |19902.02 |128000.62 |19902.02
>
> 01396|200901|R|2 |17.0 |48950.70 |11824.53 |48950.70 |11824.53
>
> 01396|200901|T|1 |150.0 |222310.87 |35144.31 |219103.51 |33361.80
>
> 01396|200901|T|2 |7.0 |29489.80 |8861.91 |28107.21 |8861.91
>
> 01396|200901|T|3 |1.0 |16014.00 |1995.00 |16014.00 |1995.00
>
> 01449|200901|R|1 |64.0 |92711.01 |13151.75 |92711.01 |13151.75
>
> 01449|200901|R|2 |12.0 |69699.94 |19235.41 |69699.94 |19235.41
>
> 01449|200901|T|1 |91.0 |119686.64 |19852.37 |132819.78 |20754.12
>
> 01449|200901|T|2 |6.0 |16099.06 |3936.02 |14529.03 |3936.02
>
> 01455|200901|R|1 |311.0 |430359.21 |62932.68 |430359.21 |62932.68
>
> 01455|200901|R|2 |31.5 |94820.43 |21554.99 |94820.43 |21554.99
>
> 01455|200901|T|1 |319.5 |443803.37 |73927.03 |446676.62 |71090.31
>
> 01455|200901|T|2 |34.0 |124661.98 |30679.76 |106984.68 |30679.76
>
> 01455|200901|T|4 |1.0 |14148.84 |2455.58 |14148.84 |2455.58
>
> 02279|200901|R|1 |144.0 |189234.12 |29202.63 |189234.12 |29202.63
>
> 02279|200901|R|2 |21.0 |57261.76 |11369.11 |57261.76 |11369.11
>
> 02279|200901|T|1 |260.0 |365001.08 |59592.54 |413346.96 |63739.76
>
> 02279|200901|T|2 |18.5 |91441.53 |22747.37 |76670.78 |22747.37
>
> 02279|200901|T|3 |1.0 |18424.74 |5050.50 |18424.74 |5050.50
>
> 02314|200901|R|1 |14.5 |20337.65 |2048.77 |20337.65 |2048.77
>
> 02314|200901|T|1 |11.0 |18006.77 |2687.60 |14423.44 |1889.71
>
> 02393|200901|R|1 |50.5 |65388.60 |9561.24 |65388.60 |9561.24
>
> 02393|200901|R|2 |6.0 |16569.74 |3661.71 |16569.74 |3661.71
>
> 02393|200901|T|1 |56.5 |70741.07 |11931.71 |76414.10 |12135.56
>
> 02393|200901|T|2 |8.0 |32610.93 |7022.78 |27407.51 |7022.78
>
> 02551|200901|R|1 |41.0 |51295.22 |6927.08 |51295.22 |6927.08
>
> 02551|200901|R|2 |4.0 |6370.24 |1650.24 |6370.24 |1650.24
>
> 02551|200901|T|1 |46.5 |59695.81 |9865.08 |65173.27 |10361.19
>
> 02551|200901|T|2 |5.0 |21269.36 |5467.50 |18677.51 |5467.50
>
> 197 rows selected
>
> *_SQL right side join:_*
>
> select b.period, b.codrec, b.rubriek,
>
> sum(case when b.tot50 = 0 then 1.0 else 0.5 end) aantal,
>
> sum(case when b.tot50 = 0 then b.todec else b.tot50 end) kostvraag,
>
> sum(b.ztva) ztva,
>
> sum(b.attri + case when b.rubriek = '2' and b.codrec = 'T' then
> b.mttva else 0.0 end) kostvergd,
>
> sum(b.mttva) mttva
>
> from record90s b
>
> where b.period = (select parmstring from params where parmtype =
> 'periodKas')
>
> group by b.period, b.codrec, b.rubriek
>
> ;
>
> PERIOD|&|RUB&|AANTAL|KOSTVRAAG |ZTVA |KOSTVERGD |MTTVA
>
> ----------------------------------------------------------------------------------------------
>
> 200901|R|1 |19903&|27289260.22 |3809145.73 |27289260.22 |3809145.73
>
> 200901|R|2 |1932.0|6509670.94 |1452124.11 |6509670.94 |1452124.11
>
> 200901|R|3 |65.0 |929992.76 |130981.36 |929992.76 |130981.36
>
> 200901|R|4 |36.0 |473693.14 |25931.23 |473693.14 |25931.23
>
> 200901|T|1 |19903&|27289260.22 |3809145.73 |28277826.96 |3809177.90
>
> 200901|T|2 |1932.0|6509670.94 |1452124.11 |5521114.81 |1452124.11
>
> 200901|T|3 |65.0 |929992.76 |130981.36 |929992.76 |130981.36
>
> 200901|T|4 |36.0 |473693.14 |25931.23 |473693.14 |25931.23
>
> 8 rows selected
>
> C:\RDR_TEST\RdrTrans>java org.apache.derby.tools.sysinfo
>
> ------------------ Java Information ------------------
>
> Java Version: 1.6.0_11
>
> Java Vendor: Sun Microsystems Inc.
>
> Java home: C:\Program Files\Java\jre6
>
> Java classpath: .; C:\Sun\SDK\jdk\lib\tools.jar;
>
> OS name: Windows XP
>
> OS architecture: x86
>
> OS version: 5.1
>
> Java user name: patrickm
>
> Java user home: C:\Documents and Settings\patrickm
>
> Java user dir: C:\RDR_TEST\RdrTrans
>
> java.specification.name: Java Platform API Specification
>
> java.specification.version: 1.6
>
> --------- Derby Information --------
>
> JRE - JDBC: Java SE 6 - JDBC 4.0
>
> [C:\Program Files\Java\jre6\lib\ext\derby.jar] 10.4.2.1 - (706043)
>
> [C:\Program Files\Java\jre6\lib\ext\derbytools.jar] 10.4.2.1 - (706043)
>
> [C:\Program Files\Java\jre6\lib\ext\derbynet.jar] 10.4.2.1 - (706043)
>
> [C:\Program Files\Java\jre6\lib\ext\derbyclient.jar] 10.4.2.1 - (706043)
>
> ------------------------------------------------------
>
> ----------------- Locale Information -----------------
>
> ------------------------------------------------------
>
> Sincerely,
>
> Patrick Muls
>
> Datassur <http://www.datassur.be/>
>
> 0032 25 47 58 59
>