I have the following problem to resolve:
Create a list of weekly salaries from the employees table where the weekly salary is between 700 and 3000.
The salaries should be formatted to include a $ sign and should have two decimal points like: $9999.99.
• Tables Used: Employees
I've tried this command:
SELECT TO_CHAR(ROUND(salary/12.0, 2), '$999999.99') AS "Weekly Salary"
FROM employees
WHERE TO_NUMBER(Weekly Salary, '9999999') BETWEEN 700 AND 3000;
But I get this error message:
ORA-00907: missing right parenthesis
Can you tell me please, where am I wrong?
Weekly salary? Why dividing it by 12? If that's supposed to represent number of months in a year, OK, but - you shouldn't return monthly but weekly salary, and there are 52 weeks in a year.
Something like this, perhaps?
SQL> select ename,
2 to_char(salary/52, '$9999d99') as weekly_salary
3 from employees
4 where salary/52 between 700 and 3000;
ENAME WEEKLY_SA
---------- ---------
BLAKE $2850,00
CLARK $2450,00
SCOTT $3000,00
TURNER $1500,00
ADAMS $1100,00
JAMES $950,00
FORD $3000,00
MILLER $1300,00
8 rows selected.
SQL>
As of your code: if you executed it in e.g. SQL*Plus, it would mark error spot with an asterisk:
SQL> SELECT TO_CHAR(ROUND(salary/12.0, 2), '$999999.99') AS "Weekly Salary"
2 FROM employees
3 WHERE TO_NUMBER(Weekly Salary, '9999999') BETWEEN 700 AND 3000;
WHERE TO_NUMBER(Weekly Salary, '9999999') BETWEEN 700 AND 3000
*
ERROR at line 3:
ORA-00907: missing right parenthesis
SQL>
It says that Weekly Salary can't be used like that; actually, you can't reference it at all in the same query - you'd have to either use a subquery, a CTE or use the whole expression in WHERE clause (as I did in my example).
Moreover, if you used double quotes and mixed letter case, you'll have to do it every time you reference that column.
Also, there's a mismatch in format model.
This works, though:
SQL> WITH temp AS (SELECT salary / 52 AS "Weekly Salary" FROM employees)
2 SELECT TO_CHAR ("Weekly Salary", '$9999.99') AS "Weekly Salary"
3 FROM temp
4 WHERE "Weekly Salary" BETWEEN 700 AND 3000;
Weekly Sa
---------
$2850.00
$2450.00
$3000.00
$1500.00
$1100.00
$950.00
$3000.00
$1300.00
8 rows selected.
SQL>