Syntax error near else expression

• river1

  SSC-Insane

  Points: 24309

  More actions

  October 13, 2017 at 2:52 am

  #340752

  Hello,

  Can you please help me understand what am I doing wrong in below code?

  Thank you

  SELECT

  COLUMN_NAME,

  
  

  DATA_TYPE,

  
  

  DATA_TYPE2 =

  ( case

  
  

  when (DATA_TYPE = 'varchar') then '(' + convert(varchar,(CHARACTER_MAXIMUM_LENGTH)) + ' )'

  
  

  when (DATA_TYPE = 'decimal') then '(' + convert(varchar,(NUMERIC_PRECISION + NUMERIC_SCALE)) + ')'

  
  

  ^ else null

  
  

  end ),

  CHARACTER_MAXIMUM_LENGTH,

  
  

  NUMERIC_PRECISION,

  
  

  NUMERIC_SCALE ,

  
  

  IS_NULLABLE

  FROM

  
  

  INFORMATION_SCHEMA.COLUMNS

  
  

  WHERE

  
  

  TABLE_SCHEMA='genericdata' and TABLE_CATALOG = 'dwhStaging'

  
  

  order by

• river1

  SSC-Insane

  Points: 24309

  More actions

  October 13, 2017 at 2:54 am

  #1963518

  Sorry I just found. Its a simbol. thanks

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  October 13, 2017 at 9:08 am

  #1963574

  river1 - Friday, October 13, 2017 2:52 AM

  Hello,

  Can you please help me understand what am I doing wrong in below code?

  Thank you

  • You need to format your code to make it easier to read. That will help you and others to find errors.
  • You shouldn't add precision and scale. The length of the number is the precision, the scale is the number of positions to the right of the decimal point and it's included in the precision (and shouldn't be greater than it).
  • You should always specify length for strings.
  • You don't need an  ELSE NULL, that's the default behavior.
  • You're missing the columns in the ORDER BY.
  • You're filtering by database on a view that returns values for just the current database.

  Hopefully, you'll take these observations as ways to improve and not as mere criticism.

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2