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] ,
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
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.