Home » SQL & PL/SQL » SQL & PL/SQL » sum expression oddity (merged 2)
| sum expression oddity (merged 2) [message #355925] |
Tue, 28 October 2008 10:00  |
Junes
Messages: 5
Registered: October 2008
|
Junior Member |
|
|
I have written the following code and i am trying to add up the total column of the 4th line statement, but i do a SUM, the expression fails with a syntax error.
If i comment out statment 1,2,3 and do a SUM for the 4th line statement it works, but i need all statements.
select
sss.description STG_DESC,
sss.model STG_MODEL,
trunc((ssc.totalmb/1024),2) as STG_TOTAL_GB,
trunc( (svs.blocksize*svs.consumableblocks) / (1024*1024*1024),2 ) AS AVAILABLE_SPACE
....
How can i have the output of statement 1, 2, 3 and 4 show on one line.
The whole code
select
sss.description STG_DESC,
sss.model STG_MODEL,
trunc((ssc.totalmb/1024),2) as STG_TOTAL_GB,
trunc( (svs.blocksize*svs.consumableblocks) / (1024*1024*1024),2 ) AS CHESTER_USP_AVAILABLE_SPACE
from
mvc_storagevolumesummaryvw svs,
mvc_lunmappingvw lm,
mvc_protocolcontrollervw pc,
mvc_portcontrollermapvw pcm,
mvc_portsummaryvw psh, -- to get host port info
mvc_portsummaryvw pss, -- to get storage port info
mvc_cardsummaryvw cs,
mvc_hostsummaryvw hs,
mvc_storgaepoolsummaryvw sps,
mvc_storagesystemsummaryvw sss,
(select sc.storagesystemid,
sc.collectiontime,
sc.availablemb,
sc.provisionedmb,
sc.rawstoragemb,
sc.totalmb,
sc.availableports,
sc.connectedports,
sc.totalports
from mvc_storagesystemconfigvw sc
where sc.collectiontime = (select max(collectiontime) from mvc_storagesystemconfigvw where storagesystemid = sc.storagesystemid
)
) ssc
where
sss.storagesystemid = ssc.storagesystemid
and svs.storagevolumeid = lm.storage_volume_id(+)
and pc.id(+) = lm.storage_system_port_id
and pcm.controllerid(+) = pc.id
and pss.portid(+) = pcm.portid
and psh.wwn(+) = lm.initiator
and cs.cardid(+) = psh.containerid
and hs.hostid(+) = cs.containerid
and sps.storagepoolid(+) = svs.poolid
and svs.storagesystemname like '%Chester USP - CRXUSPSN%'
and sss.description NOT LIKE '%CRXUSPVSND%'
and sss.description NOT LIKE '%LONUSPSN%'
and sss.description NOT LIKE '%CROUSPSN%'
and pc.name is null
and sss.model='USP'
Output for the code.
HDS Storage System Chester USP - CRXUSPSN USP 7363.94 10.41
HDS Storage System Chester USP - CRXUSPSN USP 7363.94 10.41
HDS Storage System Chester USP - CRXUSPSN USP 7363.94 10.41
|
|
|
|
|
|
|
|
| Incorrect output for SUM [message #356117 is a reply to message #355925] |
Wed, 29 October 2008 11:19  |
Junes
Messages: 5
Registered: October 2008
|
Junior Member |
|
|
I have been ranking my brain old day and i still can not understand why the following script in not working correctly.
I get a output, but not the correct info.
The following synatx is casusing me the issue
sum(trunc((ssc.totalmb/1024),2)) as TOTAL_SPACE_GB
select
sum(trunc( (svs.blocksize*svs.consumableblocks) / (1024*1024*1024),2 )) AS AVAILABLE_SPACE_GB,
sum(trunc((ssc.totalmb/1024),2)) as TOTAL_SPACE_GB
from
mvc_storagevolumesummaryvw svs,
mvc_lunmappingvw lm,
mvc_protocolcontrollervw pc,
mvc_storagesystemsummaryvw sss,
(select sc.storagesystemid,
sc.collectiontime,
sc.totalmb
from mvc_storagesystemconfigvw sc
where sc.collectiontime = (select max(collectiontime) from mvc_storagesystemconfigvw where storagesystemid = sc.storagesystemid
)
) ssc
where
sss.storagesystemid = ssc.storagesystemid
and svs.storagesystemid = sss.storagesystemid
and svs.storagevolumeid = lm.storage_volume_id(+)
and pc.id(+) = lm.storage_system_port_id
and svs.storagesystemname like '%USPSN%'
and svs.storagesystemname NOT like '%USPV%'
and pc.name is null
and sss.model='USP'
If i run the code just to get the TOTAL_SPACE_GB, then i get the correct information
select
sum(trunc((ssc.totalmb/1024),2)) as STG_TOTAL_GB
from
mvc_storagesystemsummaryvw sss,
(select sc.storagesystemid,
sc.collectiontime,
sc.totalmb
from mvc_storagesystemconfigvw sc
where sc.collectiontime = (select max(collectiontime) from mvc_storagesystemconfigvw where storagesystemid = sc.storagesystemid
)
) ssc
where
sss.storagesystemid = ssc.storagesystemid
and sss.model='USP'
But if i combine the Total Space with the AVAILABLE_SPACE_GB code, then it get me muliptle information for the Total Space.
I appericate any help....
|
|
|
|
| Re: Incorrect output for SUM [message #356119 is a reply to message #356117] |
Wed, 29 October 2008 11:25 |
 |
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
We have not your model.
We have not your data.
We have not your output.
We have not your requirement.
How could we answer to your question?
I bet there is a 1-n relation and you don't take care of it.
Regards
Michel
[Updated on: Wed, 29 October 2008 11:25] Report message to a moderator |
|
|
|
|
|
| Re: Incorrect output for SUM [message #356121 is a reply to message #356117] |
Wed, 29 October 2008 11:35 |
Junes
Messages: 5
Registered: October 2008
|
Junior Member |
|
|
I am expercing problems with the following syntax
sum(trunc((ssc.totalmb/1024),2)) as TOTAL_SPACE_GB
The ouput is
AVAILABLE_SPACE_GB TOTAL_SPACE_GB
29814.45 28332819.96
Don't worry i will figger it out myself
|
|
|
|
Goto Forum:
Current Time: Tue Dec 03 05:12:41 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
