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

Question

Post reply

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

kstarkes

Old Hand

Points: 316
More actions
March 17, 2016 at 12:35 pm

#146030

The following Code is giving me the following error:
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.
What am I doing wrong?
Declare @QRY varchar(max)
Declare @policeid varchar(3)
SET @QRY =
(Select Distinct PL.Permit_number,p.entered_date,PL.hundred_block,(select case When STREETCL_ARC.PRE_DIR IS null then STREETCL_ARC.ST_NAME+' '+streetcl_arc.ST_TYPE
else STREETCL_ARC.PRE_DIR+' '+STREETCL_ARC.ST_NAME+' '+streetcl_arc.ST_TYPE
end) AS 'STREET', S.status_code_description
FROM tblParty_Location PL INNER JOIN
tblParty_Districts D ON PL.permit_number = D.permit_number INNER JOIN
tblParty P ON PL.party_id = P.party_id INNER JOIN
tblStatusCodes S ON PL.status_code_id = S.status_code_id Inner Join
STREETCL_ARC ON PL.street_code = STREETCL_ARC.ST_CODE
WHERE (d.district = @policeid) AND (D.boundary_type_id = 4) AND (S.status_code_id =3)) --OR (S.status_code_id =1) OR (S.status_code_id =7))
EXEC sp_executesql @QRY

Viewing 3 posts - 1 through 2 (of 2 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

SET @QRY = requires passing a single column value or string value to the variable.

SELECT @DaTE = MAX(CreatedDate) is a decent example

your query says SET = (SELECT PL.Permit_number,

p.entered_date,

PL.hundred_block,

(SELECT CASE

WHEN STREETCL_ARC.PRE_DIR IS NULL THEN STREETCL_ARC.ST_NAME + ' '

+ streetcl_arc.ST_TYPE

ELSE STREETCL_ARC.PRE_DIR + ' '

+ STREETCL_ARC.ST_NAME + ' '

+ streetcl_arc.ST_TYPE

END) AS 'STREET',

S.status_code_description

..that's not allowed...you are assigning ~10 columns of values

looking at it, is really a syntax issue.

now if the whole query were in single quotes, because you are building the command, and you take the trouble to escape the inner single quotes,then you are probably fine, like this:

DECLARE @QRY VARCHAR(max)

DECLARE @policeid VARCHAR(3)

SET @QRY ='

SELECT DISTINCT PL.Permit_number,

p.entered_date,

PL.hundred_block,

(SELECT CASE

WHEN STREETCL_ARC.PRE_DIR IS NULL THEN STREETCL_ARC.ST_NAME + '' ''

+ streetcl_arc.ST_TYPE

ELSE STREETCL_ARC.PRE_DIR + '' ''

+ STREETCL_ARC.ST_NAME + '' ''

+ streetcl_arc.ST_TYPE

END) AS ''STREET'',

S.status_code_description

FROM tblParty_Location PL

INNER JOIN tblParty_Districts D

ON PL.permit_number = D.permit_number

INNER JOIN tblParty P

ON PL.party_id = P.party_id

INNER JOIN tblStatusCodes S

ON PL.status_code_id = S.status_code_id

INNER JOIN STREETCL_ARC

ON PL.street_code = STREETCL_ARC.ST_CODE

WHERE ( d.district = @policeid )

AND ( D.boundary_type_id = 4 )

AND ( S.status_code_id = 3 ) --OR (S.status_code_id =1) OR (S.status_code_id =7))'

EXEC sp_executesql @QRY

nothing you are doing there requires dynamic SQL though, so i wold get rid of the @qry , and just assign a value to @policeid and you should run the query itself.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

FridayNightGiant SSCertifiable Points: 7151 More actions · Answer 2

Looks like you lost some information when pasting the sql. You don't have any '' around the statement you are trying to assign to @QRY and looks as if there is a section of the sql statement missing.