case command sql 2005

Hi fellows

I am running a command in sql, but I have an error message. The field that i am using has real values.

could someone help me with this isue?

CASE AQUILA.dbo.BI.BI WHEN AQUILA.dbo.BI.BI = 0 THEN 5 ELSE (AQUILA.dbo.BI.BI*25/100) END AS ROCK_FACTOR

cheers

Edwin

hi Edwin,

SET NOCOUNT ON; USE tempdb; GO CREATE TABLE dbo.TestTB ( Id int NOT NULL, BI real DEFAULT 0 ); GO INSERT INTO dbo.TestTB VALUES ( 1 , 0 ); INSERT INTO dbo.TestTB VALUES ( 2 , 5 ); INSERT INTO dbo.TestTB VALUES ( 3 , 10 ); GO SELECT Id, BI, CASE BI WHEN 0 THEN 5 ELSE (BI*25/100) END AS ROCK_FACTOR FROM dbo.TestTB; GO DROP TABLE dbo.TestTB; --<- Id BI ROCK_FACTOR -- - - 1 0 5 2 5 1,25 3 10 2,5

regards

|||

Hi Andrea

I have another problem. I have a table with consecutives values , but they are desorganized for example

DHID from to

45 50 40

45 40 30

45 0 10

45 10 10

45 20 30

I wan to organize them like this

DHID from to

45 0 10

45 10 20

45 20 30

45 30 40

45 40 50

That means to start with the minimum value and consecutivite values

Do you think that this is possible.

cheers

Edwin

|||

hi Edwin,

your data really is a mess ..

you've better take care of it as it will crash you, now or then..

anyway, you can write something similar to

SET NOCOUNT ON; USE tempdb; GO CREATE TABLE dbo.TestTB ( DHID int NOT NULL, [From] int NOT NULL, [To] int NOT NULL ); INSERT INTO dbo.TestTB VALUES ( 45, 50, 40 ); INSERT INTO dbo.TestTB VALUES ( 45, 40, 30 ); INSERT INTO dbo.TestTB VALUES ( 45, 0, 10 ); INSERT INTO dbo.TestTB VALUES ( 45, 10, 10 ); INSERT INTO dbo.TestTB VALUES ( 45, 20, 30 ); GO SELECT DHID , CASE WHEN [From] < [To] THEN [From] ELSE [To] END AS [From] , CASE WHEN [To] > [From] THEN [To] ELSE [From] END AS [To] FROM dbo.TestTB ORDER BY DHID, [From], [To] GO DROP TABLE dbo.TestTB; --<-- DHID From To -- -- -- 45 0 10 45 10 10 45 20 30 45 30 40 45 40 50

but you can not fill, this way, eventual gaps (like the missing 10-20 row) or remove unchanged states, like the 2nd row of the resultset, defined as

DHID from to

45 10 10

if you have this requirement you probably have to work with temporary tables to be populated and purged by bad/redundant data, with additional logic to fill "gaps"..

regards

|||

YOU ARE AWESOME

THANK YOU VERY MUCH