Subquery returned more than 1 value Error

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)

Subquery returned more than 1 value Error

Author

Topic

sara07
Starting Member

4 Posts

Posted - 2008-07-25 : 11:49:13

I need help with this query:

I need to concantate name of person who is eldest. This query works if there is age difference per account number but if age is same for example 18 for same account number for two people. The results are like this:

Name Age Account# Household Date Count
---------------------------------------------------------------------
Miss Ashley Gnam 12 197829 N 2008-03-21 2
Mr Alexander Gross 18 310755 N 2008-03-02 2
Mr Rudolph Mattheis-Brown 18 125942 N 2008-03-20 2
Mr William Mattheis-Brown 18 125942 N 2008-03-20 2
Mrs Linda Yardney 59 251590 N 2008-04-27 2
Ms Amanda Gross 18 310755 N 2008-03-02 2

I need to get for example for Account# 125942 to come up once and pick any Name since they are same age. But when one is elder than other I pick the eldest person. When i take out name from the query it pulls the data fine. - Just one age of 18 with account number. How can I get Distinct Age with the name and other recordset?

select distinct C.Age,
C.Account_Number, C.Primary_Household_Flag, C.MPC_Member_Expiration, T.Account_Number_Count,
(Select C.Customer_Title + ' ' + C.First_Name + ' ' + C.Last_Name from DMM_Customer_Cube C where C.Account_Number = T.Account_Number
) as Salutation_Line
--into #Tmp_No_Primary_Max
from DMM_Customer_Cube C
inner join #Tmp_Account_Number T on
T.Account_Number = C.Account_Number
left join #Tmp_Primary_Account P on
P.Account_Number = C.Account_Number
where P.Account_Number is null
and T.Account_Number_Count = 2
and MPC_Member_Expiration between '3/1/2008' and '5/30/2008'
and (C.Primary_Household_Flag = 'N' or C.Primary_Household_Flag = '' or C.Primary_Household_Flag is null)
--order by C.Account_Number asc
and Age = (Select max(age) from DMM_Customer_Cube CC
where CC.Account_Number = C.Account_Number
and MPC_Member_Expiration between '3/1/2008' and '5/30/2008')

Thank you please help

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-07-25 : 12:09:12

Well, The error is pretty self explanatory, so I assume you correlated sub-query is returning more than one result (highlighted in red):

SELECT DISTINCT
	C.Age,
	C.Account_Number,
	C.Primary_Household_Flag,
	C.MPC_Member_Expiration,
	T.Account_Number_Count,
	(
		SELECT
			C.Customer_Title + ' ' + C.First_Name + ' ' + C.Last_Name
		FROM
			DMM_Customer_Cube C
		WHERE
			C.Account_Number = T.Account_Number
	) AS Salutation_Line	--into #Tmp_No_Primary_Max
FROM
	DMM_Customer_Cube C
INNER JOIN 
	#Tmp_Account_Number T
	ON T.Account_Number = C.Account_Number
LEFT JOIN 
	#Tmp_Primary_Account P
	ON P.Account_Number = C.Account_Number
WHERE
	P.Account_Number IS NULL
	AND T.Account_Number_Count = 2
	AND MPC_Member_Expiration BETWEEN '3/1/2008' AND '5/30/2008'
	AND
	(
		C.Primary_Household_Flag = 'N'
		OR C.Primary_Household_Flag = ''
		OR C.Primary_Household_Flag IS NULL
	)
	--order by C.Account_Number asc
	AND Age =
	(
		SELECT
			MAX(age)
		FROM
			DMM_Customer_Cube CC
		WHERE
			CC.Account_Number = C.Account_Number
			AND MPC_Member_Expiration BETWEEN '3/1/2008' AND '5/30/2008'
	)

You could try adding a TOP 1 clause or something to solve the issue.

sara07
Starting Member

4 Posts

Posted - 2008-07-25 : 13:18:45

I know the problem is there- but TOP 1 clause DID not solve my problem. I guess what I really want is to distinct values and when I have name then I get multiple values.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-25 : 13:52:44

quote:
Originally posted by sara07

I know the problem is there- but TOP 1 clause DID not solve my problem. I guess what I really want is to distinct values and when I have name then I get multiple values.

You could replace the subquery with a join onto the table DMM_Customer_Cube to get the names and can take TOP1 or MIN() or MAX() if you want to retrieve a single value

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-07-25 : 14:18:00

I'm not sure I'm following you. Can you show some sampel data and expected output?

I assume this is not what you want:

SELECT DISTINCT
	C.Age,
	C.Account_Number,
	C.Primary_Household_Flag,
	C.MPC_Member_Expiration,
	T.Account_Number_Count,
	C.Customer_Title + ' ' + C.First_Name + ' ' + C.Last_Name AS Salutation_Line	
	--into #Tmp_No_Primary_Max
FROM
	DMM_Customer_Cube C
INNER JOIN 
	#Tmp_Account_Number T
	ON T.Account_Number = C.Account_Number
LEFT JOIN 
	#Tmp_Primary_Account P
	ON P.Account_Number = C.Account_Number
WHERE
	P.Account_Number IS NULL
	AND T.Account_Number_Count = 2
	AND MPC_Member_Expiration BETWEEN '3/1/2008' AND '5/30/2008'
	AND
	(
		C.Primary_Household_Flag = 'N'
		OR C.Primary_Household_Flag = ''
		OR C.Primary_Household_Flag IS NULL
	)
	--order by C.Account_Number asc
	AND Age =
	(
		SELECT
			MAX(age)
		FROM
			DMM_Customer_Cube CC
		WHERE
			CC.Account_Number = C.Account_Number
			AND MPC_Member_Expiration BETWEEN '3/1/2008' AND '5/30/2008'
	)

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-07-25 : 14:35:01

Lamprey,
this DISTINCT would not work for this because of Rudolph and William in Salutation_Line.

I have made a query in some art i have seen often in posts of visakh16.
Perhaps there are too many rows in the table and this query runs too slow - i don't know.

Maybe viskh16 takes a look and says: oooohhhhh no!

SELECT
Age,
Account_Number,
Primary_Household_Flag,
MPC_Member_Expiration,
Account_Number_Count,
Salutation_Line
FROM
(SELECT DISTINCT
row_number() over (partition by C.Account_Number,C.Age order by C.Account_Number,C.Age DESC) as rownum,
C.Age,
C.Account_Number,
C.Primary_Household_Flag,
C.MPC_Member_Expiration,
T.Account_Number_Count,
C.Customer_Title + ' ' + C.First_Name + ' ' + C.Last_Name AS Salutation_Line
--into #Tmp_No_Primary_Max
FROM
DMM_Customer_Cube C
INNER JOIN
#Tmp_Account_Number T
ON T.Account_Number = C.Account_Number
LEFT JOIN
#Tmp_Primary_Account P
ON P.Account_Number = C.Account_Number
WHERE
P.Account_Number IS NULL
AND T.Account_Number_Count = 2
AND MPC_Member_Expiration BETWEEN '3/1/2008' AND '5/30/2008'
AND
(
C.Primary_Household_Flag = 'N'
OR C.Primary_Household_Flag = ''
OR C.Primary_Household_Flag IS NULL
)
--order by C.Account_Number asc
AND Age =
(
SELECT
MAX(age)
FROM
DMM_Customer_Cube CC
WHERE
CC.Account_Number = C.Account_Number
AND MPC_Member_Expiration BETWEEN '3/1/2008' AND '5/30/2008'
)

) as t
WHERE rownum = 1

Webfred

There are 10 types of people in the world: Those who understand binary, and those who don't...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-25 : 14:45:27

quote:
Originally posted by webfred

Lamprey,
this DISTINCT would not work for this because of Rudolph and William in Salutation_Line.

I have made a query in some art i have seen often in posts of visakh16.
Perhaps there are too many rows in the table and this query runs too slow - i don't know.

Maybe viskh16 takes a look and says: oooohhhhh no!

SELECT
Age,
Account_Number,
Primary_Household_Flag,
MPC_Member_Expiration,
Account_Number_Count,
Salutation_Line
FROM
(SELECT DISTINCT
row_number() over (partition by C.Account_Number,C.Age order by C.Account_Number,C.Age DESC) as rownum,
C.Age,
C.Account_Number,
C.Primary_Household_Flag,
C.MPC_Member_Expiration,
T.Account_Number_Count,
C.Customer_Title + ' ' + C.First_Name + ' ' + C.Last_Name AS Salutation_Line
--into #Tmp_No_Primary_Max
FROM
DMM_Customer_Cube C
INNER JOIN
#Tmp_Account_Number T
ON T.Account_Number = C.Account_Number
LEFT JOIN
#Tmp_Primary_Account P
ON P.Account_Number = C.Account_Number
WHERE
P.Account_Number IS NULL
AND T.Account_Number_Count = 2
AND MPC_Member_Expiration BETWEEN '3/1/2008' AND '5/30/2008'
AND
(
C.Primary_Household_Flag = 'N'
OR C.Primary_Household_Flag = ''
OR C.Primary_Household_Flag IS NULL
)
--order by C.Account_Number asc
AND Age =
(
SELECT
MAX(age)
FROM
DMM_Customer_Cube CC
WHERE
CC.Account_Number = C.Account_Number
AND MPC_Member_Expiration BETWEEN '3/1/2008' AND '5/30/2008'
)

) as t
WHERE rownum = 1

Webfred

There are 10 types of people in the world: Those who understand binary, and those who don't...

good job

The inner query can be further simplified. you dont really need the last Age field check as an example if you're using the row_number() approach

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-07-25 : 14:49:52

good job
Thank you very very much!

The inner query can be further simplified. you dont really need the last Age field check as an example if you're using the row_number() approach
Thats right! I was a little blind

Greetings
Webfred

There are 10 types of people in the world: Those who understand binary, and those who don't...

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-07-25 : 15:48:47

quote:
Originally posted by webfred

Lamprey,
this DISTINCT would not work for this because of Rudolph and William in Salutation_Line.

Yup, that's why I said I didn't think that is what the OP wanted. :)

Your query may well work, but I still I'm being thick-headed and don't fully understand what desired output should be based on the sample data.

sara07
Starting Member

4 Posts

Posted - 2008-07-25 : 17:39:52

THank you very much for helping me!!
Yes this query that webfred made it work. I just never used the rownumber or partition before. Explaination on partition would be great. But it was working good. Thank you!

:)

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-07-25 : 18:08:24

youre welcome

There are 10 types of people in the world: Those who understand binary, and those who don't...

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-07-25 : 18:18:07

Ah yes the explaination...

partition says: begin the numbering again at grouping on column in partition.

Don't know if this may enough explaination...and then my poor english

Webfred

There are 10 types of people in the world: Those who understand binary, and those who don't...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-26 : 11:49:14

quote:
Originally posted by webfred

Ah yes the explaination...

partition says: begin the numbering again at grouping on column in partition.

Don't know if this may enough explaination...and then my poor english

Webfred

There are 10 types of people in the world: Those who understand binary, and those who don't...

to clarify partition will break data into groups based on fields specified (in this case C.Account_Number,C.Age) and then puts sequential number on each group based on order of fields specified on order by.since we are ordering by decreasing order of accountnumber,age and taking 1 st record we will get record with max age for each combination of fields C.Account_Number,C.Age.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources