-1

I'm trying to count the number of unique invoice codes in table. The codes are numbers, but it might by that the field is a text field. I'm not sure how to check that.

When I run this code:

SELECT
count(DISTINCT DAB050.REC_LIST) as invoice_number
from "DAB050.ADT" DAB050
WHERE DAB050.BUCH_DATUM = '2021-02-16'

I get the error:

[sql_Query: Error 7200:  AQE Error:  State = S0000;   NativeError = 2124;  [iAnywhere Solutions][Advantage SQL Engine]Invalid operand for operator: COUNT]

Not really sure how to solve this. Any tips?

egg
  • 373
  • 1
  • 7
  • 15
  • 1
    I don't have access to Advantage but I ran a similar query in Sybase ASE and it works: `select count(distinct rec_list) as invoice_number from t where buch_datum = '2021-02-16'`. Maybe the engines are not quite the same ones. – The Impaler Feb 25 '21 at 19:11
  • yeah... this doesn't work on my setup. I think it's todo with DISTINCT only working on certain types. Maybe there is a way to convert my rec_list into another format, on the fly? – egg Feb 26 '21 at 08:44
  • You're lacking information here. What data type is `REC_LIST`? What data type is `BUCH_DATUM`? You've given us none of the details about the data types of your columns, but expect us to explain why you're getting an invalid data type error. How do you expect us to help? – Ken White Mar 02 '21 at 21:25

1 Answers1

0

I believe that the DISTINCT operation is not allowed on blob, clob, memo or nmemo data types. You could get that error message if REC_LIST is one of those.

If the REC_LIST is a memo or nmemo, you can probably use SUBSTRING() to make them fixed size. You can also use the CONVERT or CAST functions.

http://devzone.advantagedatabase.com/dz/webhelp/Advantage10/index.html?devguide_the_advantage_configuration_utility.htm

Alex W
  • 3,283
  • 1
  • 19
  • 25