| Author |
 Topic  |
|
|
fholemans
Starting Member
Belgium
12 Posts |
 Posted - 12/03/2012 : 08:35:07
|
Hi,
I have an issue concerning a SUM in an update.
Indeed, I need to copy the sum of three members of a dimension, in the same dimension but in a different member.
Hereafter the query, I have tried but without success :
Update tblFactMGMTAPP
SET M_ACCOUNT='M506'
WHERE SIGNEDDATA = (SELECT SUM(SIGNEDDATA) from tblFactMGMTAPP where M_ACCOUNT IN ('M881', 'M882','M883'))
Thanks for your help,
François |
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47065 Posts |
Posted - 12/03/2012 : 08:50:08
|
sounds like this
Update tblFactMGMTAPP
SET SIGNEDDATA = (SELECT SUM(SIGNEDDATA) from tblFactMGMTAPP where M_ACCOUNT IN ('M881', 'M882','M883'))
WHERE M_ACCOUNT='M506'
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
 |
|
|
fholemans
Starting Member
Belgium
12 Posts |
Posted - 12/03/2012 : 08:56:28
|
Unfortunately it doesn't work neither.
So my goal is to sum the M_ACCOUNT 'M881' 'M882' and 'M883' and to inject this sum in one M_ACCOUNT='M506'. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47065 Posts |
Posted - 12/03/2012 : 09:14:06
|
thats exactky what above UPDATE does.
Whats the issue with above query? are you getting some error?
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 12/03/2012 : 09:14:11
|
That is what exactly the code that Visakh posted does IF there is an already existing row for M_ACCOUNT = M506. If there is no row for that account, i.e., you are trying to insert a new row, use the following query. I am listing only the two columns that you described in your postings - there may be other required columns that you will have to list in the columns list and select listINSERT INTO tblFactMGMTAPP
(
M_ACCOUNT,
SIGNEDDATA
)
SELECT 'M506',
SUM(SIGNEDDATA)
FROM tblFactMGMTAPP
WHERE M_ACCOUNT IN ('M881', 'M882', 'M883') |
|
|
|
fholemans
Starting Member
Belgium
12 Posts |
Posted - 12/03/2012 : 10:09:41
|
Vikash,
No error but any rows where affected by the update.
Sunitabeck,
Indeed, I have create this new member in my dimension M_ACCOUNT.
But I have tried your query (hereafter)
INSERT INTO tblFactMGMTAPP
(
M_ACCOUNT,
SIGNEDDATA,
GROUPS,
CATEGORY,
TIMEID,
DIRECTIONS,
PISTES
)
SELECT 'M506',
SUM(SIGNEDDATA),
'LC',
'ACTUAL',
'20120600',
'Z_STAT',
'PIMDE'
FROM tblFactMGMTAPP
WHERE M_ACCOUNT IN ('M881', 'M882', 'M883') and DIRECTIONS='Z_STAT' and PISTES='PIMDE' and CATEGORY='ACTUAL' and GROUPS='LC' and TIMEID='20120600'
but I receive this error message :
"Cannot insert the value NULL into column 'SIGNEDDATA'. Column does not allow nulls. Insert Fails"
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47065 Posts |
Posted - 12/03/2012 : 10:23:07
|
Does SIGNEDDATA field have any non NULL values for records having M_ACCOUNT values as 'M881', 'M882', 'M883'?
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
fholemans
Starting Member
Belgium
12 Posts |
Posted - 12/03/2012 : 10:51:42
|
Yes they are some NON NULL data on SIGNEDDATA because when I write this query :
select SUM(SIGNEDDATA)from tblFactMgmtApp where M_ACCOUNT IN ('M881','M882','M883') and PISTES='PIMDE' and GROUPS='LC' and TIMEID='20120600' and CATEGORY='ACTUAL'
My result is :
which is equal to the sum of M881, M882 and M883
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47065 Posts |
Posted - 12/03/2012 : 11:01:40
|
whats about DIRECTIONS field value in that case?
you have an additional condition DIRECTIONS = 'Z_STAT' also in the where so worth checking if you get any sum after adding that
check below code
select SUM(SIGNEDDATA)from tblFactMgmtApp where M_ACCOUNT IN ('M881','M882','M883') and PISTES='PIMDE' and GROUPS='LC' and TIMEID='20120600' and CATEGORY='ACTUAL'
AND DIRECTIONS = 'Z_STAT'
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
fholemans
Starting Member
Belgium
12 Posts |
Posted - 12/03/2012 : 11:36:24
|
Thanks a lot !
Now it works.
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47065 Posts |
Posted - 12/03/2012 : 11:41:07
|
welcome
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
fholemans
Starting Member
Belgium
12 Posts |
Posted - 12/04/2012 : 03:49:03
|
I have a last question :
So this query works well :
INSERT INTO tblFactMGMTAPP
(M_ACCOUNT,SIGNEDDATA,GROUPS,CATEGORY,TIMEID,DIRECTIONS,PISTES)
SELECT 'M506',SUM(SIGNEDDATA),'LC','ACTUAL','20120600','Z_STAT','PIMDE'
FROMtblFactMGMTAPP
WHERE M_ACCOUNT IN ('M881', 'M882', 'M883') and DIRECTIONS='Z_STAT' and PISTES='PIMDE' and CATEGORY='ACTUAL' and GROUPS='LC' and TIMEID='20120600'
but I have to work on different times, different PISTES and different CATEGORY so I want to know if there is a solution with the query above to go faster. Because now I have to change each time the dimension.
Thanks in advance
|
|
|
|
bandi
Flowing Fount of Yak Knowledge
India
1419 Posts |
Posted - 12/04/2012 : 05:17:58
|
Case1: For all different values of Directions, timeid, pistes
INSERT INTO tblFactMGMTAPP
(M_ACCOUNT,SIGNEDDATA,GROUPS,CATEGORY,TIMEID,DIRECTIONS,PISTES)
SELECT 'M506',SUM(SIGNEDDATA),'LC','ACTUAL',Directions, timeid, pistes
FROMtblFactMGMTAPP
WHERE M_ACCOUNT IN ('M881', 'M882', 'M883') and CATEGORY='ACTUAL' and GROUPS='LC'
GROUP BY Directions, timeid, pistes
Case2: For specific Directions, timeid, pistes values
DECLARE @timeid date = '20120600'
DECLARE @PISTES varchar(20) = 'PIMDE'
DECLARE @dirctn varchar(10) = 'Z_STAT'
INSERT INTO tblFactMGMTAPP
(M_ACCOUNT,SIGNEDDATA,GROUPS,CATEGORY,TIMEID,DIRECTIONS,PISTES)
SELECT 'M506',SUM(SIGNEDDATA),'LC','ACTUAL',@timeid,@dirctn,@dirctn
FROMtblFactMGMTAPP
WHERE M_ACCOUNT IN ('M881', 'M882', 'M883') and DIRECTIONS=@dirctn and PISTES=@PIMDE and CATEGORY='ACTUAL' and GROUPS='LC' and TIMEID=@timeid
--
Chandu |
|
|
|
fholemans
Starting Member
Belgium
12 Posts |
Posted - 12/04/2012 : 06:31:04
|
Thanks a lot for all you answers.
It works perfect now ;-) |
|
|
|
bandi
Flowing Fount of Yak Knowledge
India
1419 Posts |
Posted - 12/04/2012 : 07:27:10
|
quote:
Originally posted by fholemans
Thanks a lot for all you answers.
It works perfect now ;-)
Welcome
--
Chandu |
|
|
| |
Topic |
|
Update + Sum
