QoQ error involving Min() function

[Guest]

I'm getting an error "The aggregate function [MIN(expression)] cannot operate on an operand of type [NULL]. " when trying to execute a query of queries (see below). The column I am applying the function to does NOT have any null values in it - I verified that by dumping the query (I even tried adding "where prmy_high_nbr is not null" to the query).

Does anyone know what causes this? We recently upgraded to MX7 which I'm guessing has something to do with this. Product version 7,0,2,142559 as reported by Server.ColdFusion.ProductVersion.

It may be pertinent that the value in the column is set using QuerySetCell() in some cases.

<cfquery name="qRanges" dbtype="query">
<!---this time we use min in case we extended a B in one group but not the other--->
select SortKey, predir_abbr, str_suffix_abbr, postdir_abbr, prmy_low_nbr, min(prmy_high_nbr) prmy_high_nbr,
prmy_odd_even_code, crid_id, str_name, int100, intLow, urb, zip_code
from qOERanges
<!---group by consolidates ranges--->
group by SortKey, predir_abbr, str_suffix_abbr, postdir_abbr,
prmy_low_nbr, prmy_odd_even_code, crid_id, str_name, int100, intLow, urb, zip_code
order by SortKey, intLow, prmy_odd_even_code
</cfquery>
Ken

[Dan_Bracuk]

I see three problems.

First, if I remember correctly, in Q of Q you need the keyword "as" when setting a column alias.

Next, in any db I've used, the number of non-aggregate fields in your select clause must equal the number of fields in your group by clause.

Finally, when selecting an aggregate, you can't order by fields you didn't select.

[Guest]

Thanks for the response. I did try adding AS which is optional and it didn't make a difference (the query ran fine before as is and there are two queries in the same file that use similar syntax and they run OK).

Your other points are valid but my query does comply with both of them (it would produce a different error if it didn't).

Incidentally the QuerySetCell() doesn't have anything to do with it because I still get the error even if I don't use it.

This is a previous query in the same file that runs OK without an error:

<cfquery name="qOERanges" dbtype="query">
select SortKey, predir_abbr, str_suffix_abbr, postdir_abbr, prmy_low_nbr, min(prmy_high_nbr) prmy_high_nbr,
prmy_odd_even_code, crid_id, str_name, int100, intLow, 1 as intOE, urb, zip_code
from qOERanges
where prmy_odd_even_code <> 'O'
group by SortKey, predir_abbr, str_suffix_abbr, postdir_abbr,
prmy_low_nbr, prmy_odd_even_code, crid_id, str_name, int100, intLow, urb, zip_code
UNION
select SortKey, predir_abbr, str_suffix_abbr, postdir_abbr, prmy_low_nbr, min(prmy_high_nbr) prmy_high_nbr,
prmy_odd_even_code, crid_id, str_name, int100, intLow, 2 as intOE, urb, zip_code
from qOERanges
where prmy_odd_even_code <> 'E'
group by SortKey, predir_abbr, str_suffix_abbr, postdir_abbr,
prmy_low_nbr, prmy_odd_even_code, crid_id, str_name, int100, intLow, urb, zip_code
order by SortKey, intOE, intLow, prmy_odd_even_code
</cfquery>


Ken

[Newsgroup_User]

QoQ does weird things if there are *any* columns in the contributing
queries (whether they are in the select statement or not) that have nulls
in them, and as far as I can tell this is governed by which direction the
wind is blowing from, more than anything else.

QoQ is as flaky and as unpredictable as f***. I would only ever use it for
the most trivial of things (yes, I would expect your example to be not much
of a stretch either, but...)

Is there no way to do this data processing on the DB instead of CF?

--
Adam

[Guest · Accepted solution]

Thanks for the response.

There's no way I could do this all on the db end as it involves a series of manipulations and QoQs. The QoQs primarily consolidate rows after they have been manipulated in code.

I did find a workaround for the problem. I found if I use min(CAST(prmy_high_nbr as integer)) it runs OK. It also works if I cast as varchar. Apparently because the column prmy_high_nbr itself is the result of applying the min() function in a previous query CF has lost track of the datatype of the column.

So I consider it a bug but happily I found a workaround.

Ken