Home » SQL & PL/SQL » Client Tools » DEFINE VARIABLES and AMPERSAND (ORACLE DB SQL)
DEFINE VARIABLES and AMPERSAND [message #608041] Fri, 14 February 2014 08:38 Go to next message
hans_cellc
Messages: 11
Registered: February 2014
Junior Member
Please assist.
Below is my code for sql.

Second code does not work, just curious from a programming point of view WHY?

Is my coding wrong or is there another way.

-- DECODE
SELECT last_name, salary,
    DECODE(TRUNC(salary / 2000, 0),
                        0, 0.00,
                        1, 0.09,
                        2, 0.20,
                        3, 0.30,
                        4, 0.40,
                        5, 0.42,
                        6, 0.44,
                           0.45) AS "Tax Rate"
FROM employees
WHERE department_id = 80;


-- DECODE with variables  _____________________NOT WORKING
DEFINE tax_rate = (DECODE (TRUNC(salary / 2000, 0),
                        0, 0.00,
                        1, 0.09,
                        2, 0.20,
                        3, 0.30,
                        4, 0.40,
                        5, 0.42,
                        6, 0.44,
                           0.45)) 
SELECT last_name, salary, &tax_rate AS "Tax Rate"  
FROM employees
WHERE department_id = 80
ORDER BY &tax_rate;
Re: DEFINE VARIABLES and AMPERSAND [message #608044 is a reply to message #608041] Fri, 14 February 2014 08:43 Go to previous messageGo to next message
cookiemonster
Messages: 11287
Registered: September 2008
Location: Rainy Manchester
Senior Member
because decode can only be used in sql statments.
Define isn't a sql statement, it's a sqlplus command.
Re: DEFINE VARIABLES and AMPERSAND [message #608045 is a reply to message #608044] Fri, 14 February 2014 08:46 Go to previous messageGo to next message
hans_cellc
Messages: 11
Registered: February 2014
Junior Member
Thanks for your prompts response.
Is there any other way to do this?
Re: DEFINE VARIABLES and AMPERSAND [message #608048 is a reply to message #608045] Fri, 14 February 2014 09:13 Go to previous messageGo to next message
cookiemonster
Messages: 11287
Registered: September 2008
Location: Rainy Manchester
Senior Member
Depends on what you are tying to accompllish, there is nothing in the above example to suggest defines are even needed.
Re: DEFINE VARIABLES and AMPERSAND [message #608051 is a reply to message #608048] Fri, 14 February 2014 09:17 Go to previous messageGo to next message
hans_cellc
Messages: 11
Registered: February 2014
Junior Member
Actually I have no specific task at hand, I was just curious to know if I could set a variable and then use it.
Re: DEFINE VARIABLES and AMPERSAND [message #608052 is a reply to message #608048] Fri, 14 February 2014 09:19 Go to previous messageGo to next message
cookiemonster
Messages: 11287
Registered: September 2008
Location: Rainy Manchester
Senior Member
This should work:
DEFINE tax_rate = ' (DECODE (TRUNC(salary / 2000, 0), 0, 0.00, 1, 0.09, 2, 0.20, 3, 0.30, 4, 0.40, 5, 0.42, 6, 0.44, 0.45)) '
SELECT last_name, salary, &tax_rate AS "Tax Rate"  
FROM employees
WHERE department_id = 80
ORDER BY &tax_rate;

define has to be on a single line and it needed quotes.
Re: DEFINE VARIABLES and AMPERSAND [message #608054 is a reply to message #608052] Fri, 14 February 2014 09:22 Go to previous message
hans_cellc
Messages: 11
Registered: February 2014
Junior Member
I change my sample code to one line within the single quotes and it works perfectly, THANK YOU.
Previous Topic: Installation of Oracle SQL Developer hangs
Next Topic: tables are not getting displayed under schema in sqldeveloper
Goto Forum:
  


Current Time: Fri Dec 26 16:11:35 CST 2014

Total time taken to generate the page: 0.18063 seconds