asked on
TSQL Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
Hi I am getting the below error while I use the sub query but no error when I comment out the subquery and did a inner join. can anyone please explain why I get error in sub query and how to find out which data is causing that error ? many Thanks
Msg 512, Level 16, State 1, Line 7
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
SELECT
COMP_Details.ID AS ID
,COMP_Details.WELLBORE_TYPE_ON_COMPLETION
--,(SELECT DISTINCT WELLBORE_TYPE_ON_COMPLETION FROM OGA_DW_Staging.[WellMgr].[S_VG_Completio_Applicati_Details]
--WHERE WELLBORE_TYPE_ON_COMPLETION IN (SELECT DATA from [XViewMgr].[S_Xview_Env_Mapsets] WHERE MS_DOMAIN = 'WONS_WELLBORE_TYPE')) AS ANTIC_WLLBRE_TYPE_ON_CMPLTN
FROM OGA_DW_Staging.[WellMgr].[S_VG_Completio_Applicati_Details] AS COMP_Details
inner join [XViewMgr].[S_Xview_Env_Mapsets] as b
on COMP_Details.WELLBORE_TYPE_ON_COMPLETION = b.DATA
ASKER
ASKER
Id 2 might have GREEN
id 3 might have BLUE
TOP 1 will only bring ONE value
ASKER
posting it again as below.
Msg 512, Level 16, State 1, Line 7
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
What returns this command?
SELECT DISTINCT WELLBORE_TYPE_ON_COMPLETION
FROM OGA_DW_Staging.[WellMgr].[S_VG_Completio_Applicati_Details]
WHERE WELLBORE_TYPE_ON_COMPLETION
IN (SELECT DATA from [XViewMgr].[S_Xview_Env_Mapsets]
WHERE MS_DOMAIN = 'WONS_WELLBORE_TYPE'
SELECT
COMP_Details.ID AS ID
, COMP_Details.WELLBORE_TYPE_ON_COMPLETION
FROM OGA_DW_Staging.[WellMgr].[S_VG_Completio_Applicati_Details] AS COMP_Details
inner join [XViewMgr].[S_Xview_Env_Mapsets] as b
on COMP_Details.WELLBORE_TYPE_ON_COMPLETION = b.DATA
Correct?And now you would like to add info from the subquery into each row on the output. What should display this additional info? Why do you need the subquery for it if it is retrieved from the same table as the main query?
ASKER
Below is the query which gives me error
SELECT
COMP_Details.ID AS ID
,COMP_Details.WELLBORE_TYPE_ON_COMPLETION
,(SELECT DISTINCT WELLBORE_TYPE_ON_COMPLETION FROM OGA_DW_Staging.[WellMgr].[S_VG_Completio_Applicati_Details]
WHERE WELLBORE_TYPE_ON_COMPLETION IN (SELECT DATA from [XViewMgr].[S_Xview_Env_Mapsets] WHERE MS_DOMAIN = 'WONS_WELLBORE_TYPE')) AS ANTIC_WLLBRE_TYPE_ON_CMPLTN
FROM OGA_DW_Staging.[WellMgr].[S_VG_Completio_Applicati_Details] AS COMP_Details
ASKER
WHERE WELLBORE_TYPE_ON_COMPLETION gets values from [XViewMgr].[S_Xview_Env_Mapsets] table WHERE MS_DOMAIN = 'WONS_WELLBORE_TYPE')
It rather seems the subquery is not applied correctly. If you place the subquery into the list of output columns then the subquery should return max 1 row.
ASKER
WHERE WELLBORE_TYPE_ON_COMPLETION gets values from [XViewMgr].[S_Xview_Env_Mapsets] table WHERE MS_DOMAIN = 'WONS_WELLBORE_TYPE')
I am TESTING if the data is satisfying the above condition.
SELECT
COMP_Details.ID AS ID
, COMP_Details.WELLBORE_TYPE_ON_COMPLETION
FROM OGA_DW_Staging.[WellMgr].[S_VG_Completio_Applicati_Details] AS COMP_Details
INNER JOIN [XViewMgr].[S_Xview_Env_Mapsets] AS b
ON COMP_Details.WELLBORE_TYPE_ON_COMPLETION = b.DATA
WHERE COMP_Details.WELLBORE_TYPE_ON_COMPLETION
IN (SELECT DATA from [XViewMgr].[S_Xview_Env_Mapsets]
WHERE MS_DOMAIN = 'WONS_WELLBORE_TYPE')
Update: The JOIN here seems to be useless.
SELECT
COMP_Details.ID AS ID
, COMP_Details.WELLBORE_TYPE_ON_COMPLETION
FROM OGA_DW_Staging.[WellMgr].[S_VG_Completio_Applicati_Details] AS COMP_Details
INNER JOIN [XViewMgr].[S_Xview_Env_Mapsets] AS b
ON COMP_Details.WELLBORE_TYPE_ON_COMPLETION = b.DATA
WHERE b.MS_DOMAIN = 'WONS_WELLBORE_TYPE'
ASKER
SELECT
COMP_Details.ID AS ID
,COMP_Details.WELLBORE_TYPE_ON_COMPLETION
FROM OGA_DW_Staging.[WellMgr].[S_VG_Completio_Applicati_Details] AS COMP_Details
inner join [XViewMgr].[S_Xview_Env_Mapsets] as b
on COMP_Details.WELLBORE_TYPE_ON_COMPLETION = b.DATA
SELECT DATA from [XViewMgr].[S_Xview_Env_Mapsets]
WHERE MS_DOMAIN = 'WONS_WELLBORE_TYPE'
Or betterSELECT COUNT(*), COUNT(DISTINCT DATA) from [XViewMgr].[S_Xview_Env_Mapsets]
WHERE MS_DOMAIN = 'WONS_WELLBORE_TYPE'
If the COUNT results are equal then the JOIN in the query is OK.
ASKER
COMP_Details.ID AS ID
,COMP_Details.WELLBORE_TYPE_ON_COMPLETION
,(SELECT DISTINCT WELLBORE_TYPE_ON_COMPLETION FROM OGA_DW_Staging.[WellMgr].[S_VG_Completio_Applicati_Details] z
WHERE WELLBORE_TYPE_ON_COMPLETION IN (SELECT DATA from [XViewMgr].[S_Xview_Env_Mapsets] WHERE MS_DOMAIN = 'WONS_WELLBORE_TYPE')
AND COMP_Details.ID = z.ID )
AS ANTIC_WLLBRE_TYPE_ON_CMPLTN
FROM OGA_DW_Staging.[WellMgr].[S_VG_Completio_Applicati_Details] AS COMP_Details
the above worked like a CHARM. basically I forgot the JOIN condition INSIDE subquery AND COMP_Details.ID = z.ID
I may expect three columns on output where the 3rd column is either equal to the 2nd column or it contains NULL if MS_DOMAIN <> 'WONS_WELLBORE_TYPE'
ASKER
ASKER
try replacing DISCTINCT by TOP 1.