In the Having clause I am trying to compare 2 column because the condition is that CUST_CREDIT_LIMIT need to be multiply by 500 and compare with sum(s.amount_sold). Any ideas how to deal with this problem.
select (c.CUST_FIRST_NAME ||' '||c.CUST_LAST_NAME) as "CUSTOMER_NAME",
to_number(substr(c.CUST_INCOME_LEVEL, -7, 8),'999999999') as "UPPER_INCOM_LEVEL",
sum(s.amount_sold) as "TOTAL_AMOUNT",
(case when c.CUST_CREDIT_LIMIT <=1500 then 'Low limit'
when c.CUST_CREDIT_LIMIT >1500 then 'High limit'
end) as "CREDIT_LIMIT_LEVEL",
c.CUST_VALID
from sh.CUSTOMERS c JOIN sh.SALES s
on s.CUST_ID = c.CUST_ID
where CUST_VALID = 'A'
and CUST_INCOME_LEVEL like '%-%'
GROUP BY c.CUST_FIRST_NAME, c.CUST_LAST_NAME, s.AMOUNT_SOLD, c.CUST_INCOME_LEVEL, c.CUST_CREDIT_LIMIT, c.CUST_VALID
having s.amount_sold > c.CUST_CREDIT_LIMIT*500
It looks like you want to put the aggregation into a sub-query with just the SALES table and then JOIN the result of that to the CUSTOMERS table and can compare the credit limit to the sales amount in the join condition (and remove the GROUP BY and HAVING clauses from the outer query):
SELECT c.cust_first_name || ' ' || c.cust_last_name AS customer_name,
TO_NUMBER(SUBSTR(c.cust_income_level, -7), '9999999') AS upper_income_level,
s.total_amount,
CASE
WHEN c.cust_credit_limit <= 1500
THEN 'Low Limit'
WHEN c.cust_credit_limit > 1500
THEN 'High Limit'
END AS credit_limit_level,
c.cust_valid
FROM sh.customers c
INNER JOIN (
SELECT cust_id,
SUM(amount_sold) AS total_amount
FROM sh.sales
GROUP BY cust_id
) s
ON ( c.cust_id = s.cust_id
AND s.total_amount > c.cust_credit_limit * 500 )
WHERE c.cust_valid = 'A'
AND c.cust_income_level LIKE '%-%' -- Check for a hyphen anywhere
-- AND SUBSTR(c.cust_income_level, -8, 1) = '-' -- Check for a hyphen in a specific place