sql server - SQL IsNull with SELECT subquery in a query -


i have this:

isnull(fpono.[replan ref_ no_],(select distinct po.[replan ref_ no_] nav_vermorel_live.dbo.[sc vermorel srl$production order] po sh.[external document no_] = po.[old prod_ order no_] , po.[source no_] = sl.no_)) 

this piece of sql part of big wall of text, works without isnull check. isnull outputs error bellow. point me in right direction? have null on specific column, , can right results table. don't know how it.

subquery returned more 1 value. not permitted when subquery follows =, !=, <, <= , >, >= or when subquery used expression.

select sl.[document no_],  sl.[sell-to customer no_],  sl.type, sl.[line no_],  isnull(fpono.[replan ref_ no_],(select distinct po.[replan ref_ no_]                                 nav_vermorel_live.dbo.[sc vermorel srl$production order] po                                 sh.[external document no_] = po.[old prod_ order no_] , po.[source no_] = sl.no_)), sl.no_,  sl.[location code],  sl.[posting group],  sl.[shipment date],  sl.description,  sl.[unit of measure],  sl.quantity,  sl.[outstanding quantity],  sl.[qty_ invoice],  sl.[qty_ ship],  sl.[unit price],  sl.amount, sl.[net weight], sl.[outstanding amount],  sl.[qty_ shipped not invoiced],  sl.[quantity shipped],  sl.[quantity invoiced],  sl.[gen_ prod_ posting group],  sl.[line amount],  sl.[item category code],  sl.[requested delivery date],   sl.[shipping time],  sl.[piece index],  sl.urgenta,  sl.[document type],  sh.[external document no_],  cust.name nav_vermorel_live.dbo.[sc vermorel srl$sales line] sl  inner join nav_vermorel_live.dbo.[sc vermorel srl$sales header] sh on sl.[document no_] = sh.no_  inner join nav_vermorel_live.dbo.[sc vermorel srl$customer] cust on sl.[sell-to customer no_] = cust.no_  left join (select              re1."entry no_",              re1."item no_",              re1."quantity (base)",              re1."source subtype",              re1."source id",               re1."source type",             re1."source ref_ no_",             re2."source ref_ no_" srn,             re2."source id" salesorder,             po.[replan ref_ no_]             nav_vermorel_live.dbo."sc vermorel srl$reservation entry" re1 join nav_vermorel_live.dbo."sc vermorel srl$production order" po             on re1."source id" = po.no_              right join (select                          re."entry no_",                          re."item no_",                          re."quantity (base)",                          re."source subtype",                          re."source id",                          re."source ref_ no_"                          nav_vermorel_live.dbo."sc vermorel srl$reservation entry" re                         re."source subtype"=1 , re.[source type] = 37) re2 on re1.[entry no_] = re2.[entry no_]             (re1."source type" = 5406 or re1."source type" = 5407) , (re1."source subtype" = 2 or re1."source subtype" = 3)) fpono      on (sl.[document no_] = fpono.[salesorder])          , (fpono."srn" = sl.[line no_])          , (fpono.[item no_] = sl.[no_])  (sl.[outstanding quantity] > 0)      , (sl.[location code] = 'mach fin'          or sl.[location code] = 'magazin nc'          or sl.[location code] = 'marfuri')  

the message simple: possible find more 1 [replan ref_ no_] per [old prod_ order no_] , [source no_] in table [sc vermorel srl$production order].

either make sure table doesn't contain duplicates (with unique constraint) or change subquery return 1 value only, e.g. replace

select distinct po.[replan ref_ no_] 

with

select min(po.[replan ref_ no_]) 

Comments

Popular posts from this blog

account - Script error login visual studio DefaultLogin_PCore.js -

xcode - CocoaPod Storyboard error: -