How to display employee names instead of employee id while keeping the ","-CodePudding

I have several tables that relate to employees and the total amount of sales in dollars and I have to follow these instructions right here:

The company wants to see the total sale amount per sales person (salesman) for all orders. Assume that some online orders do not have any sales person involved. Display the salesman full name and the total sale amount for that employee. Include also the Total Sale amount for all Orders without involvement of a salesman. Exclude sales persons whose first name end on y (like Lily or Daisy). Include only those sales persons who were responsible for more than 4 million dollars in sale. Sort the result so that bigger sales are shown first. Note; Do NOT use LIKE operator.

I already have a query statement written in which it shows the employee id instead of the employee name which is what is needed for this query question to be answered properly. In this case the salesman id "," are sales people without names so for ex. sales over the internet therefore no specific name. How do I make it so that I keep the "," but instead show the names of the employee and not their ID Here is the statement:

SELECT NVL(to_char(o.salesman_id), ',') AS "Emp Name", RPAD(to_char(SUM(i.quantity * i.unit_price),'$999,999,999.99'), 16, ' ') AS "Total Sale"
FROM (orders o 
INNER JOIN order_items i ON i.order_id = o.order_id) 
GROUP BY o.salesman_id 
HAVING SUM(i.quantity * i.unit_price) > 4000000.00
ORDER BY 2 DESC;

Heres the output of the query:

CodePudding user response：

Looks like outer join to me.

I don't have your tables so I'll use Scott's emp to illustrate it.

SQL> select empno, ename from emp where deptno = 10;

     EMPNO ENAME
---------- ----------
      7782 CLARK
      7839 KING
      7934 MILLER

I'll use those EMPNO values instead of your 62 and 64 (as displayed on the screenshot). your_query CTE represents output you currently have. Then outer join it with table that contains employee names (emp in my case), but leave , for unknown employees:

SQL> with
  2  your_query (emp_name, total_sale) as
  3    (select ','   , 100 from dual union all
  4     select '7782', 200 from dual union all
  5     select '7839', 300 from dual
  6    )
  7  select nvl(e.ename, q.emp_name) ename,
  8         q.total_sale
  9  from your_query q left join emp e on q.emp_name = to_char(e.empno);

ENAME      TOTAL_SALE
---------- ----------
CLARK             200
KING              300
,                 100

SQL>