Help in Select Query - 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

General SQL Server Forums

New to SQL Server Programming

Help in Select Query

Author

Topic

Sambasivam
Starting Member

36 Posts

Posted - 2008-12-26 : 13:40:05

Can anyone Please help me in this query. It work as separate but I need it together (like i tried below) to calculate. Please let me know. It says subquery returns more that one 1 value. I know it is wrong but still not able to get a solution.

Select
L.LocCode AS LocationCode
,L.LocName AS LocationName
,R.RolPlan AS Rank
,
(SELECT
SUM(FieldBal) AS BeginningBalance
FROM
tableHA HA
INNER JOIN Loc L ON L.Loc_Id = HA.LocId
INNER JOIN Rk R ON R.RId = HA.RId
WHERE BuId IN
(SELECT BId
FROM tableBU
WHERE BuCode IN ('xxx','yyy'))
AND
HA.LocId IN
(SELECT LId FROM Loc
WHERE LocCode IN ('AAA12','BBB11','CCC11','DDD12','AAA31'))
AND Fis_Mnth_Id =
(Select MIN(Fisc_Mnth_id) - 1 FROM Fis_Mth WHERE FY_Year = '2009')
AND R.used_pl = 1
GROUP BY LocCode
,L.LocName
,R.RolPlan
)
+SUM(Field1)
-SUM(Field2)
-SUM(Field3)
+SUM(Field3)
-SUM(Field4)
+SUM(Field5)
-SUM(Field6)
+SUM(Field7)
-SUM(Field8)
+SUM(Field9)
FROM tableHP HP
INNER JOIN Loc L ON HP.LocId = L.LocId
INNER JOIN Rk R ON R.RId = HP.RId
WHERE BuId IN
(SELECT Id
FROM Bu
WHERE BuCode IN ('US017','US313'))
AND
HP.LocId IN
(SELECT LId FROM Loc
WHERE LocCode IN ('AAA12','BBB11','CCC11','DDD12','AAA31'))
AND Fis_Mnth_Id BETWEEN
(Select MIN(Fisc_Mnth_id) FROM Fis_Mth WHERE FY_Year = '2009')
AND (Select MAX(Fisc_Mnth_id) FROM Fis_Mth WHERE FY_Year = '2009')
AND R.Is_Used_Pl = 1
GROUP BY L.LocCode
,L.LocName
,R.RolPlan

revdnrdy
Posting Yak Master

220 Posts

Posted - 2008-12-26 : 15:57:43

I think when your subquery returns more than one value it is telling you that you have returned duplicate rows of information as opposed to more than one value for a single row.

A subquery returns a boolean value of true or false btw.

r&r

Sambasivam
Starting Member

36 Posts

Posted - 2008-12-26 : 16:01:05

ty. I have rewritten the query in the way expected. Anyway thank you for everyone.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-27 : 11:59:51

you may be better off rewriting the above subqueries with join

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources