Case Statement in Stored Procedure ?

I am converting IIF statements from an Access Query into a Stored Procedure
and was doing ok until I got to this one which is a bit more complicated:
CASE WHEN [Field1] = 1 THEN 0 ELSE [Field2] + [Field3] -[Field4] / [Field5]
END
All fields are valid boleen fields
I have tried putting parens around the equation part but still get errors.
How can I get the data into this field? Thanks.CAST as the bit columns as INT such as CAST([Field2] AS INT)
I think that [Field2] + [Field3] -[Field4] / [Field5]
should be ([Field2] + [Field3] -[Field4]) / [Field5]
"AkAlan" <AkAlan@.discussions.microsoft.com> wrote in message
news:F23EB696-E2B4-43EA-AC88-664FB8B2221E@.microsoft.com...
>I am converting IIF statements from an Access Query into a Stored
>Procedure
> and was doing ok until I got to this one which is a bit more complicated:
> CASE WHEN [Field1] = 1 THEN 0 ELSE [Field2] + [Field3] -[Field4] /
> [Field5]
> END
> All fields are valid boleen fields
> I have tried putting parens around the equation part but still get errors.
> How can I get the data into this field? Thanks.
>|||Please explain what "All fields are valid boleen fields" means.
Also post the error message that you get.
ML|||The Fields I'm trying to perform math on are boolean fields and are already
in place in the stored procedure.
The error says:
ADO Error:Invalid operator for data type. Operator equals add, type equals
bit.
Thanks for helping.
"ML" wrote:

> Please explain what "All fields are valid boleen fields" means.
> Also post the error message that you get.
>
> ML|||>> All fields are valid Boolean fields <<
UNH' Let's get back to the basics of an RDBMS. Rows are not records;
fields are not columns; tables are not files.
Where is the DDL? SQL has no Boolean data types and good programemrs
do not use the proprietary BIT data type.
There is no CASE statement in SQL, either; there is a CASE expression.
So what "field" are you trying to assign this exprsssion to?|||First of all, bit is not boolean, and second - the error message is pretty
much self-explanatory. Why are you adding, subtracting and dividing values
that can either be 1 or 0?
E.g.:
what does this mean to you:
1 + 0 - 1 / 1
or:
1 + 1 - 0 / 1
or worse:
1 + 1 -1 / 0
Please describe what you're trying to achieve. There must be some reason...?
ML|||
"ML" wrote:

> First of all, bit is not boolean, and second - the error message is pretty
> much self-explanatory. Why are you adding, subtracting and dividing values
> that can either be 1 or 0?
> E.g.:
> what does this mean to you:
> 1 + 0 - 1 / 1...this means 0%
> 1 + 0 / 1 + 1 ...this would be 50%
> 1 + 1 / 1 + 1 ...100%
> or:
> 1 + 1 - 0 / 1
> or worse:
> 1 + 1 -1 / 0...checked for and not allowed through business rules
> Please describe what you're trying to achieve. There must be some reason..
.?
>
> ML
I needed to be able to do math on the fields like I can in MS
Access...Changing the bits to integers worked.|||> Where is the DDL? SQL has no Boolean data types and good programemrs
> do not use the proprietary BIT data type.
Good programmers make proper use of approriate dtaa types in any given
situation.
Similary, good programmers don't just follow along with the comment "all
GOTO statements or CURSORs are bad", but take the time to understand why
other programmers prefer to use better alternatives. Cursors can also be
very powerful, but only when used in an appropriate situation where the same
result cannot be equally-well achieved via set-based operations.
If you were defining a table ApplicationUsers, and needed to store (in a
SQL2000 database) a field indicating whether the user was enabled, ( a field
that could only ever have a value of Yes/No), what field would YOU use ...

> There is no CASE statement in SQL, either; there is a CASE expression.
> So what "field" are you trying to assign this exprsssion to?
Whilst I agree with you that the snippet is technically an expression, not a
statement, this value doesn't necessarily need to be assigned to a field.|||I guess if I were an SQL expert I wouldn't need to use this web site and
expose my ignorance of SQL terminology to arrogant know-it-alls who find it
easier to berate my lack of knowledge than just simply give me the answer I
was looking for like Raymond D'Anjou so kindly did. I would appreciate you
not anwering any of my posts in the future.
"--CELKO--" wrote:

> UNH' Let's get back to the basics of an RDBMS. Rows are not records;
> fields are not columns; tables are not files.
> Where is the DDL? SQL has no Boolean data types and good programemrs
> do not use the proprietary BIT data type.
> There is no CASE statement in SQL, either; there is a CASE expression.
> So what "field" are you trying to assign this exprsssion to?
>|||AkAlan,
Don't let Celko intimidate you, and as for him being a SQL expert, well,
perhaps an ANSI 92 expert and thats about as far as it goes.
Tony.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"AkAlan" <AkAlan@.discussions.microsoft.com> wrote in message
news:0A58D8F7-6E39-4072-9634-2082745BA421@.microsoft.com...
>I guess if I were an SQL expert I wouldn't need to use this web site and
> expose my ignorance of SQL terminology to arrogant know-it-alls who find
> it
> easier to berate my lack of knowledge than just simply give me the answer
> I
> was looking for like Raymond D'Anjou so kindly did. I would appreciate you
> not anwering any of my posts in the future.
> "--CELKO--" wrote:
>