case SQL construct not supported? 1

[mymou]

Hi All

This must be easy and I'm really stupid.

Want to create a view - but perform a few calculations on the data.

The first problem is the potential of dividing by zero (generating the error "Divide by zero error&quot. This is to be expected, so i'm trying to write a case statement to avoid the problem but get the error "The Query Designer does not support case SQL construct".

I must be doing something stupid. Please help.


Stew "Even a stopped clock tells the right time twice a day."

 

[jjonesal]

SQL Server does support a CASE construct. Could you provide a little more info about what you're trying to do & I'll try to help you come up with some SQL that will work... J. Jones
jjones@cybrtyme.com

 

[mymou]

Hi JJonesal

I was sure that SQL Server did - so i was a bit confused by the error message. I had hopped that something had been missed in the install - or something just as stupid.

All I want to do is calculate a value. The actual is a little messy (nested stuff etc) - so I'll just give details of relevant SQL.

SELECT
100* (Target - Close_Price) / Close_Price as [P/Target]
FROM
etc

The problem is that Close_Price might be 0. So I hope I can use something like:

SELECT
case close_price when 0 then 0 else 100 * (Target - Close_Price) / Close_Price end as [P/Target]
FROM
ETC

I have tried exactly this - and get error "The Query Designer does not support case SQL construct".

Any ideas?

Stew
"Even a stopped clock tells the right time twice a day."

 

[fluteplr]

SQL server supports case, the designer does not you will need to create your view in code. What I usually do is create as much of the view as posible in the designer all the joins etc. Copy the code out of the designer paste it into query analyser add the case statments and create the view from there.

 

[mymou]

Thanks fluteplr

Knew it would be easy.

Stew "Even a stopped clock tells the right time twice a day."