Converts in a case expression

Question

Post reply

Converts in a case expression

hannas46

SSC Rookie

Points: 27
More actions
November 11, 2010 at 5:07 am

#228960

Hi!
I want to mix a varchar and a int value in the same output column like:
id, name, "Number of customers"
1, name1, 15
2, name2, 35
3, name3, No customers
My code is
SELECT id, name, case
WHEN COUNT(customer_id) = 0 THEN 'No customers'
ELSE COUNT(customer_id)
/*
i tried
COUNT(CONVERT(varchar(20), customer_id))
COUNT(CONVERT(int,customer_id))
COUNT(CONVERT(varchar(20),CONVERT(int, customer_id)
*/
END AS 'Number of customers'
FROM ....
GROUP BY .....
-- error 'conversion failed when converting the varchar value 'No customers' to data type int
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

Carl Federl One Orange Chip Points: 25384 More actions · Answer 1

You should cast the numeric to a character datatype within the case statement such as:

WithSchemataTables as

(

selectSCHEMATA.schema_name

,SUM( CASE when TABLES.TABLE_SCHEMA IS NOT NULL THEN 1 ELSE 0 END ) as SchemaTableCnt

fromINFORMATION_SCHEMA.SCHEMATA

left outer join

INFORMATION_SCHEMA.TABLES

onTABLES.TABLE_SCHEMA= SCHEMATA.schema_name

group by SCHEMATA.schema_name

)

selectSchemataTables.schema_name

,CASE WHEN SchemaTableCnt = 0 then CAST( 'No Tables' as varchar(20))

ELSECAST( SchemaTableCnt as varchar(20))

END

FROMSchemataTables

SQL = Scarcely Qualifies as a Language

hannas46 SSC Rookie Points: 27 More actions · Answer 2

Hi!

I have tried to convert numeric to a character datatype in my code, but it ain´t work 🙁

Is it possible to show the converts in my code?

WHEN COUNT(customer_id) = 0 THEN COUNT(CONVERT(varchar(20),'No customers'))

ELSE COUNT(CONVERT(varchar(20),customer_id))

Thanks 🙂

mister.magoo SSC-Forever Points: 47174 More actions · Answer 3

You sure you should be writing SQL? You don't want to count the varchars, but varchar the count...

WHEN COUNT(customer_id) = 0 THEN 'No customers'

ELSE CONVERT(varchar(20),COUNT(customer_id))

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

Sachin Nandanwar SSCertifiable Points: 7699 More actions · Answer 4

OR

Try this

WHEN COUNT(customer_id) = convert(varchar(2),0) THEN 'No customers' ELSE COUNT(customer_id) END

PS - I haven't tested it

--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server

hannas46 SSC Rookie Points: 27 More actions · Answer 5

Thanks MM!

It works perfekt , but MM, i' m just a beginner writing sql-code

😉

\\ H F

mister.magoo SSC-Forever Points: 47174 More actions · Answer 6

hannas46 (11/14/2010)
Thanks MM!
It works perfekt , but MM, i' m just a beginner writing sql-code
😉
\\ H F

I understand - and I am a beginner at graciousness - forgive my comment 😛

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);