SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Group by Clause Error
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Villanuev
Constraint Violating Yak Guru

407 Posts

Posted - 11/16/2013 :  07:29:30  Show Profile  Reply with Quote
I have a query to generate a raw data but when running this query i got an error that i have an invalid field that not in a select group statement. kindly please help me to find this error. thank you.

error message

Msg 8120,Level 16,State 1, Line 4
Column'finphones.asuprodlineid'is invalid in the select list because it isnot contained in either an aggregatefunctionor the GROUPBY clause.


SELECT

		finphones.asuprodlineid  
		,finphones.itemid  
		,finphones.finishedfone AS finishedfone
		,ISNULL(MAX(itconsump.asuItemVerId),MAX(itbom.asuItemVerId))AS PartNo
		,ISNULL(MAX(itconsump.itemname),MAX(itbom.itemname))AS ItemName
		,ISNULL(CAST(consumptionTable.RECLAIMQTY ASNUMERIC),0)AS RECLAIMQTY  
FROM (
Select *
From
(
select prodList.ASUPRODLINEID, prodlist.itemid, bomlist.partQty as requiredQty, bomlist.partId as requiredPart from
(
	select * 
		from #PRODORDERS pt  
) prodList  
leftouterjoin
(
Select *
   
from	bomversion bv  
innerjoin
        bom on bom.dataareaid = bv.dataareaid and bom.bomid = bv.bomid   
where
        bv.dataareaid='tdk'
and bom.dataareaid='tdk'
AND bv.name ='Nornal'
and bom.bomqty > 0  
)bomList on bomList.phoneId = prodList.itemid   
)x --group by x.ASUPRODLINEID, x.itemid, x.requiredPart 
) bomTable 

FULLJOIN
(
SELECT
	 pt.asuprodlineid  
		,partNo = it.asuItemVerId
		,pt.itemid  
		,RECLAIMQTY =CASEWHEN (ivg.asuReclaim = 1)THEN pjb.bomconsump ELSE 0 END
FROM   prodjournalbom pjb 
INNERJOIN #PRODORDERS pt ON pt.prodid = pjb.prodid
INNERJOIN(SELECT DATAAREAID   
				,itemid  
				,inventtransid  
				-- ,sum(costamountposted) as costamountposted  
				-- ,sum(costamountadjustment) as costamountadjustment  
				,costamountposted
				,costamountadjustment
				FROM  dbo.inventtrans 
				WHERE dataareaid ='tdk') ivt--@dataArea  
				--GROUP BY DATAAREAID  
				--  ,inventtransid  
				--  ,itemid)ivt 
				ON pjb.DATAAREAID = ivt.DATAAREAID 
								and pjb.itemid = ivt.itemid 
								AND pjb.inventtransid = ivt.inventtransid  
INNERJOIN
    dbo.inventtable it ON it.dataareaid ='tdk'AND ivt.itemid = it.itemid  
INNERJOIN
    dbo.inventitemgroup ivg on ivg.dataareaid = it.dataareaid AND ivg.itemgroupid = it.itemgroupid  
WHERE
    pjb.dataareaid ='tdk'
--GROUP BY   
-- pt.asuprodlineid, pt.itemid, pjb.itemid, it.asuitemverid, it.itemgroupid, ivg.asureclaim  
)consumptionTable ON bomTable.requiredPart = consumptionTable.partNo 
					AND bomTable.itemID = consumptionTable.itemID
					and bomTable.ASUPRODLINEID = consumptionTable.ASUPRODLINEID
					--AND bomTable.MONTHDESCRIPTION=consumptionTable.MONTHDESCRIPTION
					
JOIN
(--select asuprodlineid, itemid,  COUNT(distinct pt.prodid) as finishedfone 
select asuprodlineid, itemid, pt.prodid as finishedfone
from #PRODORDERS pt  
--group by asuprodlineid, itemid        
) finphones on (finphones.ASUPRODLINEID = bomtable.ASUPRODLINEID
				AND finphones.itemid = bomtable.ItemID)
				OR(finphones.asuprodlineid = consumptionTable.asuprodlineid
					AND finphones.itemid = consumptionTable.itemid)
LEFTJOIN
      dbo.inventtable itbom ON itbom.dataareaid ='tdk'AND itbom.itemid = bomtable.requiredPart
LEFTJOIN
	  dbo.inventitemgroup ivtbom ON ivtbom.dataareaid = itbom.dataareaid AND ivtbom.itemgroupid = itbom.itemgroupid
LEFTJOIN
      dbo.inventtable itconsump ON itconsump.dataareaid ='tdk'AND itconsump.itemid = consumptionTable.PartNo
LEFTJOIN
	  dbo.inventitemgroup ivtconsump ON ivtconsump.dataareaid = itconsump.dataareaid AND ivtconsump.itemgroupid = itconsump.itemgroupid
	
--  GROUP BY   
-- finphones.asuprodlineid, finphones.itemid, finphones.finishedfone, itconsump.asuItemVerId, bomtable.requiredPart, bomTable.requiredQty  

--ORDER BY   
-- finphones.ASUPRODLINEID, finphones.ITEMID, itconsump.asuItemVerId

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 11/16/2013 :  10:15:14  Show Profile  Reply with Quote
You need to GROUP BY on the columns to be used directly in the SELECT list if you're applying aggregates over some of the other columns.
Uncomment your GROUP BY and it should work fine.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Villanuev
Constraint Violating Yak Guru

407 Posts

Posted - 11/16/2013 :  22:02:47  Show Profile  Reply with Quote
actually i dont need them to group by or aggregate that's why i place uncomment.
how can i put this finphones.asuprodlineid as not aggregate value in the select statement.

I think this is the portion where i got an error.

JOIN
(--select asuprodlineid, itemid,  COUNT(distinct pt.prodid) as finishedfone 
select asuprodlineid, itemid, pt.prodid as finishedfone
from #PRODORDERS pt  
--group by asuprodlineid, itemid        
) finphones on (finphones.ASUPRODLINEID = bomtable.ASUPRODLINEID
				AND finphones.itemid = bomtable.ItemID)
				OR(finphones.asuprodlineid = consumptionTable.asuprodlineid
					AND finphones.itemid = consumptionTable.itemid)

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 11/17/2013 :  11:47:22  Show Profile  Reply with Quote
As per this you need to include all the fields that you need to join with in GROUP BY

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Villanuev
Constraint Violating Yak Guru

407 Posts

Posted - 11/18/2013 :  20:21:49  Show Profile  Reply with Quote
Thanks Visakh. Just add another field to get the desired grouping of records.

Btw, I tried to join the result of CTE from other table but there are duplicate Prodid that's why I use row_number.
Why this row_number did not work? The result of RN is all 1. I need to get the latest REASON based on REQUESTEDDATETIME using PRODID as reference.

[code]
With CTE AS
(Select ????
)
SELECT arpl.REASON, arpt.REQUESTEDDATETIME, DAT.PRODID, dat.ASUPRODLINEID, dat.ITEMID, dat.PartNo, dat.finishedfone, dat.ItemName,
dat.REFERENCEBOMQTY, dat.requiredQty, dat.NEWPARTAVGCOST, dat.REFURBPARTAVGCOST,
CORESQTY=(CASE When dat.requiredQty>dat.Total Then dat.RequiredQty-dat.Total else 0 End), dat.NEWPARTSQTY, dat.RECLAIMQTY, dat.REFURBQTY, dat.TOTAL,
rn=(Select ROW_NUMBER() OVER (PARTITION BY dat.prodid ORDER BY arpt.REQUESTEDDATETIME DESC))
FROM CTE dat
Left Outer Join dbo.ASUREQUESTPARTTABLE arpt
ON arpt.PRODUCTIONREF=dat.PRODID and arpt.DATAAREAID='tcp'
Left Outer Join dbo.ASUREQUESTPARTLINE arpl
ON arpl.REQUESTID=arpt.REQUESTID --and arpl.ITEMVERID=dat.PartNo and arpl.DATAAREAID='tcp'
WHERE dat.PartNo='HTC1823'
Order by dat.PRODID[/cide]
Go to Top of Page

Villanuev
Constraint Violating Yak Guru

407 Posts

Posted - 11/18/2013 :  21:03:26  Show Profile  Reply with Quote
It's working. Wrong Syntax in row_number.

rn=(SELECT ROW_NUMBER() OVER (partition by PRODID order by REQUESTEDDATETIME desc)

Correct:
rn=ROW_NUMBER() OVER (partition by PRODID order by REQUESTEDDATETIME desc)
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 11/19/2013 :  01:51:08  Show Profile  Reply with Quote
Oh ok..I prefer putting aliases using AS

ROW_NUMBER() OVER (partition by PRODID order by REQUESTEDDATETIME desc) AS Rn,..

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000