not contained in the aggregate or GROUP BY..problem with D..

If someone could help, I'd appreciate it.

I do not know my SQL programming all that well, so I'll try to explain.

Below is a query (a view actually) that is to pull up data on inventory
items. Now, I thought that this would work but, when I run it, I get a
message saying that the SC020130.ITEMNMBR field is invalid because it is not
contained in an "aggregate" or in a GROUP BY statement.

I do have this field in the GROUP BY statement by way of the ITEMNMBR field.

Is the problem with the "DISTINCT" clause? I need this clause in there to
eliminate duplicates.

When I double-click on the error message, it takes me to the very first line
of the view. I have tried lots of things to eliminate this issue, and I get
the same message (I have tried placing the table name in front of the field
in all spots, I ahve tried removing the DISTINCT phrase, I have tried taking
ITEMNMBR out of the GROUP BY statement)--but no success. Thanks, for any
input! Below is the view.



select ITEMNMBR as [ITEM], DATE1 As [FORECAST DATE], QTYTOFORECOST_I As
[FORECAST QTY],
[TOTAL QTY ON ORDER], DUEDATE As [DUE DATE], GETDATE() As [CURRENT DATE]
FROM(
select DISTINCT SC020130.ITEMNMBR, SC020230.SFLINEITEM_I, SC020230.DATE1,
SC020230.QTYTOFORECOST_I,
MRPW4060.QUANTITY, MRPW4060.DUEDATE, GETDATE() As [CURRENT DATE],
CASE WHEN GETDATE()<MRPW4060.DUEDATE--and CASE WHEN GETDATE()>SC020230.DATE1
THEN SUM(MRPW4060.QUANTITY)
ELSE 0 END As [TOTAL QTY ON ORDER]
from SC020230 As SC020230
JOIN SC020130 As SC020130
on SC020230.SFLINEITEM_I = SC020130.SFLINEITEM_I
JOIN MRPW4060 As MRPW4060
on SC020130.ITEMNMBR = MRPW4060.ITEMNMBR
where SC020230.PLANNAME_I = 'MRP FG1' and SC020130.ITEMNMBR = '89417' and
SOPTYPE = '2') As I
GROUP BY ITEMNMBR, SFLINEITEM_I, DATE1, QTYTOFORECOST_I, DUEDATE, [TOTAL QTY
ON ORDER]
ORDER BY ITEMNMBR ASC

Nevermind...I figured it out...I should not have had the SUM statement in the
select DISTINCT clause. Here is the solution:

select ITEMNMBR as [ITEM], DATE1 As [FORECAST DATE], QTYTOFORECOST_I As
[FORECAST QTY],
SUM([QTY ON ORDER]) As [TOTAL QTY ON ORDER], DUEDATE As [DUE DATE],
GETDATE() As [CURRENT DATE]
FROM(
select DISTINCT SC020130.ITEMNMBR, SC020230.SFLINEITEM_I, SC020230.DATE1,
SC020230.QTYTOFORECOST_I,
MRPW4060.QUANTITY, MRPW4060.DUEDATE, GETDATE() As [CURRENT DATE],
CASE WHEN GETDATE()<MRPW4060.DUEDATE--and CASE WHEN GETDATE()>SC020230.DATE1
THEN MRPW4060.QUANTITY
ELSE 0 END As [QTY ON ORDER]
from SC020230 As SC020230
JOIN SC020130 As SC020130
on SC020230.SFLINEITEM_I = SC020130.SFLINEITEM_I
JOIN MRPW4060 As MRPW4060
on SC020130.ITEMNMBR = MRPW4060.ITEMNMBR
where SC020230.PLANNAME_I = 'MRP FG1' and SC020130.ITEMNMBR = '89417' and
SOPTYPE = '2') As I
GROUP BY ITEMNMBR, SFLINEITEM_I, DATE1, QTYTOFORECOST_I, DUEDATE, QUANTITY
ORDER BY ITEMNMBR ASC

"SQL Programmer" wrote:

> If someone could help, I'd appreciate it.
>
> I do not know my SQL programming all that well, so I'll try to explain.
>
> Below is a query (a view actually) that is to pull up data on inventory
> items. Now, I thought that this would work but, when I run it, I get a
> message saying that the SC020130.ITEMNMBR field is invalid because it is not
> contained in an "aggregate" or in a GROUP BY statement.
>
> I do have this field in the GROUP BY statement by way of the ITEMNMBR field.
>
> Is the problem with the "DISTINCT" clause? I need this clause in there to
> eliminate duplicates.
>
> When I double-click on the error message, it takes me to the very first line
> of the view. I have tried lots of things to eliminate this issue, and I get
> the same message (I have tried placing the table name in front of the field
> in all spots, I ahve tried removing the DISTINCT phrase, I have tried taking
> ITEMNMBR out of the GROUP BY statement)--but no success. Thanks, for any
> input! Below is the view.
>
>
>
> select ITEMNMBR as [ITEM], DATE1 As [FORECAST DATE], QTYTOFORECOST_I As
> [FORECAST QTY],
> [TOTAL QTY ON ORDER], DUEDATE As [DUE DATE], GETDATE() As [CURRENT DATE]
> FROM(
> select DISTINCT SC020130.ITEMNMBR, SC020230.SFLINEITEM_I, SC020230.DATE1,
> SC020230.QTYTOFORECOST_I,
> MRPW4060.QUANTITY, MRPW4060.DUEDATE, GETDATE() As [CURRENT DATE],
> CASE WHEN GETDATE()<MRPW4060.DUEDATE--and CASE WHEN GETDATE()>SC020230.DATE1
> THEN SUM(MRPW4060.QUANTITY)
> ELSE 0 END As [TOTAL QTY ON ORDER]
> from SC020230 As SC020230
> JOIN SC020130 As SC020130
> on SC020230.SFLINEITEM_I = SC020130.SFLINEITEM_I
> JOIN MRPW4060 As MRPW4060
> on SC020130.ITEMNMBR = MRPW4060.ITEMNMBR
> where SC020230.PLANNAME_I = 'MRP FG1' and SC020130.ITEMNMBR = '89417' and
> SOPTYPE = '2') As I
> GROUP BY ITEMNMBR, SFLINEITEM_I, DATE1, QTYTOFORECOST_I, DUEDATE, [TOTAL QTY
> ON ORDER]
> ORDER BY ITEMNMBR ASC