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 Go to next message
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
icon10.gif  Re: SUM expression not working. [message #355926 is a reply to message #355925] Tue, 28 October 2008 10:17 Go to previous messageGo to next message
Junes
Messages: 5
Registered: October 2008
Junior Member
I worked out the answer

group by sss.description,sss.model,trunc((ssc.totalmb/1024),2)

Thanks
Re: SUM expression not working. [message #355943 is a reply to message #355926] Tue, 28 October 2008 11:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Next time, before posting, Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Use SQL*Plus and copy and paste your session.

Regards
Michel
Incorrect output for SUM [message #356117 is a reply to message #355925] Wed, 29 October 2008 11:19 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
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 #356120 is a reply to message #356117] Wed, 29 October 2008 11:28 Go to previous messageGo to next message
rodolpho
Messages: 6
Registered: August 2008
Location: Rio de Janeiro, Brazil
Junior Member
Junes,

Try do it:
sum(trunc( (svs.blocksize*svs.consumableblocks) / ((1024*1024)*1024),2 )) AS AVAILABLE_SPACE_GB,
Re: Incorrect output for SUM [message #356121 is a reply to message #356117] Wed, 29 October 2008 11:35 Go to previous message
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
Previous Topic: return correct ww
Next Topic: Connect By is looping
Goto Forum:
  


Current Time: Sun Dec 11 00:17:13 CST 2016

Total time taken to generate the page: 0.06352 seconds