Send per email SELECT results with more than 1 row

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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources