Home » SQL & PL/SQL » SQL & PL/SQL » HELP WITH EASY SELECT DECODE ROUND
HELP WITH EASY SELECT DECODE ROUND [message #425551] Fri, 09 October 2009 11:03 Go to next message
justin91980
Messages: 3
Registered: October 2009
Junior Member
Hi, If anyone could help me with this. I need to round all these numbers in the DECODE but without repeating ROUND.
This way works:

SELECT title "Title", category "Category",retail "Current Price",
DECODE(category,
'COMPUTER',(ROUND(retail * 1.1,2)),
'FITNESS', (ROUND(retail * 1.15,2)),
'SELF HELP', (ROUND(retail * 1.25,2)),
(ROUND(retail * 1.03,2)))"Revised Price"
FROM books
ORDER BY category, title;

But I need something more like this:

SELECT title "Title", category "Category",retail "Current Price",
DECODE (ROUND((category,
'COMPUTER',retail * 1.1,
'FITNESS', retail * 1.15,
'SELF HELP', retail * 1.25,
retail * 1.03)"Revised Price"),2)
FROM books
ORDER BY category, title;

so that ROUND is not repeated more than once..they all need to be rounded to two decimal places.
any help would be great. Thanks



[Updated on: Fri, 09 October 2009 11:12]

Report message to a moderator

Re: HELP WITH EASY SELECT DECODE ROUND [message #425565 is a reply to message #425551] Fri, 09 October 2009 12:06 Go to previous messageGo to next message
justin91980
Messages: 3
Registered: October 2009
Junior Member
Ok, someone helped me figure it out:

SELECT title "Title", category "Category",retail "Current Price",
ROUND(DECODE(category, 'COMPUTER',retail * 1.1,
'FITNESS', retail * 1.15,
'SELF HELP', retail * 1.25,
retail * 1.03),2) "Revised Price"
FROM books
ORDER BY category, title;
Re: HELP WITH EASY SELECT DECODE ROUND [message #425569 is a reply to message #425551] Fri, 09 October 2009 12:11 Go to previous messageGo to next message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use ROUND outside DECODE.

Don't post your title in UPPER.
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version with 4 decimals.

Regards
Michel
Re: HELP WITH EASY SELECT DECODE ROUND [message #425571 is a reply to message #425569] Fri, 09 October 2009 12:13 Go to previous messageGo to next message
justin91980
Messages: 3
Registered: October 2009
Junior Member
Ok thanks, I have never used a forum before.
I will read up on the correct etiquette.
Justin
Re: HELP WITH EASY SELECT DECODE ROUND [message #425572 is a reply to message #425571] Fri, 09 October 2009 12:20 Go to previous message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Sorry, I didn't see your post before answering but I open anout 10 pages at same time and when your turn came, you already found the answer.

Regards
Michel
Previous Topic: Converting TIMESTAMP from one timezone to other
Next Topic: Firing a Trigger on a time
Goto Forum:
  


Current Time: Tue Sep 27 09:22:05 CDT 2016

Total time taken to generate the page: 0.05913 seconds