Data types conversion in SQL and Conditional format

• nmxobo

  Valued Member

  Points: 60

  More actions

  July 18, 2008 at 12:56 am

  #188298

  Hi

  I would like you to help me to come up with desired results for this report.I am currently preparing a script in SQL for people who has attended course now what i want the report to do is to highlight the entire row green if the column STATUS contains the value ' NOT ATTENDED',Im thinking of a coding like this

  select case when status <> "Attended' then

  backcolor = green

  else status

  end case

  the second one is this field called Cost_per_person is captured in string data type and other values are having 'R' before(R4562,00) and the comma is not used to separate thousand is for hundrends instead.Now i want to convert all the numbers to decimal and leave those rows that contains characters('NOT YET PAID').I want to have a code like this one

  select CASE WHEN f.segment10 = string then

  REPLACE (CAST (f.segment10 AS VARCHAR (10)), 'R', ' ' )

  when f.segment10 = varchar then

  convert(To_decimal(8,2),f.segment10 )

  ELSE f.segment10

  END cost_per_person,

  your response will be highly appreciated.

  Regards

  Nosizwe Mxobo

  Marang Oracle Centre of excellence : HRMS Business Intelligence

  Tel: 27 (21) 403 8075

  Fax: 27 (21) 403 3657

  Cell: 082 064 4691

  http://www.parliament.gov.za

• PaulB-TheOneAndOnly

  SSC Guru

  Points: 66772

  More actions

  August 9, 2008 at 11:46 am

  #855704

  :w00t: May be is because of the bottle of Brandy but believe me I couldn't figure it out what are you looking for... I'll check back when I back to my senses

  _____________________________________
  Pablo (Paul) Berzukov

  Author of Understanding Database Administration available at Amazon and other bookstores.

  Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.