I'm getting the error message:

Subquery returned more than 1 value. This is not permitted when the subquery follows =,!=,<,<=,>,=> or when the subquery is used as an expression.

Below is a small subset of a larger query, but the part of the query to determine the Test_Col value is essentially where I'm running into this issue. The query by itself works, but when I use it as a subquery within the larger query, I get this error message in SQL Server. Any ideas on where I'm going wrong?

select 
    distinct(nml.scode) Loan_Num,
    (select isnull(sum(isnull(t.smtd, 0)   isnull(t.sbeginbudget, 0)), 0) 
     from nmloan nml
     left join property p on nml.hprop = p.hmy
     left join total t on p.hmy = t.hppty
     where nml.hprop in (2380, 3348) 
       and t.umonth between '1/1/1900' and '9/30/2021' 
       and t.ibook = 1 and t.hacct in (1349, 1348, 1347, 1345, 1343, 1342, 1341, 1339, 1338, 1337, 1336, 1334, 1332, 1690, 1682, 1331) 
     group by nml.hprop) Test_Col
from 
    nmloan nml
left join 
    property p on nml.hprop = p.hmy
left join 
    total t on p.hmy = t.hppty
left join 
    acct ac on ac.hmy = t.hacct
left join 
    nmborrower nmb on nml.hmy = nmb.hnmloan
left join 
    person ps on nmb.hmyperson = ps.hmy
left join 
    nmloanterms nmt on nml.hmy = nmt.hloan
left join 
    nmcollateralxref nmx on nml.hmy = nmx.hnmloan
left join 
    nmcollateral nmc on nmx.hnmcollateral = nmc.hmy
left join 
    loanbut1 lb1 on nml.hmy = lb1.hcode
left join 
    NMLedger l ON nml.hmy = l.hNMLoan
left join 
    nmLedgerDetail d on l.hmy = d.hNMLedger
left join 
    loanbut7 lb on nml.hmy = lb.hcode
left join 
    loanbut8 lb8 on nml.hmy = lb8.hcode
left join 
    loanbut9 lb9 on nml.hmy = lb9.hcode
where
    nml.hprop in (2380, 3348) 
    and lb.lrPeriod in ('9/30/2021') 
    and lb9.lrnDate in ('9/30/2021')
group by 
    nml.hprop, nml.scode

CodePudding user response：

In SQL Server DB if your subquery is written after the select command where we wrote field name list, then your subquery must be return only one record and only one field, else you will get an error. In your script, you wrote subquery before the from command, after this Loan_Num,. I did a little research on your subquery. Your subquery will return more than 1 record in most cases. The reason is that you wrote group by nml.hprop and after the where command you wrote this condition nml.hprop in (2380, 3348). I would have written this query for you myself, but I don't know your business logic and what need you. If your subquery must return more than 1 record, so you must join this subquery to the main query, using inner join or left join, you can not write this subquery on the field list.

CodePudding user response：

Turns out that since my subquery's alias, nml, has the same alias as the parent query, nml (for the nmLoan table), it was did not work.

Upon changing my subquery's alias to nl and leaving the parent query's alias to nml, that actually worked and I was able to generate multiple results.