IllegalArgumentException in group by clause

[Paritosh Ranjan]

I am trying to do group by based on ( a string and an enum).

Somehow, I am getting,

java.lang.IllegalArgumentException: Expression-based filters cannot be
manually evaluated.

Anyone any idea?

The code snippet follows:
Query query =
dc.query().from(table).select(table.getColumns()).toQuery().where("FREQUENCY_F
= -1000").
groupBy(groupByColumns.toArray(new Column[0]));

DataSet dataset = dc.executeQuery(query);

[Kasper Sørensen]

Hello Paritosh,

There are two ways to fix that. Basically what you are doing is not valid because MetaModel cannot parse the expression that you're giving it (it's parser is very very simple because we encourage you to use java types in stead).

Solution 1: Get the column and use it:

Column column = table.getColumnByName("FREQUENCY_F");

....where(column, OperatorType.EQUALS, -1000)

Solution 2: Use the shorthand version of the above:

...where("FREQUENCY_F", OperatorType.EQUALS, -1000);

Hope it works.

Regards,

Kasper

--
You received this message because you are subscribed to the Google Groups "DataCleaner-dev" group.
To post to this group, send email to datacle...@googlegroups.com.
To unsubscribe from this group, send email to datacleaner-d...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/datacleaner-dev?hl=en.

[Paritosh Ranjan]

The group by is still giving exception, though the exception has
changed to

WARN JdbcDataContextStrategy - Exception occurred when executing
query: Not in aggregate function or group by clause:
org.hsqldb.Expression@7673a2 in statement [SELECT
"DATASET"."REPRESENTATION", "DATASET"."REGIONCODE",
"DATASET"."KNOWLEDGETYPE", "DATASET"."FREQUENCY",
"DATASET"."FREQUENCY_F", "DATASET"."STATUS", "DATASET"."MEMO" FROM
PUBLIC."DATASET" WHERE "DATASET"."FREQUENCY_F" = -1000 GROUP BY
"DATASET"."REPRESENTATION", "DATASET"."KNOWLEDGETYPE"]

And thus the result set does not contains data for all columns. It has
more to do with hsql db than with datacleaner though.

I used this query

Column column = table.getColumnByName("FREQUENCY_F");

Column representationColumn =
table.getColumnByName("REPRESENTATION");
Column knowledgeTypeColumn = table.getColumnByName("KNOWLEDGETYPE");

Query query =
dc.query().from(table).select(table.getColumns()).toQuery().where(column,
OperatorType.EQUALS_TO,
-1000).groupBy(representationColumn, knowledgeTypeColumn);
DataSet dataset = dc.executeQuery(query);

On Dec 6, 5:55 pm, Kasper Sørensen <kas...@eobjects.dk> wrote:
> Hello Paritosh,
>
> There are two ways to fix that. Basically what you are doing is not valid
> because MetaModel cannot parse the expression that you're giving it (it's
> parser is very very simple because we encourage you to use java types in
> stead).
>
> Solution 1: Get the column and use it:
>
> Column column = table.getColumnByName("FREQUENCY_F");
> ....where(column, OperatorType.EQUALS, -1000)
>
> Solution 2: Use the shorthand version of the above:
>
> ...where("FREQUENCY_F", OperatorType.EQUALS, -1000);
>
> Hope it works.
>
> Regards,
> Kasper
>
> On Mon, Dec 6, 2010 at 12:36 PM, Paritosh Ranjan

> <paritoshranj...@gmail.com>wrote:

>
> > I am trying to do group by based on ( a string and an enum).
>
> > Somehow, I am getting,
>
> > java.lang.IllegalArgumentException: Expression-based filters cannot be
> > manually evaluated.
>
> > Anyone any idea?
>
> > The code snippet follows:
> > Query query =
>
> > dc.query().from(table).select(table.getColumns()).toQuery().where("FREQUENCY_F
> > = -1000").
> > groupBy(groupByColumns.toArray(new Column[0]));
>
> > DataSet dataset = dc.executeQuery(query);
>
> > --
> > You received this message because you are subscribed to the Google Groups
> > "DataCleaner-dev" group.
> > To post to this group, send email to datacle...@googlegroups.com.
> > To unsubscribe from this group, send email to

> > datacleaner-d...@googlegroups.com<datacleaner-dev%2Bunsu...@googlegroups.com>
> > .

[Kasper Sørensen]

Hi Paritosh,

The error you're seeing now is simply because you're creating an invalid query. I would say that this query would fail on any db. The reason for this is that you select columns that you neither aggregate or group, which will mean that the DB is not able to determine what/how to select it. Let me give you an example:

Take this data set:

| Fruit | Color | Amount |

| Banana | Yellow | 5 |

| Banana | Green | 4 |

| Apple | Green | 5 |

And then imagine you need to execute a query like this:

"SELECT fruit, color, SUM(amount) FROM fruits GROUP BY fruit"

What would you do about the color then? If you want the datastore to return every combination (eg. 5 yellow and 4 green bananas), then you should also add "color" to the group by clause. If you want all bananas (eg. 9 bananas in total) then you should not select "color".

In your case it looks like you should add the remaining unaggregated columns to the GROUP BY clause. Hope it makes sense.

Best regards,

Kasper

To unsubscribe from this group, send email to datacleaner-d...@googlegroups.com.

[paritosh ranjan]

Ya, got it.

Thanks for your help.

2010/12/7 Kasper Sørensen <kas...@eobjects.dk>