Link to home
Start Free TrialLog in
Avatar of gvamsimba
gvamsimbaFlag for United Kingdom of Great Britain and Northern Ireland

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

Avatar of Éric Moreau
Éric Moreau
Flag of Canada image

is your sub-select supposed to return only 1 row? A sub-select in the SELECT clause must return a single rom/value because it will be used as a column on the currently processed row of the main query.

try replacing DISCTINCT by TOP 1.
Avatar of gvamsimba

ASKER

No Eric, TOP 1 will only bring ONE value. I want all values related to that id
id 1 might have RED,
Id 2 might have GREEN
id 3 might have BLUE

TOP 1 will only bring ONE value
First of all you should post the complete command which reports the error. Try to format it to be readable. Then any debugging is much easier

Hi, I already posted the full error above..

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.
I did not ask for the error but for the complete command. The command you've posted cannot report such error because it has two lines commented out...
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'

Open in new window

And you could tell what's your goal. The "basic" command w/o lines which are commented should be OK:
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

Open in new window

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?
Hi Pavel, tat returns 7 values for that column selected.

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


I want to get WELLBORE_TYPE_ON_COMPLETION column    FROM OGA_DW_Staging.[WellMgr].[S_VG_Completio_Applicati_Details]  table
WHERE WELLBORE_TYPE_ON_COMPLETION gets values from [XViewMgr].[S_Xview_Env_Mapsets] table  WHERE MS_DOMAIN = 'WONS_WELLBORE_TYPE') 
So is your intention to add 7 rows generated by the subquery to each output row generated in the basic query? How would you like to format such output?  

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. 
I want to TEST IF THE VALUES for WELLBORE_TYPE_ON_COMPLETION column    FROM OGA_DW_Staging.[WellMgr].[S_VG_Completio_Applicati_Details]  table
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.
Something like this?
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')

Open in new window

Update: The JOIN here seems to be useless.
Or simpler form:
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'

Open in new window

as I already mentioned, my query works fine WITHOUT subquery.. I raised this question because I am interested in knowing WHY the subquery was failing and HOW to investigate that data.

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
How many rows returns this query?
SELECT DATA from [XViewMgr].[S_Xview_Env_Mapsets]
 WHERE MS_DOMAIN = 'WONS_WELLBORE_TYPE'

Open in new window

Or better
SELECT COUNT(*), COUNT(DISTINCT DATA) from [XViewMgr].[S_Xview_Env_Mapsets]
 WHERE MS_DOMAIN = 'WONS_WELLBORE_TYPE'

Open in new window

If the COUNT results are equal then the JOIN in the query is OK. 
The subquery itself is correct. You just cannot use it the way showed in your question.

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] 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 believe this query works BUT does it return correct results?
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 CERTIFIED SOLUTION
Avatar of Pavel Celba
Pavel Celba
Flag of Czechia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
yes both of your queries are giving same as mine. Thank you so much for your help.
very good 
You are welcome!