March 18, 2004 at 4:30 pm
I am trying to sum a case statment an then after the else statement another sum case statement. Does this work? I caanot get it to work!
Here is the case statement:
SELECT
sum(case dbo.D_VEHICLE_STATUS.LIFECYCLE_STATUS_CODE when '0' then dbo.F_VEHICLE_STATUS.VEHICLE_STATUS_UNITS
else sum(case when dbo.D_PORT.PHYSICAL_PORT_CODE = 'PMA' then
dbo.F_VEHICLE_STATUS.VEHICLE_STATUS_UNITS
else 0 end)
FROM
dbo.D_PORT,
dbo.D_VEHICLE_STATUS,
dbo.F_VEHICLE_STATUS,
dbo.D_COUNTRY,
dbo.D_TIME
WHERE
( dbo.D_TIME.TIME_KEY=dbo.F_VEHICLE_STATUS.TIME_KEY )
AND ( dbo.D_VEHICLE_STATUS.VEHICLE_STATUS_KEY=dbo.F_VEHICLE_STATUS.VEHICLE_STATUS_KEY )
AND ( dbo.F_VEHICLE_STATUS.PORT_KEY=dbo.D_PORT.PORT_KEY )
AND ( dbo.D_COUNTRY.COUNTRY_KEY=dbo.F_VEHICLE_STATUS.COUNTRY_KEY )
AND (
( dbo.D_COUNTRY.COUNTRY_CD = 'PR ' )
AND ( dbo.D_TIME.LAST_OPERATION_DATE_IND = 'T' )
)
March 19, 2004 at 1:39 am
sum( case dbo.D_VEHICLE_STATUS.LIFECYCLE_STATUS_CODE
when '0' then dbo.F_VEHICLE_STATUS.VEHICLE_STATUS_UNITS
else sum(
( case when dbo.D_PORT.PHYSICAL_PORT_CODE = 'PMA' then
dbo.F_VEHICLE_STATUS.VEHICLE_STATUS_UNITS
else 0 end ) end )
every case must have a end.
March 19, 2004 at 5:22 am
Additionally you should always simplify the CASE statement if possible.
For both situations you return the same thing
dbo.F_VEHICLE_STATUS.VEHICLE_STATUS_UNITS
So instead of a CASE WHEN...ELSE CASE WHEN... or doing CASE WHEN...WHEN...ELSE try this
sum(case when dbo.D_VEHICLE_STATUS.LIFECYCLE_STATUS_CODE = '0' OR dbo.D_PORT.PHYSICAL_PORT_CODE = 'PMA'
then dbo.F_VEHICLE_STATUS.VEHICLE_STATUS_UNITS
else 0
end)
By doing this the overall code will be smaller and simpler to read.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply