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
 Send per email SELECT results with more than 1 row

Author  Topic 

Pelusa7288
Starting Member

1 Post

Posted - 2014-11-20 : 16:26:29
Hi all,

I am looking for help as I am trying to solve this problem "Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression"

Hopefully someone can help me. It is about a Stored Procedure, which not only should output the number of opportunities with status "Pending" or "Accepted" but also the name of the opportunities with these statuses. Following points:


1. The SELECT that outputs the names of the opportunities may in some cases return more than one value. I created then the CURSOR CUR_TEST.

2. The code should return the names of Opportunities, namely after "The opportunities are:" and these must be sent to the recipients of an email. How can I do this, so the Names of the Opportunities and the rest of the email can be sent by email?. I mean, the concatenation with SET or SELECT doesn't work. I get as email only the output of the last row of the SELECT with cursor CUR_TEST.

3. I add an example how the output should be.

The following is the code:

BEGIN  

declare
@V_USER VARCHAR(20),
@V_NAME VARCHAR(100),
@V_QUANTITY INT,
@V_EMAIL VARCHAR(60),
@V_BODY VARCHAR(MAX),
@V_QUANTITY_ACTIVE int,

--I added these lines
----------------------
declare @V_OP_NAME VARCHAR(100)


DECLARE CUR_TEST CURSOR FOR
SELECT T1.NAME FROM dbo.S_OPTY T1
INNER JOIN dbo.S_OPTY_POSTN T2 ON T1.PR_POSTN_ID = T2.POSITION_ID AND T1.ROW_ID = T2.OPTY_ID
INNER JOIN dbo.S_OPTY_BU T3 ON T3.BU_ID = '1-O5H' AND T1.ROW_ID = T3.OPTY_ID
INNER JOIN dbo.S_PARTY T4 ON T2.POSITION_ID = T4.ROW_ID
INNER JOIN dbo.S_PARTY T5 ON T3.BU_ID = T5.ROW_ID
LEFT OUTER JOIN dbo.S_USER T6 ON T1.CREATED_BY = T6.PAR_ROW_ID
LEFT OUTER JOIN dbo.S_SYS_KEYMAP T7 ON T1.ROW_ID = T7.SIEBEL_SYS_KEY
LEFT OUTER JOIN dbo.S_OPTY T8 ON T1.PAR_OPTY_ID = T8.ROW_ID
LEFT OUTER JOIN dbo.S_ADDR_ORG T9 ON T1.PR_OU_ADDR_ID = T9.ROW_ID
LEFT OUTER JOIN dbo.S_ORG_EXT T10 ON T1.PR_DEPT_OU_ID = T10.PAR_ROW_ID
LEFT OUTER JOIN dbo.S_POSTN T11 ON T1.PR_POSTN_ID = T11.PAR_ROW_ID
LEFT OUTER JOIN dbo.S_PRI_LST T12 ON T10.CURR_PRI_LST_ID = T12.ROW_ID
LEFT OUTER JOIN dbo.S_USER T13 ON T11.PR_EMP_ID = T13.PAR_ROW_ID
WHERE T1.STATUS_CD in ( 'Pending' , 'Accepted')
--AND T13.LOGIN = @V_USER
AND (T13.LOGIN in ('MyUSER') )
--OR (T6.LOGIN in (@V_USER)) )
AND T1.SUM_EFFECTIVE_DT <= GETDATE()
AND T1.SUM_EFFECTIVE_DT >= '20090601'

OPEN CUR_TEST
FETCH NEXT FROM CUR_TEST INTO @V_OP_NAME
WHILE (@@FETCH_STATUS = 0)
BEGIN
PRINT @V_OP_NAME
FETCH NEXT FROM CUR_TEST INTO @V_OP_NAME

END


CLOSE CUR_TEST
DEALLOCATE CUR_TEST



SET @V_QUANTITY = 0
SET @V_QUANTITY_ACTIVE = 0
SET @V_QUANTITY = ( SELECT COUNT(*) FROM dbo.S_OPTY T1
INNER JOIN dbo.S_OPTY_POSTN T2 ON T1.PR_POSTN_ID = T2.POSITION_ID AND T1.ROW_ID = T2.OPTY_ID
INNER JOIN dbo.S_OPTY_BU T3 ON T3.BU_ID = '1-O5H' AND T1.ROW_ID = T3.OPTY_ID
INNER JOIN dbo.S_PARTY T4 ON T2.POSITION_ID = T4.ROW_ID
INNER JOIN dbo.S_PARTY T5 ON T3.BU_ID = T5.ROW_ID
LEFT OUTER JOIN dbo.S_USER T6 ON T1.CREATED_BY = T6.PAR_ROW_ID
LEFT OUTER JOIN dbo.S_SYS_KEYMAP T7 ON T1.ROW_ID = T7.SIEBEL_SYS_KEY
LEFT OUTER JOIN dbo.S_OPTY T8 ON T1.PAR_OPTY_ID = T8.ROW_ID
LEFT OUTER JOIN dbo.S_ADDR_ORG T9 ON T1.PR_OU_ADDR_ID = T9.ROW_ID
LEFT OUTER JOIN dbo.S_ORG_EXT T10 ON T1.PR_DEPT_OU_ID = T10.PAR_ROW_ID
LEFT OUTER JOIN dbo.S_POSTN T11 ON T1.PR_POSTN_ID = T11.PAR_ROW_ID
LEFT OUTER JOIN dbo.S_PRI_LST T12 ON T10.CURR_PRI_LST_ID = T12.ROW_ID
LEFT OUTER JOIN dbo.S_USER T13 ON T11.PR_EMP_ID = T13.PAR_ROW_ID
WHERE T1.STATUS_CD in ( 'Pending' , 'Accepted')
--AND T13.LOGIN = @V_USER
AND (T13.LOGIN in ('MyUser') )
--OR (T6.LOGIN in (@V_USER)) )
AND T1.SUM_EFFECTIVE_DT <= GETDATE()
AND T1.SUM_EFFECTIVE_DT >= '20090601' )


SET @V_QUANTITY_ACTIVE = ( SELECT COUNT(*)
FROM dbo.S_EVT_ACT T1
WHERE ( (T1.APPT_REPT_REPL_CD IS NULL)
AND (T1.TEMPLATE_FLG != 'Y' AND T1.TEMPLATE_FLG != 'P' OR T1.TEMPLATE_FLG IS NULL)
AND (T1.PRIV_FLG = 'N' OR T1.PRIV_FLG IS NULL OR T1.OWNER_PER_ID = '1-ONZ')
)
AND T1.TODO_PLAN_START_DT <= getdate()
AND T1.TODO_PLAN_START_DT >= '20090501'
AND T1.EVT_STAT_CD IN ('Pending','Programmed')
AND T1.TODO_CD != 'Alert'
AND T1.TODO_CD != 'Task'
AND T1.OPTY_ID is not null
AND T1.NAME NOT LIKE '%has invited yo to an Opportunity%'
AND T1.OWNER_LOGIN = 'MyUser' )


if @V_QUANTITY + @V_QUANTITY_ACTIVE > 0
begin

SELECT @V_BODY = '<BODY style="color:navy; font-size:10pt; font-family:verdana">'
SELECT @V_BODY = @V_BODY + 'Dear ' + 'MyName' + ',<br>'
SELECT @V_BODY = @V_BODY + 'You have ' IF @V_QUANTITY > 0

BEGIN SELECT @V_BODY = @V_BODY + cast(@V_QUANTITY as varchar) + ' Opportunities with status Pending'

SELECT @V_BODY = @V_BODY + '<br>The opportunities are:<br>'
SELECT @V_BODY = @V_BODY + '<BR>'
SELECT @V_BODY = @V_BODY + @V_OP_NAME
SELECT @V_BODY = @V_BODY + '<BR>'

END
SELECT @V_BODY = @V_BODY + '<BR>'
'</BODY>'

exec msdb..sp_send_dbmail @profile_name = 'SiebelAdministrator',
@recipients = 'my_email_address',
@subject = 'SIEBEL / Email',
@body = @V_BODY,
@body_format = 'HTML',
@exclude_query_output = 1
end END



An example how the Output in the email body should be:

Dear MyName,
You have 47 Opportunities with status Pending.
The Opportunities are:

Name of Opportunity 1
Name of Opportunity 2
Name of Opportunity 3
...

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-21 : 09:20:02
At which point is this failing? Maybe you can insert some PRINT statements to help you see how far it gets before the error.

One thing I would do: Replace statements like "SELECT @V_BODY = ..." with "SET @V_BODY = ..." since you are not querying a table.
Go to Top of Page
   

- Advertisement -