Found the weekly salary from database-CodePudding

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?

CodePudding user response：

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>