Subquery returned more than 1 value

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

Subquery returned more than 1 value

Author

Topic

burzum
Yak Posting Veteran

55 Posts

Posted - 2012-07-23 : 15:57:26

Hi,

I wrote this sql script to send out an email, however when I run this I get the following error:

quote:
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

I've done some research and found out that I was using '=' sign so I changed it to 'IN'. It works. If I run two queries separately, it works.

However, when I combine them, I get the same result again. And I do need to return more than 1 value.

For example, if I just run this script:

(select Full_Link from [Billing].[dbo].[vw_BillingEmailLink] where [Company Name] in (select [Company Name] from  [Billing].[dbo].[vw_BillingEmailLink] where  [Company Name] in (select [Company Name] from  [Billing].[dbo].[vw_BillingEmailLink])))

I will get 3 full path, since I only have 3 path in my db.

If I run this:

(select emails from [Billing].[dbo].[vw_BillingEmailLink] where [Company Name] in (select [Company Name] from  [Billing].[dbo].[vw_BillingEmailLink] where  [Company Name] in (select [Company Name] from  [Billing].[dbo].[vw_BillingEmailLink])))

I will get 3 email addresses, as I'm suppose to.

But when I try to run this:

--declaring path of attachement;
	declare @attachment nvarchar(Max);
	set @attachment = (select Full_Link from [Billing].[dbo].[vw_BillingEmailLink] where [Company Name] in (select [Company Name] from  [Billing].[dbo].[vw_BillingEmailLink] where  [Company Name] in (select [Company Name] from  [Billing].[dbo].[vw_BillingEmailLink])));

--getting emails of clients;
	declare @recipients nvarchar (Max);
	set @recipients = (select emails from [Billing].[dbo].[vw_BillingEmailLink] where [Company Name] in (select [Company Name] from  [Billing].[dbo].[vw_BillingEmailLink] where  [Company Name] in (select [Company Name] from  [Billing].[dbo].[vw_BillingEmailLink])));


--sending email to clients
EXEC msdb.dbo.sp_send_dbmail
    @profile_name = '202',
    @recipients = @recipients,
    @body = 'Your detailed call report has been attached as .pdf file',
    @importance ='High', 
    @sensitivity ='Personal',
    @subject = 'Detailed Call Report',
    @file_attachments = @attachment;

I get the more than 1 value error.

Any ideas on how I can fix this?
Thank you.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-23 : 16:38:50

the error is because you cant store a resultset (group of rows) in a variable

you just need this


declare @attachment nvarchar(Max);
	set @attachment = stuff((select ';' + Full_Link from [Billing].[dbo].[vw_BillingEmailLink] for xml path('')),1,1,'')

--getting emails of clients;
	declare @recipients nvarchar (Max);
	set @recipients = stuff((select ';' + emails from [Billing].[dbo].[vw_BillingEmailLink] for xml path('')),1,1,'')


--sending email to clients
EXEC msdb.dbo.sp_send_dbmail
    @profile_name = '202',
    @recipients = @recipients,
    @body = 'Your detailed call report has been attached as .pdf file',
    @importance ='High', 
    @sensitivity ='Personal',
    @subject = 'Detailed Call Report',
    @file_attachments = @attachment;

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

burzum
Yak Posting Veteran

55 Posts

Posted - 2012-07-23 : 17:06:34

Thank you for your reply.

When I executed the following code, I get this error:

quote:
Attachment file ...\detailcallreport\convertedpdfTestDetailedCallReport_201206.pdf is invalid.

The link should be like this:

quote:
...\detailcallreport\convertedpdf\Test_DetailedCallReport_201206.pdf

That link is valid. I didn't know how I could add slash and _ score to that query.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-23 : 17:20:28

use stuff.

http://msdn.microsoft.com/en-us/library/ms188043.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

burzum
Yak Posting Veteran

55 Posts

Posted - 2012-07-23 : 17:22:13

Sorry, it was my mistake. I fixed that.

There's one problem I'm running into right now.

When it sends out an email, for instance there's two fields in the database right now. Two email addresses. Each email address has it's own path. But when I execute that query, I recieve both pdf files, I should be only recieving one pdf file.

If you want, I can give you more detail examples.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-23 : 20:56:21

for that you need to have two sp_send_dbmail calls. you cant wrap them inside a single call. So call it twice each time passing correct email address and path combination.
Or another way is to do it inside a loop to get email and corresponding path each time and call sp_send_dbmail using them

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

burzum
Yak Posting Veteran

55 Posts

Posted - 2012-07-24 : 11:05:07

Could you please give me an example or any link for a loop?

Because right now I'm having two records, but it's a test table. When I switch to real database, I will have more than 100 records. I wouldn't want to write more sp_send_dbmail...

Thank you.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-24 : 11:16:36

you can use cursor or a while loop

see an exmaple using cursor below

http://www.kodyaz.com/articles/t-sql-cursor-example-code.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

burzum
Yak Posting Veteran

55 Posts

Posted - 2012-07-24 : 14:08:13

I want to use while loop.

So I declare


Declare @companyinput varchar (50)
set @companyinput = (SELECT [Company Name] FROM [Billing].[dbo].[vw_BillingEmailLink])


while (@companyinput = @companyinput)

begin 

SET @companyname  = (SELECT [Company Name] FROM [Billing].[dbo].[vw_BillingEmailLink] WHERE [Company Name] = @companyinput);
end

I tried a different approach. I have two stored procedures. One is getting Name and passing to other stored procedure which does the sending email.

In that first stored procedure I have my while loop. However, I get that message with more than 1 returned.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-24 : 14:15:41

quote:
Originally posted by burzum

I want to use while loop.

So I declare

Declare @companyinput varchar (50)
set @companyinput = (SELECT [Company Name] FROM [Billing].[dbo].[vw_BillingEmailLink])

while (@companyinput = @companyinput)

begin

SET @companyname = (SELECT [Company Name] FROM [Billing].[dbo].[vw_BillingEmailLink] WHERE [Company Name] = @companyinput);
end

I tried a different approach. I have two stored procedures. One is getting Name and passing to other stored procedure which does the sending email.

In that first stored procedure I have my while loop. However, I get that message with more than 1 returned.

you're again doing same mistake
you cant store entire resultset in a variable

you need to modify statements like


Declare @companyinput varchar (50)
SELECT  @companyinput = MIN([Company Name]) FROM [Billing].[dbo].[vw_BillingEmailLink]


while (@companyinput IS NOT NULL)

begin 
... other logic here


SELECT  @companyinput = MIN([Company Name]) 
FROM [Billing].[dbo].[vw_BillingEmailLink]
WHERE [Company Name]  > @companyinput
end

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

burzum
Yak Posting Veteran

55 Posts

Posted - 2012-07-24 : 14:33:36

What do you mean by other logic?

So shouldn't this technically be my logic?

SELECT  @companyinput = MIN([Company Name]) 
FROM [Billing].[dbo].[vw_BillingEmailLink]
WHERE [Company Name]  > @companyinput
end

Because all I'm trying to do is to pass the name to that SELECT statement. And then this procedure will send it to other procedure.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-24 : 14:41:16

quote:
Originally posted by burzum

What do you mean by other logic?

So shouldn't this technically be my logic?

SELECT @companyinput = MIN([Company Name])
FROM [Billing].[dbo].[vw_BillingEmailLink]
WHERE [Company Name] > @companyinput
end

Because all I'm trying to do is to pass the name to that SELECT statement. And then this procedure will send it to other procedure.

thats logic for looping

other logic means whatever you want to do inside loop
send mails, populate data or something else.... i dont know what all you're trying to implement inside loop that why i gave a stub for loop for you to work on further

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

burzum
Yak Posting Veteran

55 Posts

Posted - 2012-07-24 : 14:46:30

visakh16,

Thank you for your time. I really appreciate it. It's just I can't figure this thing out for the past couple days. Seems pretty easy, but in reality not.

What I was originally trying to do is send emails. The code you provided me yesterday, did work, however it send all of the attachement to all emails. You suggested using loop. I thought having it into different stored procedures would make it little be easier. I would pass the name with the loop in the first stored procedure and then second one will send email...

Hopefully, I'm not confusing.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-24 : 14:49:11

no need of separate procedure. As i understand what you need is to get required attachment,recipient detail values inside loop in two variables and then call sp_send_dbmail to send the mail with attachment to required recipient

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

burzum
Yak Posting Veteran

55 Posts

Posted - 2012-07-24 : 14:52:33

Exactly. That's what I need to do. I need to do something like for loop to send it to specific person, however I'm not good at sql, and after some search I found out that I can use while loop. Passing single ClientName was suppose to fix my issue. Each row has unique Name...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-24 : 15:09:30

i've already given you stub to work on. once you add part to get attachment,recipient names and sp_send_dbmail you are good to go with it

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources