No one has an idea as to what the problem is? Why does a case work but
a select doesn't?

-----Original Message-----
From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of Rick Debay
Sent: Thursday, December 22, 2005 12:44 PM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Stored procedure in select with grouping

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)"

If I change the stored procedure back to a case statement, similar the
case statement in column five, it works.
The objective was to encapsulate the rules in one stored procedure, as
they change, and are used all over the code.
The case statement in column five would also be changed to a similar SP
select if this worked.

Is there a different approach, and is this even legal?


------------------------ Yahoo! Groups Sponsor --------------------~-->
Get Bzzzy! (real tools to help you find a job). Welcome to the Sweet
Life.
http://us.click.yahoo.com/KIlPFB/vlQLAA/TtwFAA/67folB/TM
--------------------------------------------------------------------~->

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Visit http://firebird.sourceforge.net and click the Resources item on
the main (top) menu. Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Yahoo! Groups Links