0

I have a SQL database and one column of that database is a short array.

Column name Data type
Order Id Integer
Timestamp Long
Activity Short []

I want to query this table and get a count of rows that include a given short value in this Activity column. I have tried following SQL statement.

private static final String SYMPTOMS_GET_ACTIVITY_TYPE_COUNT =
    "Select count(_val) from PatientTrigger where orderId = ? and  ? = ANY(activity)";

But I am getting the following error: Unsupported expression: ANY(ACTIVITY) [type=Aggregate]

I am using Apache Ignite caches. Please tell me how to do this correctly.

sachith
  • 129
  • 2
  • 12

1 Answers1

0

The short version is that you can't. As noted in the comments, an array isn't a SQL type, at least not in ANSI SQL-99 which is what Ignite supports.

Typically you'd resolve this by normalising your cache and using a join in your SQL. That is, you'd have a one-to-many relationship between your PatientTrigger table and the (new) Activity table.

Stephen Darlington
  • 51,577
  • 12
  • 107
  • 152
  • Could you please explain this further. its a short [ ]. `Typically you'd resolve this by normalising your cache and using a join in your SQL.` – sachith Sep 08 '21 at 04:10
  • If you leave it as a short array, you can't query it using SQL. You need to create a new table, with each entry in your Array as a row. Then you can use a SQL join to query the activity. – Stephen Darlington Sep 08 '21 at 13:57