this case statement:
This was derived from and access IIF statement
Access IIF statement:
IIf([meterreading] & ""="" Or [previousreading] & ""="",Null,
(IIf([MeterReading]>=[PreviousReading],[MeterReading]- [previousreading],[MeterReading]+([Rollo
ver]- [PreviousReading]))*IIf(IsNull([MultiFac
tor]),1,[MultiFactor]))+nz([UsageAdjustm
ent],0))
SQL Case statement:
select
case when 10000.0 is null or 1.0 is null
then null else case when 10000.0 >= 1.0 then (10000.0 - 1.0)
else 10000.0 + (4 - 1.0) end end * case when 22.0 is null
then 1 else 22.0 + isnull(4.0,0) end as CurUsage
Above is a sample of the case statement with the actual values instead of
the field names used in the actual statement so you may test. The result I
require and the result access renders using the exact values is 219982, but
the result I get from SQL is 259974. I believe that my case statement is
formatted incorrectly. If I simply compute all the value that would be
returned base on the case statement conditions I get the value I s
.select (10000.0 - 1.0) * 22.0 + isnull(4.0,0) = 219982
Any Ideas?
Best Regards,
Advice on Case StatementThe problem is that it will evaluate inside out. So the 22 + 4 is evaluated
prior to the multiplication.
I think just you just need to move your last END statement.
As in the following:
select
case when 10000.0 is null or 1.0 is null
then null else case when 10000.0 >= 1.0 then (10000.0 - 1.0)
else 10000.0 + (4 - 1.0) end end * case when 22.0 is null
then 1 else 22.0 end + isnull(4.0,0) as CurUsage
Ryan Powers
Clarity Consulting
http://www.claritycon.com
"Tim Harvey" wrote:
> Can someone guide me in the correct direction as to what's going on with
> this case statement:
> This was derived from and access IIF statement
> Access IIF statement:
> IIf([meterreading] & ""="" Or [previousreading] & ""="",Null,
> (IIf([MeterReading]>=[PreviousReading],[MeterReading]- [previousreading],[MeterReading]+([Rollo
ver]- [PreviousReading]))*IIf(IsNull([MultiFac
tor]),1,[MultiFactor]))+nz([UsageAdjustm
ent],0))
>
> SQL Case statement:
> select
> case when 10000.0 is null or 1.0 is null
> then null else case when 10000.0 >= 1.0 then (10000.0 - 1.0)
> else 10000.0 + (4 - 1.0) end end * case when 22.0 is null
> then 1 else 22.0 + isnull(4.0,0) end as CurUsage
> Above is a sample of the case statement with the actual values instead of
> the field names used in the actual statement so you may test. The result I
> require and the result access renders using the exact values is 219982, bu
t
> the result I get from SQL is 259974. I believe that my case statement is
> formatted incorrectly. If I simply compute all the value that would be
> returned base on the case statement conditions I get the value I s
.> select (10000.0 - 1.0) * 22.0 + isnull(4.0,0) = 219982
>
> Any Ideas?
>
> Best Regards,
> Advice on Case Statement
>
>|||1) First thing to learn is that ther is no CASE statement in SQL; there
is a CASE **expression**!! Important!! Expressions have a single data
type. Expressions have nothign to do with control flow; since SQL is a
declarative language, there is no concept whatsoever of control flow!!
This is basic programming concepts.
2) "CASE WHEN 10000.0 IS NULL OR .. " is absurd code; it is always
UNKNOWN. Do you understand the 3-Valued Logic in SQL?
3) Above is a sample of the case statement [sic] with the actual values
instead of the field [sic] names used in the actual statement so you may
test. <<
Columns are not anything like fields; please learn the foundations,
concepts and the right words. Now, how do you expect us to debug code
you will not show us? How do we tell the constants from the columns?
You failed to post even minimal DDL or a spec that makes sense. Try
again, if you really want help.
The CASE expression is an *expression* and not a control statement; that
is, it returns a value of one datatype. SQL-92 stole the idea and the
syntax from the ADA programming language. Here is the BNF for a <case
specification>:
<case specification> ::= <simple case> | <searched case>
<simple case> ::=
CASE <case operand>
<simple when clause>...
[<else clause>]
END
<searched case> ::=
CASE
<searched when clause>...
[<else clause>]
END
<simple when clause> ::= WHEN <when operand> THEN <result>
<searched when clause> ::= WHEN <search condition> THEN <result>
<else clause> ::= ELSE <result>
<case operand> ::= <value expression>
<when operand> ::= <value expression>
<result> ::= <result expression> | NULL
<result expression> ::= <value expression>
The searched CASE expression is probably the most used version of the
expression. The WHEN ... THEN ... clauses are executed in left to right
order. The first WHEN clause that tests TRUE returns the value given in
its THEN clause. And, yes, you can nest CASE expressions inside each
other. If no explicit ELSE clause is given for the CASE expression,
then the database will insert a default ELSE NULL clause. If you want
to return a NULL in a THEN clause, then you must use a CAST (NULL AS
<datatype> ) expression. I recommend always giving the ELSE clause, so
that you can change it later when you find something explicit to return.
The <simple case expression> is defined as a searched CASE expression in
which all the WHEN clauses are made into equality comparisons against
the <case operand>. For example
CASE iso_sex_code
WHEN 0 THEN 'Unknown'
WHEN 1 THEN 'Male'
WHEN 2 THEN 'Female'
WHEN 9 THEN 'N/A'
ELSE NULL END
could also be written as:
CASE
WHEN iso_sex_code = 0 THEN 'Unknown'
WHEN iso_sex_code = 1 THEN 'Male'
WHEN iso_sex_code = 2 THEN 'Female'
WHEN iso_sex_code = 9 THEN 'N/A'
ELSE NULL END
There is a gimmick in this definition, however. The expression
CASE foo
WHEN 1 THEN 'bar'
WHEN NULL THEN 'no bar'
END
becomes
CASE WHEN foo = 1 THEN 'bar'
WHEN foo = NULL THEN 'no_bar' -- error!
ELSE NULL END
The second WHEN clause is always UNKNOWN.
The SQL-92 Standard defines other functions in terms of the CASE
expression, which makes the language a bit more compact and easier to
implement. For example, the COALESCE () function can be defined for one
or two expressions by
1) COALESCE (<value exp #1> ) is equivalent to (<value exp #1> )
2) COALESCE (<value exp #1>, <value exp #2> ) is equivalent to
CASE WHEN <value exp #1> IS NOT NULL
THEN <value exp #1>
ELSE <value exp #2> END
then we can recursively define it for (n) expressions, where (n >= 3),
in the list by
COALESCE (<value exp #1>, <value exp #2>, . . ., n), as equivalent to:
CASE WHEN <value exp #1> IS NOT NULL
THEN <value exp #1>
ELSE COALESCE (<value exp #2>, . . ., n)
END
Likewise, NULLIF (<value exp #1>, <value exp #2> ) is equivalent to:
CASE WHEN <value exp #1> = <value exp #2>
THEN NULL
ELSE <value exp #1> END
It is important to be sure that you have a THEN or ELSE clause with a
datatype that the compiler can find to determine the highest datatype
for the expression.
A trick in the WHERE clause is use it for a complex predicate with
material implications.
WHERE CASE
WHEN <search condition #1>
THEN 1
WHEN <search condition #2>
THEN 1
..
ELSE 0 END = 1
Gert-Jan Strik posted some exampels of how ISNULL() and COALESCE() on
2004 Aug 19
CREATE TABLE #t(a CHAR(1));
INSERT INTO #t VALUES (NULL);
SELECT ISNULL(a,'abc') FROM #t;
SELECT COALESCE(a, 'abc') FROM #t;
DROP TABLE #t;
He always use COALESCE, with the exception of the following type of
situation, because of its performance consequences:
SELECT ...,
ISNULL((SELECT COUNT(*) -- or other aggregate
FROM B
WHERE B.key = A.key), 0)
FROM A;
Likewise, Alejandro Mesa cam up with this example:
SELECT 13 / COALESCE(CAST(NULL AS INTEGER), 2.00); -- promote to highest
type (decimal)
SELECT 13 / ISNULL(CAST(NULL AS INTEGER), 2.00); -- promote to first
type (integer)
--CELKO--
Please post DDL in a human-readable format and not a machine-generated
one. This way people do not have to guess what the keys, constraints,
DRI, datatypes, etc. in your schema are. Sample data is also a good
idea, along with clear specifications.
*** Sent via Developersdex http://www.examnotes.net ***
