If ELSE in a Select Statement

• jayoub

  SSCertifiable

  Points: 6991

  More actions

  May 9, 2016 at 7:10 pm

  #310474

  I am trying to return several columns from the sys.database_files view and want to calculate the growth amount of the file. If there is a view or something that has this value please let me know. If there is no view I need to calculate based on that values in the sys.database_files view.

  I am trying to return the amount that a database file will autogrow in a database. If it is percent I need to return the percentage value. If its absolute I want to return the amount in MB.

  So the is_percent_growth has two values 1 = percent and 0 = absolute value

  The growth field will give you the percent amount or it will give you the absolute value (which I can divide by 128) and get the MB

  The logic would be

  If is_percent_growth = 1 then I want to return the value in growth

  If is_percent_growth = 0 then I need to return the value of growth divided by 128

  I initially thought I could use case, but I read that it will not do any calculations

  I am thinking to use something like this, but its not working

  Select

  Name

  , physical_name

  ,(IF is_percent_growth = 0 then

  select growth/128 from sys.database_files where is_percent_growth = 0

  else

  select growth from sys.database_files where is_percent_growth = 1

  end as [amount growth]

  ,getdate()

  From sys.database_files

  I cannot get it to work.

  Any help is appreciated.

  Jeff

• Sergiy

  SSC Guru

  Points: 110209

  More actions

  May 9, 2016 at 7:58 pm

  #1877649

  select

  ...

  ,growth/CASE is_percent_growth WHEN 0 then 128 ELSE 1 END as [amount growth]

  ...

  FROM ...

  _____________
  Code for TallyGenerator

• jayoub

  SSCertifiable

  Points: 6991

  More actions

  May 11, 2016 at 6:13 pm

  #1878417

  Thank you very much It was perfect.

  Jeff