Sql Script Help !!!! [message #71849] |
Tue, 21 January 2003 14:57 |
Arpit
Messages: 99 Registered: March 2001
|
Member |
|
|
Hello Everyone,
I'm doing a select statement and need to assign the output based on the selection criteria in a seperate column with a special value e.g. exclamation mark or asterisk.
E.g
select week_total_hours
from emp
where week_total_hours>60
If the week_total_hours is greater than 60 then i need to assign an asterisk in a new column[[that does not exist in the table]]
If the week_total_hours is greater that 90 then i need to assign an exclamation mark in that new column.
Is ther any other way to do this. All I'm looking for is to flag the records that have week_total_hours > 60 or 90 in a seperate column so that they are highly visible.
Your help on this ASAP would be highly appreciated.
Thanks!
|
|
|
Re: Sql Script Help !!!! [message #71850 is a reply to message #71849] |
Tue, 21 January 2003 18:35 |
Todd Barry
Messages: 4819 Registered: August 2001
|
Senior Member |
|
|
sql>select week_total_hours, case
2 when week_total_hours between 61 and 90 then '*'
3 when week_total_hours > 90 then '!'
4 else null
5 end marker
6 from e
7 where week_total_hours > 60
8 order by week_total_hours;
WEEK_TOTAL_HOURS M
---------------- -
61 *
89 *
90 *
91 !
4 rows selected.
|
|
|
Re: Sql Script Help !!!! [message #71863 is a reply to message #71849] |
Wed, 22 January 2003 14:23 |
Arpit
Messages: 99 Registered: March 2001
|
Member |
|
|
Hello Todd,
Thanks very much for you help on this. I appreciate it.
I tried to use the same select statement but am getting an error. Can you please let me know where would i be going wrong.
select week_total_hours,case
when week_total_hours between 10 and 30 then '*'
when week_total_hours > 50 then '!'
else null
end marker
from timesheet
where week_total_hours>10
order by week_total_hours;
This is the error message:-
SQL> @a
when week_total_hours between 10 and 30 then '*'
*
ERROR at line 2:
ORA-00923: FROM keyword not found where expected
when week_total_hours between 10 and 30 then '*'
*
ERROR at line 2:
ORA-00923: FROM keyword not found where expected
|
|
|