> 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?

Yes and yes:

Fails:

SELECT
p.NDC9, p.GPI, p.TEE,
(SELECT ATOM_UNIT FROM P_SELECT_IS_ATOMIC( p.UNIT_DOSE_USE )), <--
beginning of attempt to encapsulate rule
(CASE WHEN p.UNIT_DOSE_USE IN ('U') THEN p.UNIT_DOSE_SZ ELSE 1
END), <-- same rule, but not encapsulated yet
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

Works:

SELECT
p.NDC9, p.GPI, p.TEE,
(CASE WHEN p.UNIT_DOSE_USE IN ('U') THEN 'U' ELSE '' END), <--
simple (as of this moment) rule
(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 ( :RPL_GRP ) p
WHERE
(p.DECIMALQTY - p.ORDER_QTY - p.PO_QTY - p.BO_QTY) <> 0
GROUP BY
p.NDC9, p.GPI, p.TEE, 4, 5
HAVING
SUM (p.DECIMALQTY - p.ORDER_QTY - p.PO_QTY - p.BO_QTY) > 0

The intent is to group together items that, according to the rule, can
be grouped together.

I have not tried it with FB2 beta, I'm "discussing" with the networking
group the need for another test machine :-(
In your opinion, does the subselect in the result set fall within the
spirit and/or letter of SQL grouping rules?

> one approach would be to add another output field

Good idea, I don't see any issues, practical or philosophical, to doing
this.

-----Original Message-----
From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of Ann W. Harrison
Sent: Thursday, December 29, 2005 2:55 PM
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] Stored procedure in select with grouping

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




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

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