CODEWITHSUNDEEP
sql-server-2014
Mostafa Amin
Mostafa Amin

Reputation: 1

SQL Server Error - Msg 116

My query is as follows, and contains a subquery within it:

SELECT       
    dbo.Lawsuit.LawsuitNUM, dbo.Lawsuit.LawsuitYear,
    dbo.Groups.GroupName, dbo.LawsuitType.LawsuitType,
    dbo.Courts.CourtName,
    (select 
         LawsuitID, DOJ, NextMeeting, ReceiptNUM, ExportNUM, ExportDate
     from 
         (select   
              dbo.LawsuitExport.LawsuitID, 
              dbo.LawsuitExport.DOJ,
              dbo.LawsuitExport.NextMeeting,
              dbo.LawsuitExport.ReceiptNUM,
              dbo.LawsuitExport.ExportNUM,
              dbo.LawsuitExport.ExportDate,
              row_number() over(partition by dbo.LawsuitExport.LawsuitID 
                                order by dbo.LawsuitExport.ExportDate desc) as rn
          from 
              dbo.LawsuitExport) as T 
       where 
           rn = 1)
FROM    
    dbo.Courts 
INNER JOIN
    dbo.LawsuitType ON dbo.Courts.CourtID = dbo.LawsuitType.CourtID 
INNER JOIN
    dbo.Groups ON dbo.LawsuitType.LawsuitTypeID = dbo.Groups.LawsuitTypeID 
INNER JOIN
    dbo.Lawsuit ON dbo.Groups.GroupID = dbo.Lawsuit.GroupID 
INNER JOIN
    dbo.LawsuitExport ON dbo.Lawsuit.LawsuitID = dbo.LawsuitExport.LawsuitID

The error I am receiving is:

Only one expression can be specified in the select list when the subquery is not introduced with EXISTS

Upvotes: 0

Views: 243

Answers (2)

Rahul
Rahul

Reputation: 77906

If that's what is your intention then consider modifying your query like

SELECT       
    dbo.Lawsuit.LawsuitNUM, dbo.Lawsuit.LawsuitYear,
    dbo.Groups.GroupName, dbo.LawsuitType.LawsuitType,
    dbo.Courts.CourtName,XXX.LawsuitID, XXX.DOJ, XXX.NextMeeting, XXX.ReceiptNUM, XXX.ExportNUM, XXX.ExportDate
FROM    
    dbo.Courts 
INNER JOIN
    dbo.LawsuitType ON dbo.Courts.CourtID = dbo.LawsuitType.CourtID 
INNER JOIN
    dbo.Groups ON dbo.LawsuitType.LawsuitTypeID = dbo.Groups.LawsuitTypeID 
INNER JOIN
    dbo.Lawsuit ON dbo.Groups.GroupID = dbo.Lawsuit.GroupID 
INNER JOIN
    dbo.LawsuitExport ON dbo.Lawsuit.LawsuitID = dbo.LawsuitExport.LawsuitID
INNER JOIN (select   
              LawsuitID, 
              DOJ,
              NextMeeting,
              ReceiptNUM,
              ExportNUM,
              ExportDate,
              row_number() over(partition by LawsuitID 
                                order by ExportDate desc) as rn
          from 
              dbo.LawsuitExport) XXX ON dbo.Lawsuit.LawsuitID = XXX.LawsuitID
WHERE XXX.rn = 1;

Upvotes: 0

gh9
gh9

Reputation: 10703

The line select LawsuitID,DOJ,NextMeeting,ReceiptNUM,ExportNUM,ExportDate doesnt work because you can only have 1 item returned from a sub query when it is trying to be used in another select statement. For example

SELECT X FROM Y GOOD

SELECT X, (SELECT A,B,C FROM FOO) FROM Y NOT GOOD

A,B,C cannot be mapped to 1 single element so that is invalid

Upvotes: 1

Related Questions