Sub Query return more than one value

Question

I have create a table in which data will be inserted.

For data inserting, Have created a query that will insert the information from another table.

If the output is single value, It's updating in the new table.

But If it's multiple value, I am getting an error " Subquery return more than one value"

How to input multiple values using query in table?

Declare @BATCHNO as Nvarchar(10)

Declare @PRODNO as Nvarchar(10)

Declare @ISSUENO AS Nvarchar(10)

set @BATCHNO = (select T2.BatchNum  from OWOR T0 INNER JOIN table_P T1 ON T0.DocEntry = T1.BaseEntry
              INNER JOIN Table_I T2 ON T1.DocEntry = T2.BaseEntry AND T1.ObjType = T2.BaseType
              WHERE T0.DocEntry = '14')

Set @PRODNO = (select T1.BaseRef  from OWOR T0 INNER JOIN table_P T1 ON T0.DocEntry = T1.BaseEntry
              INNER JOIN Table_I T2 ON T1.DocEntry = T2.BaseEntry AND T1.ObjType = T2.BaseType
              WHERE T0.DocEntry = '14' )

Set @ISSUENO = (select T1.DocEntry AS 'ISSUE NUMBER' from OWOR T0 INNER JOIN table_P T1 ON T0.DocEntry = T1.BaseEntry
              INNER JOIN Table_I T2 ON T1.DocEntry = T2.BaseEntry AND T1.ObjType = T2.BaseType
              WHERE T0.DocEntry = '14')

Insert Into BATCHDETAIL (BATCHNO,PRODNO,ISSUENO) Values(@BATCHNO,@PRODNO,@ISSUENO)

[Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. FMS execution failed on field 'U_EA_LICENCE' with query name 'FMS_BATCHNO_UPDATE'

Answer 1

It looks like you are trying to insert multiple rows based on existing data. In which case you should use INSERT .. SELECT rather than INSERT...VALUES:

INSERT BatchDetail (BatchNo, ProdNo, IssueNo)
SELECT  T2.BatchNum, T1.BaseRef, T1.DocEntry
FROM    OWOR T0 
        INNER JOIN table_P T1 
            ON T0.DocEntry = T1.BaseEntry
        INNER JOIN Table_I T2 
            ON T1.DocEntry = T2.BaseEntry 
            AND T1.ObjType = T2.BaseType
WHERE   T0.DocEntry = '14';

If you did only want to insert one record, based on multiple records, you can use TOP 1, and provide an ORDER BY to ensure you get repeatable results:

INSERT BatchDetail (BatchNo, ProdNo, IssueNo)
SELECT  TOP 1 T2.BatchNum, T1.BaseRef, T1.DocEntry
FROM    OWOR T0 
        INNER JOIN table_P T1 
            ON T0.DocEntry = T1.BaseEntry
        INNER JOIN Table_I T2 
            ON T1.DocEntry = T2.BaseEntry 
            AND T1.ObjType = T2.BaseType
WHERE   T0.DocEntry = '14'
ORDER BY t2.BatchNum, T1.BaseRef, T1.DocEntry;

Finally, if you really do want 3 scalar variables, then you can still use SELECT and TOP 1, you just need to use the SELECT for variable assignment:

DECLARE @BATCHNO AS NVARCHAR(10)
        @PRODNO AS NVARCHAR(10)
        @ISSUENO AS NVARCHAR(10);

SELECT  TOP 1
        @BATCHNO = T2.BatchNum, 
        @PRODNO = T1.BaseRef, 
        @ISSUENO = T1.DocEntry
FROM    OWOR T0 
        INNER JOIN table_P T1 
            ON T0.DocEntry = T1.BaseEntry
        INNER JOIN Table_I T2 
            ON T1.DocEntry = T2.BaseEntry 
            AND T1.ObjType = T2.BaseType
WHERE   T0.DocEntry = '14'
ORDER BY t2.BatchNum, T1.BaseRef, T1.DocEntry;

---

ADENDUMEM

I would very much recommend staying away from any of the approaches that use 3 queries with TOP 1 and no order by. For example, if you had a table:

A   B   C
----------
1   3   2
2   1   3
3   2   1

You might expect this to assign @A, @B, and @C values that correspond to a single record (e.g. 1, 3, 2 or 2, 1, 3)

SET @A = (SELECT TOP 1 A FROM #T);
SET @B = (SELECT TOP 1 B FROM #T);
SET @C = (SELECT TOP 1 C FROM #T);

However, you are executing 3 different queries, and depending on what indexes are on the table(s) you could get 3 different query plans, and 3 different records used for each assignment.

This is fairly easily demonstrated:

-- CREATE TABLE AND FILL IT 
IF OBJECT_ID(N'tempdb..#T', 'U') IS NOT NULL DROP TABLE #T;

CREATE TABLE #T (A INT NOT NULL PRIMARY KEY, B INT NOT NULL, C INT NOT NULL);

CREATE NONCLUSTERED INDEX IX_T_B ON #T (B);
CREATE NONCLUSTERED INDEX IX_T_C ON #T (C);

INSERT #T (A, B, C) 
VALUES (1, 3, 2), (2, 1, 3), (3, 2, 1);

DECLARE @A INT, @B INT, @C INT;

SET @A = (SELECT TOP 1 A FROM #T);
SET @B = (SELECT TOP 1 B FROM #T);
SET @C = (SELECT TOP 1 C FROM #T);

SELECT A = @A, B = @B, C = @C;

For me this returns:

A   B   C
----------
3   1   1

Which does not correspond to any record in the original data. This might be absolutely fine for your needs, but it is definitely something to be aware of.

Example on db<>fiddle

Answer 2

You need to fix your subqueries so they return the exact single value to be inserted.

For example, if you don't really care and can insert whatever value that fulfills those conditions, then simply add a TOP 1 to your subqueries, so they will return only the first value available.

Declare @BATCHNO as Nvarchar(10)   
Declare @PRODNO as Nvarchar(10)
Declare @ISSUENO AS Nvarchar(10)

set @BATCHNO = (select TOP 1 T2.BatchNum  from OWOR T0 INNER JOIN table_P T1 ON T0.DocEntry = T1.BaseEntry
              INNER JOIN Table_I T2 ON T1.DocEntry = T2.BaseEntry AND T1.ObjType = T2.BaseType
              WHERE T0.DocEntry = '14')

Set @PRODNO = (select TOP 1 T1.BaseRef  from OWOR T0 INNER JOIN table_P T1 ON T0.DocEntry = T1.BaseEntry
              INNER JOIN Table_I T2 ON T1.DocEntry = T2.BaseEntry AND T1.ObjType = T2.BaseType
              WHERE T0.DocEntry = '14' )

Set @ISSUENO = (select TOP 1 T1.DocEntry AS 'ISSUE NUMBER' from OWOR T0 INNER JOIN table_P T1 ON T0.DocEntry = T1.BaseEntry
              INNER JOIN Table_I T2 ON T1.DocEntry = T2.BaseEntry AND T1.ObjType = T2.BaseType
              WHERE T0.DocEntry = '14')

Insert Into BATCHDETAIL (BATCHNO,PRODNO,ISSUENO) Values(@BATCHNO,@PRODNO,@ISSUENO)

By the way, you can simplify your script assigning the three variables on a single query :

Declare @BATCHNO as Nvarchar(10)    
Declare @PRODNO as Nvarchar(10)    
Declare @ISSUENO AS Nvarchar(10)

select TOP 1 @BATCHNO = T2.BatchNum,
             @PRODNO = T1.BaseRef,
             @ISSUENO = T1.DocEntry
from OWOR T0 
     INNER JOIN table_P T1 ON T0.DocEntry = T1.BaseEntry
     INNER JOIN Table_I T2 ON T1.DocEntry = T2.BaseEntry AND T1.ObjType = T2.BaseType
where T0.DocEntry = '14'   

Insert Into BATCHDETAIL (BATCHNO,PRODNO,ISSUENO) Values(@BATCHNO,@PRODNO,@ISSUENO)

Finally, if you want to insert all the values that fulfill your condition, then instead of taking just the first one with TOP 1, you can insert all of them feeding the insert with a select :

Insert Into BATCHDETAIL (BATCHNO,PRODNO,ISSUENO)     
       select T2.BatchNum, T1.BaseRef, T1.DocEntry
       from OWOR T0 
            INNER JOIN table_P T1 ON T0.DocEntry = T1.BaseEntry
            INNER JOIN Table_I T2 ON T1.DocEntry = T2.BaseEntry AND T1.ObjType = T2.BaseType
       where T0.DocEntry = '14'   

Answer 3

use this.

Declare @BATCHNO as Nvarchar(10)

Declare @PRODNO as Nvarchar(10)

Declare @ISSUENO AS Nvarchar(10)

set @BATCHNO = (select TOP 1 T2.BatchNum  from OWOR T0 INNER JOIN table_P T1 ON T0.DocEntry = T1.BaseEntry
              INNER JOIN Table_I T2 ON T1.DocEntry = T2.BaseEntry AND T1.ObjType = T2.BaseType
              WHERE T0.DocEntry = '14')

Set @PRODNO = (select TOP 1 T1.BaseRef  from OWOR T0 INNER JOIN table_P T1 ON T0.DocEntry = T1.BaseEntry
              INNER JOIN Table_I T2 ON T1.DocEntry = T2.BaseEntry AND T1.ObjType = T2.BaseType
              WHERE T0.DocEntry = '14' )

Set @ISSUENO = (select TOP 1 T1.DocEntry AS 'ISSUE NUMBER' from OWOR T0 INNER JOIN table_P T1 ON T0.DocEntry = T1.BaseEntry
              INNER JOIN Table_I T2 ON T1.DocEntry = T2.BaseEntry AND T1.ObjType = T2.BaseType
              WHERE T0.DocEntry = '14')

Insert Into BATCHDETAIL (BATCHNO,PRODNO,ISSUENO) Values(@BATCHNO,@PRODNO,@ISSUENO)