Case statement not allowing > symbol

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Case statement not allowing > symbol

Author

Topic

rbeach1955
Starting Member

8 Posts

Posted - 2010-10-20 : 12:40:25

Below is a view created in mt SQL database. The statements "" will ot allow the view to be saved. If I change both of the statements to "1" it saves properly. Please help me format the view to allow the comparison values.

SELECT LocationsQueryA.Building, StandardTotalKVAbyBuildingAndLocation.LocationDesc,
Sum(CASE [DateMeasured]
WHEN Null
THEN [StandardTotalKVAbyBuildingAndLocation].[SumOfAmps]
ELSE CASE [MaxOfUpdated]
WHEN ([MaxOfUpdated]>[DateMeasured])
THEN [StandardTotalKVAbyBuildingAndLocation].[SumOfAmps]
ELSE [MeasuredCircuitInfoTotals].[SumOfAmps]
END
END) AS 'TotalAmps',
Sum(CASE [DateMeasured]
WHEN Null
THEN [StandardTotalKVAbyBuildingAndLocation].[SumOfAmps]*[Volts]
ELSE CASE [MaxOfUpdated]
WHEN ([MaxOfUpdated] > [DateMeasured])
THEN [StandardTotalKVAbyBuildingAndLocation].[SumOfAmps]*[Volts]
ELSE [MeasuredCircuitInfoTotals].[SumOfAmps]*[Volts]
END
END) AS TotalWatts
FROM (AuditQueries.LocationsQueryA LEFT JOIN AuditQueries.StandardTotalKVAbyBuildingAndLocation ON LocationsQueryA.FullLoc=StandardTotalKVAbyBuildingAndLocation.LocationDesc) LEFT JOIN AuditQueries.MeasuredCircuitInfoTotals ON StandardTotalKVAbyBuildingAndLocation.LocationDesc=MeasuredCircuitInfoTotals.Location
GROUP BY LocationsQueryA.Building, StandardTotalKVAbyBuildingAndLocation.LocationDesc

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-10-20 : 12:53:38

You actually have 2 different types of CASE statements here, a SIMPLE and a SEARCHED

This is copied from Books On Line and should give you enough info to make the changes (very eacy ones!).

Jim
Evaluates a list of conditions and returns one of multiple possible result expressions.

CASE has two formats:

The simple CASE function compares an expression to a set of simple expressions to determine the result.

The searched CASE function evaluates a set of Boolean expressions to determine the result.

Both formats support an optional ELSE argument.

Transact-SQL Syntax Conventions

Syntax

Simple CASE function:
CASE input_expression
WHEN when_expression THEN result_expression
[ ...n ]
[
ELSE else_result_expression
]
END
Searched CASE function:
CASE
WHEN Boolean_expression THEN result_expression
[ ...n ]
[
ELSE else_result_expression
]
END

Everyday I learn something that somebody else already knew

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-10-20 : 12:59:43

are you trying to create the view query in design view editor? If yes, you cant use CASE constructs inside it. You might be better off creating view using CREATE VIEW statement in query analyser.

http://databases.aspfaq.com/database/which-tool-should-i-use-enterprise-manager-or-query-analyzer.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2010-10-20 : 13:18:03

When you re-write your CASE expression, you willgo from this:

SELECT ..
SUM(CASE measurement_date
WHEN NULL
THEN XX.amp_tot
.. END)

To this:
SELECT ..
SUM(CASE WHEN measurement_date = NULL
THEN XX.amp_tot
.. END)

But "measurement_date = NULL" is always UNKNOWN. Sort of useless, isn't it?

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

rbeach1955
Starting Member

8 Posts

Posted - 2010-10-20 : 13:40:53

Below is two ways the case statement was set up. The first case statements do not work, the second set works. There has to be some way the ">" can be used in a case statement. I have also attempted to use this in New Query Editor but have the same problem as in the View design.

CASE 1:
SELECT CASE [DateMeasured]
WHEN Null
THEN [StandardTotalKVAbyBuildingAndLocation].[SumOfAmps]
ELSE CASE [MaxOfUpdated]
WHEN [MaxOfUpdated]>[DateMeasured])
THEN [StandardTotalKVAbyBuildingAndLocation].[SumOfAmps]
ELSE [MeasuredCircuitInfoTotals].[SumOfAmps]
END
END AS 'TotalAmps'
FROM AuditQueries.StandardTotalKVAbyBuildingAndLocation INNER JOIN
AuditQueries.MeasuredCircuitInfoTotals ON AuditQueries.StandardTotalKVAbyBuildingAndLocation.LocationDesc = AuditQueries.MeasuredCircuitInfoTotals.Location

CASE 2
SELECT CASE [DateMeasured]
WHEN Null
THEN [StandardTotalKVAbyBuildingAndLocation].[SumOfAmps]
ELSE CASE [MaxOfUpdated]
WHEN 1
THEN [StandardTotalKVAbyBuildingAndLocation].[SumOfAmps]
ELSE [MeasuredCircuitInfoTotals].[SumOfAmps]
END
END AS 'TotalAmps'
FROM AuditQueries.StandardTotalKVAbyBuildingAndLocation INNER JOIN
AuditQueries.MeasuredCircuitInfoTotals ON AuditQueries.StandardTotalKVAbyBuildingAndLocation.LocationDesc = AuditQueries.MeasuredCircuitInfoTotals.Location

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-10-20 : 13:41:40

I did a quick fix for you, but it's important under stand the points made by Jim and Joe!

SELECT
    LocationsQueryA.Building
  ,StandardTotalKVAbyBuildingAndLocation.LocationDesc
  ,SUM(
        CASE 
            WHEN [DateMeasured] IS NULL
                THEN [StandardTotalKVAbyBuildingAndLocation].[SumOfAmps]
            ELSE
                CASE 
                    WHEN [MaxOfUpdated] > [DateMeasured]
                        THEN [StandardTotalKVAbyBuildingAndLocation].[SumOfAmps]
                    ELSE [MeasuredCircuitInfoTotals].[SumOfAmps]
                END
        END
        ) AS 'TotalAmps'
  ,SUM(
        CASE 
            WHEN [DateMeasured] IS NULL
                THEN [StandardTotalKVAbyBuildingAndLocation].[SumOfAmps] * [Volts]
            ELSE
                CASE 
                    WHEN [MaxOfUpdated] > [DateMeasured]
                        THEN [StandardTotalKVAbyBuildingAndLocation].[SumOfAmps] * [Volts]
                    ELSE [MeasuredCircuitInfoTotals].[SumOfAmps] * [Volts]
                END
        END) AS TotalWatts
FROM
    (AuditQueries.LocationsQueryA
LEFT JOIN
    AuditQueries.StandardTotalKVAbyBuildingAndLocation
    ON LocationsQueryA.FullLoc = StandardTotalKVAbyBuildingAndLocation.LocationDesc)
LEFT JOIN
    AuditQueries.MeasuredCircuitInfoTotals
    ON StandardTotalKVAbyBuildingAndLocation.LocationDesc = MeasuredCircuitInfoTotals.Location
GROUP BY
    LocationsQueryA.Building
  ,StandardTotalKVAbyBuildingAndLocation.LocationDesc

rbeach1955
Starting Member

8 Posts

Posted - 2010-10-20 : 13:49:36

My apologies but I wrote an additional email before I noticed your reply. That works correctly even in View Design.

Thanks for your assistance.

Rick

quote:
Originally posted by jcelko

When you re-write your CASE expression, you willgo from this:

SELECT ..
SUM(CASE measurement_date
WHEN NULL
THEN XX.amp_tot
.. END)

To this:
SELECT ..
SUM(CASE WHEN measurement_date = NULL
THEN XX.amp_tot
.. END)

But "measurement_date = NULL" is always UNKNOWN. Sort of useless, isn't it?

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

rbeach1955
Starting Member

8 Posts

Posted - 2010-10-20 : 13:57:48

Again I appreciate your assistance. I understand the points made by Jim and Joe, but the views I have created need to be linked to an MSAccess database that I do not have control of. The multiple Views created can be set up for permissions by utilizing a schema setup and I do not know how this would be able to work with a query set up with New Query Design.

Thanks,

Rick

quote:

Originally posted by Lamprey

I did a quick fix for you, but it's important under stand the points made by Jim and Joe!

SELECT
    LocationsQueryA.Building
  ,StandardTotalKVAbyBuildingAndLocation.LocationDesc
  ,SUM(
        CASE 
            WHEN [DateMeasured] IS NULL
                THEN [StandardTotalKVAbyBuildingAndLocation].[SumOfAmps]
            ELSE
                CASE 
                    WHEN [MaxOfUpdated] > [DateMeasured]
                        THEN [StandardTotalKVAbyBuildingAndLocation].[SumOfAmps]
                    ELSE [MeasuredCircuitInfoTotals].[SumOfAmps]
                END
        END
        ) AS 'TotalAmps'
  ,SUM(
        CASE 
            WHEN [DateMeasured] IS NULL
                THEN [StandardTotalKVAbyBuildingAndLocation].[SumOfAmps] * [Volts]
            ELSE
                CASE 
                    WHEN [MaxOfUpdated] > [DateMeasured]
                        THEN [StandardTotalKVAbyBuildingAndLocation].[SumOfAmps] * [Volts]
                    ELSE [MeasuredCircuitInfoTotals].[SumOfAmps] * [Volts]
                END
        END) AS TotalWatts
FROM
    (AuditQueries.LocationsQueryA
LEFT JOIN
    AuditQueries.StandardTotalKVAbyBuildingAndLocation
    ON LocationsQueryA.FullLoc = StandardTotalKVAbyBuildingAndLocation.LocationDesc)
LEFT JOIN
    AuditQueries.MeasuredCircuitInfoTotals
    ON StandardTotalKVAbyBuildingAndLocation.LocationDesc = MeasuredCircuitInfoTotals.Location
GROUP BY
    LocationsQueryA.Building
  ,StandardTotalKVAbyBuildingAndLocation.LocationDesc

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources