OraFAQ Forum: Server Utilities » Sql Script Help !!!!

Home » RDBMS Server » Server Utilities » Sql Script Help !!!!

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

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!

Report message to a moderator

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.

Report message to a moderator

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

Report message to a moderator

Previous Topic:	error
Next Topic:	ORA-01475 error

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue May 14 19:23:45 CDT 2024