Click here to Skip to main content
15,116,815 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
Hi i have querry statement in my ETL to insert data in my table but i got this error :

"The subquery return multiple values. This is not allowed when the subquery follows =,! =, <, <=,>,> = Or when it is used as an expression."

Please help !!

This is the querry statement :

INSERT INTO [CRM_GAT].[dbo].[Table_Audit_Sinistres]([Libelle_Etape],[time],[Count_Input],[NumSinistre],[Count_Output])
  VALUES ('FIN_TR_Sinistre',GEtDATE()
  ,(SELECT COUNT(*)FROM [CRM_GAT].[dbo].[Sinistre] WHERE ([CodeValidation] is null or [CodeValidation]=0) and [DateOperation] between DATEADD(day,-10, GETDATE()) and GETDATE()),
  (SELECT  [NumSinistre]FROM [CRM_GAT].[dbo].[Sinistre] where [NumContrat]  in(select [NumContrat] from [CRM_GAT].[dbo].[Contrats] where [CodeProduit]!=7940  and [DateOperation] between DATEADD(day,-10, GETDATE()) and GETDATE())
 and [DateOperation] between DATEADD(day,-10, GETDATE()) and GETDATE() and ([CodeValidation] is null or [CodeValidation]=0 ))
	  ,(SELECT COUNT(*) FROM [CRMGAT].[dbo].[Sinistre] WHERE [CtrCodeSource]=1 and [DateOperation] between DATEADD(day,-10, GETDATE()) and GETDATE()))


What I have tried:

This is querry causing problems :

SELECT [NumSinistre]FROM [CRM_GAT].[dbo].[Sinistre] where [NumContrat] in(select [NumContrat] from [CRM_GAT].[dbo].[Contrats] where [CodeProduit]!=7940 and [DateOperation] between DATEADD(day,-10, GETDATE()) and GETDATE())
and [DateOperation] between DATEADD(day,-10, GETDATE()) and GETDATE() and ([CodeValidation] is null or [CodeValidation]=0)
Posted
Updated 26-Apr-21 0:00am

1 solution

Read the error message: it's pretty clear:
The subquery return multiple values. This is not allowed when the subquery follows =,! =, <, <=,>,> = Or when it is used as an expression.

Your inner SELECT returns more than one value, which isn't allowed as part of an INSERT value:
SQL
INSERT INTO MyTable (C1, C2) 
VALUES 
   (SELECT COUNT(*) FROM MyOtherTable),
   (SELECT X FROM YetAnotherTable WHERE A = B),
Because the second select returns multiple values, it can't create a single row.

You need an INSERT INTO SELECT statement instead: INSERT INTO SELECT statement overview and examples[^]
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900