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

• Carl Federl

  One Orange Chip

  Points: 25384

  More actions

  November 11, 2010 at 5:45 am

  #1249416

  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

  November 13, 2010 at 3:20 pm

  #1250388

  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

  November 13, 2010 at 4:41 pm

  #1250390

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

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