Subquery related issue in SQL Server

Question

SELECT DISTINCT
    w.sourcekeyid1,
    i.instrumentdesc,
    i.serialnum,
    i.instrumentmodelid,
    i.instrumenttype,
    i.workareadepartmentid,
    i.testingdepartmentid,
    spi.scheduleplanid,
    spi.scheduleplanitemdesc,
    spi.schedulerule,
    v.testmethod,
    v1.specid,
    v2.certificationinterval,
    v3.grcperiod,
    (SELECT keyid1 + '|' + keyid2 
     FROM workitemitem 
     WHERE workitemid + '|' + workitemversionid = v.testmethod 
       AND sdcid = 'ParamList') parameterlist,
    p.displayunits,
    (SELECT s.limittypesequence 
     FROM specparamlimits s 
     WHERE s.paramlistid = ((SELECT keyid1 FROM workitemitem 
                             WHERE workitemid + '|' + workitemversionid = v.testmethod 
                               AND sdcid = 'ParamList'))) limitlevel
FROM
    workorder w 
LEFT JOIN  
    instrument i ON w.sourcekeyid1 = i.instrumentid 
LEFT JOIN
    sysuser u ON w.completedby = u.sysuserid
LEFT JOIN
    scheduleplanitem spi ON spi.linksdcid = 'Instrument'
                         AND spi.linkkeyid1 = i.instrumentid
LEFT OUTER JOIN 
    vw_testmethod v ON v.scheduleplanitemid = spi.scheduleplanitemid
LEFT OUTER JOIN
    vw_specid v1 ON v1.scheduleplanitemid = spi.scheduleplanitemid
LEFT OUTER JOIN
    vw_certificationinterval v2 ON v2.scheduleplanitemid = spi.scheduleplanitemid
LEFT OUTER JOIN
    vw_grcperiod v3 ON v3.scheduleplanitemid = spi.scheduleplanitemid,
paramlistitem p 
INNER JOIN 
    workitemitem w1 ON p.paramlistid = w1.keyid1
ORDER BY
    w.sourcekeyid1

I am using a view here. I want to use this column of the view to find other could in the tables , but I get an error

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

How can I modify this?

is it possible to use `IN` instead of `=` in this part of query `where s.paramlistid= ((select keyid1 ` ? — Koushik Roy, Oct 29 '20 at 05:27

score 0 · Answer 1 · answered Oct 29 '20 at 06:14

There are several issues here and you need to solve all of them even not all are causing errors right now.

First, in the following sub-query:

(
    SELECT keyid1 + '|' + keyid2 
    FROM workitemitem 
    WHERE workitemid + '|' + workitemversionid = v.testmethod 
         AND sdcid = 'ParamList'
) parameterlist

You are generating a column value here and the SQL Engine is expecting only one value to be returned. If the query above returns more then one value, you will get the error.

There are several approaches here depending on your business logics:

ensure the query returns only one value (leave the query as it is)

select only the first value (you need to order the values in a unique matter in order to ensure consistent results are generated each time):

 (
     SELECT TOP 1 keyid1 + '|' + keyid2 
     FROM workitemitem 
     WHERE workitemid + '|' + workitemversionid = v.testmethod 
          AND sdcid = 'ParamList'
     ORDER BY keyid1 + '|' + keyid2 
 ) parameterlist

use aggregation in order to ensure only one value is returned - again depending on your business logic you may decide to get the MAX or the MIN value:

(
    SELECT MAX(keyid1 + '|' + keyid2)
    FROM workitemitem 
    WHERE workitemid + '|' + workitemversionid = v.testmethod 
         AND sdcid = 'ParamList'
) parameterlist

and if you are using SQL Server 2017 or later you can use STRING_AGG to get a CSV list with the values:

(
    SELECT STRING_AGG (keyid1 + '|' + keyid2, ',')
    FROM workitemitem 
    WHERE workitemid + '|' + workitemversionid = v.testmethod 
         AND sdcid = 'ParamList'
) parameterlist

for earlier version you can create own SQL CLR aggregation function.

For the next query:

(
    SELECT s.limittypesequence 
    FROM specparamlimits s 
    WHERE s.paramlistid = 
    (
        (
            SELECT keyid1 
            FROM workitemitem 
            WHERE workitemid + '|' + workitemversionid = v.testmethod 
                AND sdcid = 'ParamList'
        )
    )
) limitlevel

The same stuff applies to the first outer part. In the WHERE part, you have the same options for its statement plus using a IN clause:

WHERE s.paramlistid IN 
(
        (
            SELECT keyid1 
            FROM workitemitem 
            WHERE workitemid + '|' + workitemversionid = v.testmethod 
                AND sdcid = 'ParamList'
        )
)

Again using a IN clause may lead to the outer query to return more values, so you need to them, too.

Subquery related issue in SQL Server

1 Answers1