 Determining Shipping cost [message #116099] |
Sun, 17 April 2005 13:34  |
cduggins
Messages: 1
Registered: April 2005
|
Junior Member |
|
|
I am a little confused. I am in a Oracle course and one of the task is to write a script that assigns a cost for shipping a number of items. -Up to 3 qty cost $5, 4-6 cost $ 7.50 7-10 cost $10.00 and over 10 cost $12.00. A block is needed to check the quantity provided by a host variable and determine shipping cost. Display shipping cost to screen, test by using 5 and 12 qty's. Any guidance would be helpful.
CD
|
|
|
|
| Re: Determining Shipping cost [message #116121 is a reply to message #116099] |
Sun, 17 April 2005 22:13  |
 |
djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS Australia
|
Senior Member
Account Moderator |
|
|
A crude way of doing it would be to use a DECODE statement.
select decode(5,1,5,2,5,3,5,4,7.5,5,7.5,6,7.5,7,10,8,10,9,10,10,10,12) from dual;
Will give 7.5
|
|
|
|
| Re: Determining Shipping cost [message #116145 is a reply to message #116099] |
Mon, 18 April 2005 02:29 |
Frank
Messages: 7901
Registered: March 2000
|
Senior Member |
|
|
Depends on your version:
ONTW>create table tst as select rownum tst_value
2 from all_objects
3 where rownum <= 15
4 /
Table created.
ONTW>
ONTW>select case when tst_value <= 3 then 5
2 when tst_value <= 6 then 7.5
3 when tst_value <= 10 then 10
4 when tst_value > 10 then 12
5 end cost
6 from tst
7 /
COST
----------
5
5
5
7.5
7.5
7.5
10
10
10
10
12
12
12
12
12
15 rows selected.
Or, if that does not work because of version problems (case-statement not recognised)
ONTW>create table tst as select rownum tst_value
2 from all_objects
3 where rownum <= 15
4 /
Table created.
ONTW>
ONTW>select decode( sign(tst_value - 10)
2 , 1, 12
3 , decode( sign(tst_value - 6)
4 , 1, 10
5 , decode( sign(tst_value - 3)
6 , 1, 7.5
7 , 5
8 )
9 )
10 ) cost
11 from tst
12 /
COST
----------
5
5
5
7.5
7.5
7.5
10
10
10
10
12
12
12
12
12
15 rows selected.
The second option uses sign. Sign(x) returns 1 if x > 0, returns 0 if x = 0 and returns -1 if x < 0.
hth
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
