Oracle PL/SQL Variable in HAVING clause

Question

I'm having some troubles with putting a variable in a HAVING clause, the problem is that I need COUNT(*) to be greater than the variable.

I'm using ORACLE Database XE 11.2

DECLARE


  cnt1 NUMBER;
  cnt2 NUMBER;
  res NUMBER;
BEGIN
  SELECT COUNT(*)
    INTO cnt1
    FROM BESTELLING;

  SELECT COUNT(*)
    INTO cnt2
    FROM ARTIKEL;

  res := cnt1 / cnt2;
END;
/

SELECT A.Naam, COUNT(*) AS HOEVEEL_VERKOCHT
FROM Artikel A, Winkelwagen W
WHERE A.Artikel_ID = W.Artikel_ID
AND W.Datum_Besteld IS NOT NULL
GROUP BY A.Naam
HAVING COUNT(*) > ?res?
ORDER BY COUNT(*) DESC;

Answer 1

Place queries from PL/SQL directly in your main query, as subqueries:

.......
HAVING COUNT(*) > 
   (  SELECT COUNT(*) FROM BESTELLING )   /
   (  SELECT COUNT(*) FROM ARTIKEL )
ORDER BY COUNT(*) DESC;

Answer 2

such way wont work ?

DECLARE


  cnt1 NUMBER;
  cnt2 NUMBER;
  result NUMBER;
BEGIN
  SELECT COUNT(*)
    INTO cnt1
    FROM BESTELLING;

  SELECT COUNT(*)
    INTO cnt2
    FROM ARTIKEL;

  result := cnt1 / cnt2;
SELECT A.Naam, res, COUNT(*) AS HOEVEEL_VERKOCHT
FROM Artikel A, Winkelwagen W
WHERE A.Artikel_ID = W.Artikel_ID
AND W.Datum_Besteld IS NOT NULL
GROUP BY A.Naam, res
HAVING COUNT(*) > result
ORDER BY COUNT(*) DESC;

END;
/

I change variable res to result , because it seems you have column called res