Need Help With SQL Method

Classic Servoy
1

Hi All

As I am trying to update from 3.5.x to 5.1.3 I keep running against subtle differences.
In 3.5 is the following getDataByQuery query.

var query_pk = "SELECT donation_detail_id FROM donation_detail WHERE donation_detail_id IN " + "(SELECT (donation_detail_id) FROM donation_detail WHERE detail_date = ? GROUP BY detail_query_totals)" + "ORDER BY detail_query_totals asc";

In 5.1.3 it throws the following error,

SQL Anywhere Error -149: Function or column reference to ‘donation_detail_id’ must also appear in a GROUP BY

Thanks in advance for all your assistance.

2

What would you like to do with this query?

It is quite normal that this query will fail, so please explain what you intended to do with this query.

3

If you use GROUP BY in your query, you need to specify how you want to group the fields that you select. By doing SUM, AVG, MIN, MAX, FIRST, LAST, …

For example for your query:

var query_pk = "SELECT donation_detail_id FROM donation_detail WHERE donation_detail_id IN " + "(SELECT FIRST(donation_detail_id) FROM donation_detail WHERE detail_date = ? GROUP BY detail_query_totals)" + "ORDER BY detail_query_totals asc";
4

Thanks Joas,

If you use GROUP BY in your query, you need to specify how you want to group the fields that you select. By doing SUM, AVG, MIN, MAX, FIRST, LAST, …

The key word I was missing was “MIN”. I rechecked my 3.5 method and it has “MIN”.

MY BAD!!

Thanks a lot.

5

It would seen very expensive to use a subquery here, certainly if this is is a large table.
If you need more complex stuff to happen in the subquery you could try:

with tmp as (
select distinct dd.donation_detail_id
from
donation_detail as dd
–some more (left outer) join(s)
where
dd.detail_date = ?
– some more clauses
)
select
donation_detail_id
from
donation_detail
join dd on (
dd.donation_detail_id = donation_detail.donation_detail_id
)
order by
detail_query_totals asc

this will perform a lot better than using subqueries
(works on sybase 9+ and MS-sql 2005+, other backends I don’t know…)

Hope this helps,
Hans Nieuwenhuijs

6

tommygill:
Thanks Joas,

If you use GROUP BY in your query, you need to specify how you want to group the fields that you select. By doing SUM, AVG, MIN, MAX, FIRST, LAST, …

The key word I was missing was “MIN”. I rechecked my 3.5 method and it has “MIN”.

MY BAD!!

Thanks a lot.

I’m not sure this solves your problem. As far as I can see the query still has a problem.
There is GROUP BY detail_query_totals, but detail_query_totals is not in your result.

Please can you explain English what this query should do, so that I can see if I can propose a good query for you.

7

Hi Tom,

You don’t need a sub-select at all:

SELECT MIN(donation_detail_id) 
FROM donation_detail 
WHERE detail_date = ? 
GROUP BY detail_query_totals 
ORDER BY detail_query_totals ASC

This does exactly the same and it’s MUCH more efficient.