SQL Query generating this error -Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

Question

I have a SQL query Question, I am trying to nest two separate queries which run perfectly fine individually.

---

The first query is

Select DISTINCT Leads.EmailAddress,ClientCustomFields.CommonName , LeadSources.Name, CONVERT(VARCHAR(10), leads.LeadStatusDate,101) AS [MM/DD/YYYY],Leads.Age,Leads.State, Leads.ProgramOfInterest, Leads.HighestEducationLevel, Leads.LeadNumber from LeadSources inner join Leads on Leads.LeadSourceNumber=LeadSources.LeadSourceNumber join ClientCustomFieldPrograms on Leads.ClientCustomFieldProgramsNumber=ClientCustomFieldPrograms.ClientCustomFieldProgramsNumber join  ClientCustomFields on ClientCustomFieldPrograms.ClientCustomFieldsNumber=ClientCustomFields.ClientCustomFieldsNumber where Leads.EmailAddress in ('beloved1965@charter.net') AND Year (Leads.LeadDate)=2011 and ClientCustomFields.CommonName LIKE '%Ashford%' and Leads.LeadStatus='sent' order by  Leads.EmailAddress

---

The second query

 /****** Script for SelectTopNRows command from SSMS  ******/ 
SELECT 
CASE
   WHEN CustomFieldsXML.value('(/Salutation/node())[1]', 'varchar(max)') = 'Mr.' THEN   'Male'
WHEN CustomFieldsXML.value('(/Salutation/node())[1]', 'varchar(max)') = 'Mrs.' THEN 'Female'
WHEN CustomFieldsXML.value('(/Salutation/node())[1]', 'varchar(max)') = 'Ms.' THEN 'Female'
ELSE 'Unknown' END as Gender, dbo.ClientLeadDistribution.LeadNumber, dbo.ClientLeadDistribution.Postdate, dbo.ClientLeadDistribution.ClientCustomFieldsNumber, dbo.ClientCustomFields.CommonName, dbo.Leads.LeadSourceNumber, dbo.LeadSources.Name, dbo.Leads.ProgramOfInterest, dbo.Leads.EmailAddress from dbo.ClientLeadDistribution join dbo.Leads on dbo.ClientLeadDistribution.LeadNumber = dbo.Leads.LeadNumber join dbo.LeadSources on dbo.Leads.LeadSourceNumber = dbo.LeadSources.LeadSourceNumber join dbo.ClientCustomFields on dbo.ClientLeadDistribution.ClientCustomFieldsNumber = dbo.ClientCustomFields.ClientCustomFieldsNumber where CustomFieldsXML.exist('/Salutation') = 1 and YEAR (postdate) = 2012 and MONTH (postdate)=1 --and dbo.ClientLeadDistribution.ClientCustomFieldsNumber in (1810,1820,1830,2750,2760,2770) order by PostDate, ClientCustomFieldsNumber

---

The combination is

    Select DISTINCT ClientLeadDistribution.ClientCustomFieldsNumber,   Leads.EmailAddress,ClientCustomFields.CommonName , LeadSources.Name, CONVERT(VARCHAR(10), leads.LeadStatusDate,101) AS [MM/DD/YYYY],Leads.Age,Leads.State, Leads.ProgramOfInterest, Leads.HighestEducationLevel, Leads.LeadNumber from LeadSources inner join Leads  on Leads.LeadSourceNumber=LeadSources.LeadSourceNumber join ClientCustomFieldPrograms on Leads.ClientCustomFieldProgramsNumber=ClientCustomFieldPrograms.ClientCustomFieldProgramsNumber join  ClientCustomFields on ClientCustomFieldPrograms.ClientCustomFieldsNumber=ClientCustomFields.ClientCustomFieldsNumber join ClientLeadDistribution on ClientCustomFields.ClientCustomFieldsNumber=ClientLeadDistribution.ClientCustomFieldsNumber where Leads.EmailAddress in ('beloved1965@charter.net') AND Year (Leads.LeadDate)=2011  and ClientCustomFields.CommonName LIKE '%Fortis%' and Leads.LeadStatus='sent' and ClientLeadDistribution.ClientCustomFieldsNumber =(SELECT ClientLeadDistribution.CustomFieldsXML,CASE
WHEN CustomFieldsXML.value('(/Salutation/node())[1]', 'varchar(max)') = 'Mr.' THEN 'Male'
WHEN CustomFieldsXML.value('(/Salutation/node())[1]', 'varchar(max)') = 'Mrs.' THEN 'Female'
WHEN CustomFieldsXML.value('(/Salutation/node())[1]', 'varchar(max)') = 'Ms.' THEN 'Female'
ELSE 'Unknown' END as Gender from dbo.ClientLeadDistribution join dbo.Leads on dbo.ClientLeadDistribution.LeadNumber = dbo.Leads.LeadNumber join dbo.LeadSources on dbo.Leads.LeadSourceNumber = dbo.LeadSources.LeadSourceNumber join dbo.ClientCustomFields on dbo.ClientLeadDistribution.ClientCustomFieldsNumber = dbo.ClientCustomFields.ClientCustomFieldsNumber where CustomFieldsXML.exist('/Salutation') = 1 AND YEAR (postdate) = 2012 and MONTH (postdate)=1) --and dbo.ClientLeadDistribution.ClientCustomFieldsNumber in (1810,1820,1830,2750,2760,2770)--order by ClientCustomFields.CommonName--order by  Leads.EmailAddress

And Finally I get this error I tried stuff but nothing seems to work. Any help or suggestions appreciated.

Answer 1

Your subquery is returning 2 fields: "CustomFieldsXML" and "gender".

The error is telling you that you cannot have it return 2 fields. I think you are only after the "gender" so remove the first.