We help IT Professionals succeed at work.

TSQL Expression error Msg 116, Level 16, in subquery

2,293 Views
Last Modified: 2014-05-15
Hello,

I am still in the process of learning nested select statements. I have such as statement as shown below that is giving me the error:

Msg 116, Level 16, State 1, Line 7
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

If I am reading the error message correctly, the problem lies with the fact that I have two WHEN statements inside the Select statements. I may be wrong. What I am working towards accomplishing is the abliity to find the correct match for the incoming data record to the outgoing data.

There are a few multiples in the incoming data aka  ci.[OCCUPANCY_CD]  and there almost nothing but multiples in the compare/ lookup column that is used to populate output aka oc.[CITI Occupancy CODE].

I have attached a xref table showing the data being used.  The columns are as follows:
Column 1 = the final resulting output
Column 2 = the description of the 1st column
Column 3 = the incoming data from a row in the CI. table.
Column 4= the description of the 3rd column


Can someone help me with the correct syntax please?


(
   SELECT [CITI Occupancy CODE] ,                        
      (SELECT
        CASE       
             WHEN  ci.[OCCUPANCY_CD] IS NULL THEN  '6'
             WHEN ci.[OCCUPANCY_CD]  IS NOT NULL  
                   AND ci.[OCCUPANCY_CD]  = oc.[CITI Occupancy CODE]
                               THEN  oc.[CITI Occupancy CODE]
        END) as [Occupancy Code]
       FROM dbo.[CITI_Occupancy] oc

 )
C-Occupancy-Code.xls
Comment
Watch Question

Jim HornSQL Server Data Dude
CERTIFIED EXPERT
Most Valuable Expert 2013
Author of the Year 2015

Commented:
Post your entire SQL, as the problem likely likes with how it joins to the subquery you posted, and not the subquery itself.

>the problem lies with the fact that I have two WHEN statements inside the Select statements.
Likely not the problem.
Scott PletcherSenior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
The problem is that you're trying to return two columns -- [CITI Occupancy CODE] and [Occupancy Code] -- from a subquery.  As the error msg states, that is not allowed.

The syntax is off anyway, as the sub-sub-SELECT:

  (SELECT
        CASE      
             WHEN  ci.[OCCUPANCY_CD] IS NULL THEN  '6'
             WHEN ci.[OCCUPANCY_CD]  IS NOT NULL  
                   AND ci.[OCCUPANCY_CD]  = oc.[CITI Occupancy CODE]
                               THEN  oc.[CITI Occupancy CODE]
        END)

doesn't have a FROM clause in it either.  If there is no table reference there, a CASE alone is enough, although that still leaves 2 columns in a subquery result, which is invalid.
CERTIFIED EXPERT

Commented:
You can do this:
select 
	col1,
	(select collx from tbl2 where some_col=t1.some_other_col) as col2
from
	tbl1 t1

Open in new window

But you can't do this:
select 
	col1,
	(select colx, coly from tbl2 where some_col=t1.some_other_col) as col2
from
	tbl1 t1

Open in new window

In the first query you get col1 from tbl1 and the colx from tbl2 WHERE matches some other column in tbl1 and you name it as col2. The "nested" select  thatement keeps place for one column so it HAS TO retunrn only 1 column and 1 row for each row in tbl1.

The second query is wrong because you CANNOT have 2 columns in the nested query to keep place for a column.

In your query the nested query returns 2 columns,:  [CITI Occupancy CODE]  and whatever results from the CASE statemen and that is wrong. Also if that nested query(actually called a subquery) returns more than 1 row it will also generate error.

Author

Commented:
I modified the code to the following:

, SELECT  
      [OCCUPANCY_CD] ,        
( SELECT [CMS_OCCUPANCY_CODE] FROM [CITI_Occupancy]
WHERE [CITI_Occupancy_CODE] =  ci.[OCCUPANCY_CD] )

from [CITI_COREINFO] ci

When running the entire thing with this new subquery I receive the following errors.

Msg 156, Level 15, State 1, Line 287
Incorrect syntax near the keyword 'SELECT'.
Msg 156, Level 15, State 1, Line 316
Incorrect syntax near the keyword 'as'

Here is the code for line 316:
, CAST(' ' as varchar(3)) as [Date Recorded]

Author

Commented:
I think I have it now:

,  (
            SELECT top 1
                [CMS_OCCUPANCY_CODE]
                FROM [CITI_Occupancy] WHERE [CITI_Occupancy_CODE] =  ci.[OCCUPANCY_CD]  
   )

now I just need to add a second criteria ( where ci.[OCCUPANCY_CD] is null then '6'
CERTIFIED EXPERT

Commented:
I think here a LEFT join is much better suited in this case and will have the same result. It is possible to have some multiple rows with the same value of one of the columns  if the relation is not 1 to 1:
SELECT   
	ci.[OCCUPANCY_CD],        
	co.[CMS_OCCUPANCY_CODE]
from 
	[CITI_COREINFO] ci
	LEFT JOIN [CITI_Occupancy] co
		on co.[CITI_Occupancy_CODE]=ci.[OCCUPANCY_CD]

Open in new window

Author

Commented:
Drats!

Now I am getting 2 errors...
Msg 156, Level 15, State 1, Line 303
Incorrect syntax near the keyword 'SELECT'.
Msg 156, Level 15, State 1, Line 313
Incorrect syntax near the keyword 'as'.

Below is the section of code involved.

, CAST('Y' as varchar(1)) as [Done Flag]

----------------------------------------------------------------------------- Occupancy code
 ,  SELECT  
      ci.[OCCUPANCY_CD],        
      co.[CMS_OCCUPANCY_CODE]  
            from
      [CITI_COREINFO] ci  
      LEFT JOIN [CITI_Occupancy] co
            on co.[CITI_Occupancy_CODE]=ci.[OCCUPANCY_CD]  

------------------------------------------------------------------------------  recording      
, CAST(' ' as varchar(3)) as [Date Recorded]


I admit that I am still learning the syntax of SQL and the various ways to end with the same result.  


here is another stab I made it with the sql line of code before and after this union

  , CAST('Y' as varchar(1)) as [Done Flag]

,  (
            SELECT top 1
                [CMS_OCCUPANCY_CODE]  
                FROM [CITI_Occupancy] WHERE [CITI_Occupancy_CODE] =  ci.[OCCUPANCY_CD]    
   )

              UNION
      
  (
            SELECT   [CMS_OCCUPANCY_CODE] ,

                ( CASE
                    WHEN  ci.[OCCUPANCY_CD] IS NULL THEN '6'    
                 END ) as [Occupancy Code]

         FROM [CITI_OCCUPANCY]  
    )      

------------------------------------------------------------------------------  recording      
, CAST(' ' as varchar(3)) as [Date Recorded]


I truly appreciate any help that can be given as I will need to replicate this code in yet another part of the program....
CERTIFIED EXPERT

Commented:
I think you need to post your entire query here and not just fragments.
PortletPaulEE Topic Advisor
CERTIFIED EXPERT
Most Valuable Expert 2014
Awarded 2013

Commented:
Please consider you are asking us to respond to bits and pieces of information that are changing as this question moves on. The line numbers mean nothing to us for example as we don't get to see the whole thing.

Please post the entire query as it now exists so we can cast our eyes across it. If there are syntax errors it is likely we will spot them. Ultimately we can't do the testing without your tables and data - but let's please start with the whole query.
EE Topic Advisor
CERTIFIED EXPERT
Most Valuable Expert 2014
Awarded 2013
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
I finally was able ot get the occupancy code working without a subquery and now have a similar problem with interest method.

If I use a case statement I return multiples. When I try once again to use a subquery I run into a problem with pulling the field I want labeling the ouput

I appoligize in advance for the code change if it causes confusion or frustration the same error occurs with out Select top 1 which I realize is not the best solution.  

I have removed all company identifies in the tables and fields and made the references vanilla.

I am atatching the SQL code and the table that is at issue.  I humbly thank one and all for any direction/ instruction provided.
int-method.xls
MASTER-ACQUISTION-SQL-QUERY-TO-P.docx

Author

Commented:
I realized two things, one i was working to hard at resolving the problem and two there are 2 'A' methods in the table.
I was able to fix the interest method by using the following code:

, CASE WHEN ci.[ACCOUNT_NBR] =  ino.[ACCOUNT_NBR] AND ni.[interest_method] = 'A' THEN '10' -- Interest Only
             WHEN ni.[interest_method] not in ('0','7','8')  THEN im.[CMS Int Method Code]  
             WHEN ni.[interest_method] = im.[CITI Int Method Code]  AND ni.[interest_method] in ('0','7','8') then '00'
                 END  as [Interest Method]
PortletPaulEE Topic Advisor
CERTIFIED EXPERT
Most Valuable Expert 2014
Awarded 2013

Commented:
so you don't need assistance on that query now...

However feel compelled to point out that "select distinct" and the balance of that query are not a good combination.

Please see "Select Distinct is returning duplicates ..."and look for The “Hail Mary Distinct”

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.