sub quries - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

sub quries

Author

Topic

desikankannan
Posting Yak Master

152 Posts

Posted - 2010-01-28 : 09:29:57

i try to do subquriers to join three tables i need the report based
on the size wise herewith iam sending my quariers

select (select fldsizedesc from tblsizemaster where fldsizecode = a.fldsizecode) as sizedesc , sum(b.fldreciptqty) as Quantity from tblstockitem a, tblreciptprddetail b where b.flditemcode= a.flditemcode and
a.fldgrdcode ='G00001' AND fldseccode='C00001' AND fldcondcode='N00001' group by fldsizedesc

my error message
Msg 207, Level 16, State 1, Line 2
Invalid column name 'fldsizedesc'.

Desikankannan

Kristen
Test

22859 Posts

Posted - 2010-01-28 : 09:38:49

Change


group by fldsizedesc


GROUP BY (select fldsizedesc from tblsizemaster where fldsizecode = a.fldsizecode)

(which I hope will be permitted)

desikankannan
Posting Yak Master

152 Posts

Posted - 2010-01-28 : 09:47:38

Hi kristern,

i got error

select (select c.fldsizedesc from tblsizemaster c where c.fldsizecode = a.fldsizecode), sum(b.fldreciptqty) as Quantity from tblstockitem a, tblreciptprddetail b where b.flditemcode= a.flditemcode and
a.fldgrdcode ='G00001' AND fldseccode='C00001' AND fldcondcode='N00001' group by (select fldsizedesc from tblsizemaster where fldsizecode = a.fldsizecode)

Msg 144, Level 15, State 1, Line 1
Cannot use an aggregate or a subquery in an expression used for the group by list of a GROUP BY clause.

quote:
Originally posted by Kristen

Change

group by fldsizedesc

to

GROUP BY (select fldsizedesc from tblsizemaster where fldsizecode = a.fldsizecode)

(which I hope will be permitted)

Desikankannan

DP978
Constraint Violating Yak Guru

269 Posts

Posted - 2010-01-28 : 09:54:07

Move your subquery out of the statements part

select c.fldsizedesc as sizedesc , sum(b.fldreciptqty) as Quantity from tblstockitem a, tblreciptprddetail b, tblsizemaster c where b.flditemcode= a.flditemcode and a.fldsizecode = c.fldsizecode and a.fldgrdcode ='G00001' AND fldseccode='C00001' AND fldcondcode='N00001' group by c.fldsizedesc

Kristen
Test

22859 Posts

Posted - 2010-01-28 : 10:08:57

Probably this then, if I have got the inferences right:


select	fldsizedesc, 
	sum(b.fldreciptqty) as Quantity 
from	tblstockitem AS a
	JOIN tblreciptprddetail AS b 
		ON b.flditemcode= a.flditemcode
	JOIN tblsizemaster 
		ON fldsizecode = a.fldsizecode
WHERE	a.fldgrdcode ='G00001' 
	AND fldseccode='C00001' 
	AND fldcondcode='N00001' 
GROUP BY  fldsizedesc

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-01-28 : 10:13:51

or

select m.fldsizedesc as sizedesc ,sum(b.fldreciptqty) as Quantity
from tblsizemaster as m inner join tblstockitem a on m.fldsizecode = a.fldsizecode
inner join tblreciptprddetail b on b.flditemcode= a.flditemcode
where a.fldgrdcode ='G00001' AND fldseccode='C00001' AND fldcondcode='N00001'
group by fldsizedesc

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-01-28 : 10:14:23

quote:
Originally posted by Kristen

Change

group by fldsizedesc

to

GROUP BY (select fldsizedesc from tblsizemaster where fldsizecode = a.fldsizecode)

(which I hope will be permitted)

Suggesting new syntax?