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

  • 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

  • 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!

  • 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.

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

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