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
 General SQL Server Forums
 New to SQL Server Programming
 Help in Select Query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Sambasivam
Starting Member

USA
36 Posts

Posted - 12/26/2008 :  13:40:05  Show Profile  Send Sambasivam a Yahoo! Message  Reply with Quote
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

USA
220 Posts

Posted - 12/26/2008 :  15:57:43  Show Profile  Reply with Quote
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

Go to Top of Page

Sambasivam
Starting Member

USA
36 Posts

Posted - 12/26/2008 :  16:01:05  Show Profile  Send Sambasivam a Yahoo! Message  Reply with Quote
ty. I have rewritten the query in the way expected. Anyway thank you for everyone.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 12/27/2008 :  11:59:51  Show Profile  Reply with Quote
you may be better off rewriting the above subqueries with join
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