Search code examples
sqloracleopenoffice-base

SQL OpenOffice Base "Not a Condition in Statement"

So I tried using OpenOffice Base and I had a hard time. Now, I have this SQL query here and it works well:

SELECT  "CUSTOMER"."CREDIT_LIMIT" AS "CREDIT_LIMIT",
         COUNT(*) AS "TOTAL_NUMBER"
FROM "CUSTOMER"
WHERE "SLSREP_NUMBER" = 6
GROUP BY "CREDIT_LIMIT";

Query:

| CRED_LIMIT | TOTAL_NUMBER |
|         1500       |                 1             |
|           750       |                 2             |
|         1000       |                 1             |

Now my problem is when I add this : AND ("TOTAL_NUMBER" > 1) 

SELECT  "CUSTOMER"."CREDIT_LIMIT" AS "CREDIT_LIMIT",
         COUNT(*) AS "TOTAL_NUMBER"
FROM "CUSTOMER"
WHERE "SLSREP_NUMBER" = 6 AND "TOTAL_NUMBER" > 1
GROUP BY "CREDIT_LIMIT";

Open Office would throw an Error: "Not a condition in statement"

My questions are: is there something wrong with my syntax? Have I written something wrong? or is my copy of OOBase defective? or am I missing something?enter image description here


Update: I tried using HAVING as suggested by potashin (Thank you for answering) and it seems like it's still not working.

Solution

  • @potashin was close but didn't quite have it right. Do not say AS "TOTAL_NUMBERS". Also, Base does not require quotes around UPPER case names.

    SELECT CUSTOMER.CREDIT_LIMIT AS CREDIT_LIMIT, COUNT(*)
FROM CUSTOMER
WHERE SLSREP_NUMBER = 6
GROUP BY CREDIT_LIMIT
HAVING COUNT(*) > 1

    See also: http://www.w3resource.com/sql/aggregate-functions/count-having.php