Rick Debay wrote:

>
> The following query works fine as long as I don't try to group. Once I
> do I get the following error.
>
> SELECT
> p.NDC9, p.GPI, p.TEE,
> (SELECT ATOM_UNIT FROM P_SELECT_IS_ATOMIC( p.UNIT_DOSE_USE )),
> (CASE WHEN p.UNIT_DOSE_USE IN ('U') THEN p.UNIT_DOSE_SZ ELSE 1 END),
> SUM (p.DECIMALQTY - p.ORDER_QTY - p.PO_QTY - p.BO_QTY)
> FROM
> P_RPL_NDC9_2 ( '010' ) p
> WHERE
> (p.DECIMALQTY - p.ORDER_QTY - p.PO_QTY - p.BO_QTY) <> 0
> GROUP BY
> p.NDC9, p.GPI, p.TEE, 4, 5
>
> "Invalid expression in the select list (not contained in either an
> aggregate function or the GROUP BY clause)"

Do you get that error if you omit the subselect from the stored
procedure from the select list (and of course also from the group by
list?

Have you tried the query with Firebird 2 beta?

>
> If I change the stored procedure back to a case statement, similar the
> case statement in column five, it works.

OK ... It did take me about a half an hour (pleasantly spent eating
lunch) to figure out what that sentence means - if, indeed I did figure
it out. When you say "the stored procedure", you're referring to
P_SELECT_IS_ATOMIC, not P_RPL_NDC9_2 or some other procedure, right?
And that procedure is essentially a case statement, right?

> The objective was to encapsulate the rules in one stored procedure, as
> they change, and are used all over the code.

That's a reasonable goal, but you may need to handle it slightly
differently - one approach would be to add another output field to
P_RPL_NDC9_2 that is P_SELECT_IS_ATOMIC( :UNIT_DOSE_USE )

> The case statement in column five would also be changed to a similar SP
> select if this worked.

If you can build the "rule enforcing" procedure (P_SELECT_IS_ATOMIC)
reference into the working procedures (e.g. P_RPL_NDC9_2) rather than
using them in subselects referenced in a group by, your chances of
success would be higher.

>

Regards,


Ann