Avatar of Leogal
Leogal
Flag for United States of America asked on

TSQL Expression error Msg 116, Level 16, in subquery

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
Microsoft SQL ServerMicrosoft SQL Server 2008

Avatar of undefined
Last Comment
PortletPaul

8/22/2022 - Mon
Jim Horn

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 Pletcher

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.
Zberteoc

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.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Leogal

ASKER
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]
Leogal

ASKER
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'
Zberteoc

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

⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Leogal

ASKER
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....
Zberteoc

I think you need to post your entire query here and not just fragments.
PortletPaul

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.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
ASKER CERTIFIED SOLUTION
PortletPaul

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Leogal

ASKER
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
Leogal

ASKER
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]
PortletPaul

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”
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.