Help with Oracle SQL Developer - Subquery & Join will not work.

Applications Programming
1

Greetings all,

Hopefully there is someone out there on this forum who can help with this Oracle SQL Developer issue I have.

Here is the code:

CREATE OR REPLACE VIEW
  SALES_OVER_30000_WITH_TOTAL
AS
  SELECT
    E.FIRST_NAME || ' ' || E.LAST_NAME AS EMPLOYEE_NAME,
    SUM(SO.TOTAL) AS TOTAL_SOLD
  FROM
    EMPLOYEE E
  LEFT OUTER JOIN 
    CUSTOMER C 
  ON
    C.SALESPERSON_ID = E.EMPLOYEE_ID
  LEFT OUTER JOIN 
    SALES_ORDER SO 
  ON
    SO.CUSTOMER_ID = C.CUSTOMER_ID
  GROUP BY EMPLOYEE_NAME
  HAVING SUM(SO.TOTAL) > 30000
;
SELECT
  EMPLOYEE_NAME,
  TOTAL_SOLD
FROM
  SALES_OVER_30000_WITH_TOTAL
ORDER BY EMPLOYEE_NAME DESC
;

The error being produced is:

Error at Command Line:17 Column:11
Error report:
SQL Error: ORA-00904: "EMPLOYEE_NAME": invalid identifier
00904. 00000 -  "%s: invalid identifier"
*Cause:    
*Action:

Which is..

GROUP BY EMPLOYEE_NAME

I have pretty much tried everything I simply cannot get this to work at all :wall:

Any suggestions would be greatly appreciated..

Thanks in advance

2

Try:

[...]
group by
  E.FIRST_NAME || ' ' || E.LAST_NAME
1 Like
3

Hi, thanks for reply.

Was a standard syntax error in the end.

Can post new code if anyone is interested.