in the Having clause i`m 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.
enter image description here

>Solution :

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