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
Post a Comment