Re: Single function problem

The first part is (obviously) a dummy select to generate data. The case statement is the point of interest.

with book_list as (

select 'FIRST BOOK' as title, 'COMPUTER' as book_type, 40 as price

from dual

union

select 'SECOND BOOK', 'FITNESS', 100 from dual

union

select 'THIRD BOOK', 'COOKING', 50

from dual

)

select title, book_type,

case when book_type='COMPUTER' THEN price*1.1

when book_type = 'FITNESS' THEN price*1.07

when book_type = 'COOKING' then price*1.25

else price

end as retail_price

from book_list

will create the following result set:

TITLE BOOK_TYPE RETAIL_PRICE

• --------- ------------

FIRST BOOK COMPUTER 44 SECOND BOOK FITNESS 107 THIRD BOOK COOKING 62.5 "Rosh157" <kouroshsaei_at_gmail.com> wrote in message news:1195021763.932562.94370_at_q5g2000prf.googlegroups.com...
> Hello:
> i'm kind of new in Oracle and now learning how to use the Sql*Plus. i
> have question about
> Single functions and i hope that somebody can help me.
> my question is:
> I using Sql*Plus on oracle 10g, and I want to use the "Single row
> function" to create a list of books, I have to increase a price of
> each books based on books category. For example for computer books
> 10%, for fitness books 7%, and for cooking books 25%. And price should
> be display with 3 decimal places. (we have a price, title, category,
> and retail price column).what function should be use and if I have to
> use the nesting function how can I apply that.
> Thanks
>
Received on Wed Nov 14 2007 - 06:17:25 CST