Oracle task issue in having clause-CodePudding

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

CodePudding user response：

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

CodePudding user response：

Just like you said (looks like you're missing the sum aggregate function in your having clause) (also, you should put all non-aggregated columns into the group by clause; yours contains s.amount_sold, while it shouldn't):

group by c.cust_first_name, 
         c.cust_last_name,
         substr(c.cust_income_level, -7, 8),
         case when c.cust_credit_limit <= 1500 then 'Low limit'
              when c.cust_credit_limit >  1500 then 'High limit'
         end,
         c.cust_valid
having sum(s.amount_sold) > c.cust_credit_limit * 500