Subject | Stored procedure in select with grouping |
---|---|
Author | Rick Debay |
Post date | 2005-12-22T17:43:51Z |
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?
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?