Only one expression can be specified in the select list when the subquery is not introduced with EXISTS

Question

Only one expression can be specified in the select list when the subquery is not introduced with EXISTS

mamadou-lamarana.bah

SSC Veteran

Points: 215
More actions
January 5, 2018 at 2:59 am

#409921

hello, i'm working on sql server and i'm writting a procedure to send an email.
The following code is giving me an error when I run it, i've seen some examples but I don't know how to solve it by keeping my "Union all " command.
Thanks for your help!.
SELECT TOP 1 td = dql.table_name,'',
td = dql.column_name, '',
td = 'log_message'
FROM YCEE_LOG.dbo.data_quality_log as dql
WHERE dql.validation_rule_code = @validation_rule_code and dql.exec_id = @exec_id
UNION ALL
SELECT td = business_key, '',
td = column_value, '',
td = error_message
FROM YCEE_LOG.dbo.data_quality_log
WHERE validation_rule_code = @validation_rule_code and exec_id = @exec_id) AS NVARCHAR(MAX) )

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Please post the entire query.
The code you posted has no subqueries, and hence cannot throw that error. It does however fail to parse with

Msg 102, Level 15, State 1, Line 13
Incorrect syntax near ')'.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

mamadou-lamarana.bah SSC Veteran Points: 215 More actions · Answer 2

okay, the query is in my procedure, when i run it out of my procedure, i have no error, but inside of my procedure, i get that error.
this is the entire query:

SET @tableHTML =
        N'<h1>Data quality:'+ @validation_rule_code +'</h1>'+
        N'<table border = "1">' +
        N'<tr><th>Table name</th> <th> Column name </th> <th>Log message </th></tr>'+
        CAST ( ( SELECT TOP 1 td = dql.table_name,'',
                            td = dql.column_name, '',
                            td = 'log_message'
                    FROM YCEE_LOG.dbo.data_quality_log as dql
                    WHERE dql.validation_rule_code = @validation_rule_code and dql.exec_id = @exec_id
                    UNION ALL
                    SELECT td = business_key, '',
                            td = column_value, '',
                            td = error_message
                    FROM YCEE_LOG.dbo.data_quality_log
                    WHERE validation_rule_code = @validation_rule_code and exec_id = @exec_id) AS NVARCHAR(MAX) )+ N'</table>';

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Ok, the problem is that CASE (SELECT ...) must only return a single column and a single row, because it's part of a scalar expression.
Multiple columns is easy, you can just concatenate them. For the multiple rows, use one of these to turn the rows into a single string: https://www.red-gate.com/simple-talk/sql/t-sql-programming/concatenating-row-values-in-transact-sql/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

mamadou-lamarana.bah SSC Veteran Points: 215 More actions · Answer 4

GilaMonster - Friday, January 5, 2018 3:21 AM
Ok, the problem is that CASE (SELECT ...) must only return a single column and a single row, because it's part of a scalar expression.
Multiple columns is easy, you can just concatenate them. For the multiple rows, use one of these to turn the rows into a single string: https://www.red-gate.com/simple-talk/sql/t-sql-programming/concatenating-row-values-in-transact-sql/

okay, thanks, I'm gonna try it.

I've solve it 🙂