I am receiving an invalid identifier for "CUSTBAL"... any ideas why? Thanks!
SELECT CUSTOMER_NUM, CUSTOMER_NAME, SUM(BALANCE) AS CUSTBAL, REP.REP_NUM FROM CUSTOMER, REP WHERE CUSTOMER.REP_NUM=REP.REP_NUM AND (CUSTBAL>10000) GROUP BY CUSTOMER_NUM;
It is not working becuase CUSTBAL column does not exist in either cusomter and rep. you need to use derived table for the solution as :
select CUSTOMER_NUM, CUSTOMER_NAME, CUSTBAL, REP.REP_NUM from (
SELECT CUSTOMER_NUM, CUSTOMER_NAME, SUM(BALANCE) AS CUSTBAL, REP.REP_NUM FROM CUSTOMER, REP
WHERE CUSTOMER.REP_NUM=REP.REP_NUM GROUP BY CUSTOMER_NUM)
as tblderived where (CUSTBAL>10000)
select CUSTOMER_NUM, CUSTOMER_NAME, CUSTBAL, REP.REP_NUM from (
SELECT CUSTOMER_NUM, CUSTOMER_NAME, SUM(BALANCE) AS CUSTBAL, REP.REP_NUM FROM CUSTOMER, REP
WHERE CUSTOMER.REP_NUM=REP.REP_N
as tblderived where (CUSTBAL>10000)