July 17, 2008 at 8:26 am
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
July 21, 2008 at 12:06 am
With Query 1,Try this;
select (case when status <> "Attended' then
'green'
else status
end ) as BACK_STATUS
You cannot assign a value in case statement.
With second query, is column 'f.segment10' a varchar column?
if yes, than your query should be like this...
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
But I dont think so... I think I need some more explanation against your second query. what are you trying to check in
WHEN f.segment10 = string then
And
when f.segment10 = 'varchar' then
Also, please posty some test data.
Atif Sheikh
July 21, 2008 at 12:43 am
Hi
Thanks for the response.I think I was not clear enough with my statements.With the first query I want the row containing the value that is not ATTENDED to be painted in green colour however this coding replace those fields not ATTENDED with name 'green'.
Case when bst.NAME <> 'Attended' then
'green'
else bst.NAME
end as BACK_STATUS
I have never seen this before me either but i want to know if is this possible with SQL.
The second one.What i am trying to do is, I have this field called ACTUAL COST which is suppose to be stored in numbers but according to the system is stored as text.And other values are having 'R' like (R5566,00) at the beginning that indicate the currency while other values are just numbers like (455,65) and commas are not used to separate thousands but cents instead however there is also text in this field like ('Not yet paid').Now what i want to do is to convert all those values that are numbers into numeric and make the text ones as null or delete them.Please help me with my coding coz im still new in the SQL world.This is the kind of coding i have in mind and u can completely change it if there is a need.
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,
Plz focus on the explanation coz it might happen my coding is not in line with explanation.Your response will be highly appreciated.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply