1

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.

Community
  • 1
  • 1
MrCzeal
  • 55
  • 8
  • Maybe the 'in general...' bit in [this answer](http://stackoverflow.com/a/9315951/266304) might help? The 'having' clause can't use the column alias you've defined, but that doesn't seem to be what it's complaining about... – Alex Poole Feb 29 '16 at 17:40

1 Answers1

1

@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

Jim K
  • 12,824
  • 2
  • 22
  • 51
  • 1
    You can still have `AS alias`, you just can't refer to it by that name in the `having` clause - only in an `order by` clause. The `AS` keyword is optional if it still doesn't like that. It sounds like OpenOffice is complaining about something else though - it isn't getting an ORA-00904. – Alex Poole Feb 29 '16 at 17:44