Error in Case construct - SQL Server Forums

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

Error in Case construct

Author

Topic

Flaterik
Starting Member

26 Posts

Posted - 2011-07-20 : 04:38:42

Hi people i have the query in quote

quote:
CASE WHEN CAST(ADB_TREND.dbo.vw_daniele_materiali_mese_per_mese_anno_prec.Expr1 AS varchar(100)) IS NULL THEN
CASE WHEN ADB_TREND.dbo.vw_daniele_materiali_tot_anno_in_corso.TotMerce IS NULL THEN 'ALPHA'
ELSE 'BETA'
END

ELSE
CASE ADB_TREND.dbo.vw_daniele_materiali_mese_per_mese_anno_prec.Expr1 WHEN 0 THEN NULL
ELSE ((
(CASE WHEN ADB_TREND.dbo.vw_daniele_materiali_tot_anno_in_corso.TotMerce IS NULL
THEN 0 ELSE ADB_TREND.dbo.vw_daniele_materiali_tot_anno_in_corso.TotMerce END)- ADB_TREND.dbo.vw_daniele_materiali_mese_per_mese_anno_prec.Expr1)
/ ADB_TREND.dbo.vw_daniele_materiali_mese_per_mese_anno_prec.Expr1 * 100)
END
END

AS variazione

Messaggio 8114, livello 16, stato 5, riga 11
Errore durante la conversione del tipo di dati da varchar a numeric.

This is ther error message that i have.
I tell you the situation. I have a Case statement that have as output a number but in some case this must be a Text String.
How to solve this case?

Thank you

the world is strange but people are crazy

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-07-20 : 04:49:04

[code]CASE
WHEN ADB_TREND.dbo.vw_daniele_materiali_mese_per_mese_anno_prec.Expr1 IS NULL AND ADB_TREND.dbo.vw_daniele_materiali_tot_anno_in_corso.TotMerce IS NULL THEN 'ALPHA'
WHEN ADB_TREND.dbo.vw_daniele_materiali_mese_per_mese_anno_prec.Expr1 IS NULL THEN 'BETA'
WHEN ADB_TREND.dbo.vw_daniele_materiali_mese_per_mese_anno_prec.Expr1 = 0 THEN NULL
ELSE LTRIM(STR( (
ISNULL(ADB_TREND.dbo.vw_daniele_materiali_tot_anno_in_corso.TotMerce, 0) - ADB_TREND.dbo.vw_daniele_materiali_mese_per_mese_anno_prec.Expr1
) * 100E / ADB_TREND.dbo.vw_daniele_materiali_mese_per_mese_anno_prec.Expr1
, 15, 4))
END AS Variazione[/code]

N 56°04'39.26"
E 12°55'05.63"

Flaterik
Starting Member

26 Posts

Posted - 2011-07-20 : 05:14:36

You are the biggest ONE

Thank you very much

the world is strange but people are crazy

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-07-20 : 05:43:33

[code]CASE
WHEN ADB_TREND.dbo.vw_daniele_materiali_mese_per_mese_anno_prec.Expr1 IS NULL AND ADB_TREND.dbo.vw_daniele_materiali_tot_anno_in_corso.TotMerce IS NULL THEN 'ALPHA'
WHEN ADB_TREND.dbo.vw_daniele_materiali_mese_per_mese_anno_prec.Expr1 IS NULL THEN 'BETA'
ELSE LTRIM(STR(100E * (ISNULL(ADB_TREND.dbo.vw_daniele_materiali_tot_anno_in_corso.TotMerce, 0) - ISNULL(ADB_TREND.dbo.vw_daniele_materiali_mese_per_mese_anno_prec.Expr1, 0)) / NULLIF(ADB_TREND.dbo.vw_daniele_materiali_mese_per_mese_anno_prec.Expr1, 0), 15, 4))
END AS Variazione[/code]

N 56°04'39.26"
E 12°55'05.63"

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2011-07-20 : 12:18:36

quote:
Originally posted by Flaterik

You are the biggest ONE

Thank you very much

the world is strange but people are crazy

He's calling you fat. You going to let that slide?

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-07-20 : 15:25:07

Yes. In my language, FlatErik can be translated as Erik the smalldicked.

N 56°04'39.26"
E 12°55'05.63"

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2011-07-20 : 16:33:54

quote:
Originally posted by SwePeso

Yes. In my language, FlatErik can be translated as Erik the smalldicked.

N 56°04'39.26"
E 12°55'05.63"

Peso : I don't think I've ever laughed so much at a post before

+++

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-07-20 : 20:21:49

CASE is an expression. NOT a statement. It returns one and only one result of one and only one data type.

--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

Flaterik
Starting Member

26 Posts

Posted - 2011-07-21 : 03:28:49

Oh jcelko , thank you for your explanation. I abuse of "statement" terms

SwePeso i hope you understood what i meant.
(I don't know if you are fat

)

Transact Charlie: My English is a circus, i'm glad you enjoy

So my name is Massimiliano, glad to know nice people like you!

But the problem is not finished

Where the result of the case is a number, because of the string type, i can't order it from the bigger ? Ther's a solution?

Bye

Massimiliano

************************************************
the world is strange but people are crazy

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2011-07-21 : 04:46:41

Can you give us some sample data? I don't really know what you are ordering.

Charlie.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-07-21 : 05:20:59

The solution is to convert into varchar, as shown in my example above.
The result is varchar only, since the compuation/expression/statement is a single column value.
And all values in a column must have same datatype.

N 56°04'39.26"
E 12°55'05.63"

Flaterik
Starting Member

26 Posts

Posted - 2011-07-21 : 05:37:03

So my data are like:

- 1
- 5
12
35

I use your expression to have this result . I suppose that, because of the varchar data, if i order i have an alphabetical order.

************************************************
the world is strange but people are crazy

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources