SQL IsNull with SELECT subquery in a query

Question

I have this:

ISNULL(FPONO.[Replan Ref_ No_],(SELECT DISTINCT PO.[Replan Ref_ No_]
FROM NAV_Vermorel_Live.dbo.[SC Vermorel SRL$Production Order] AS PO
WHERE SH.[External Document No_] = PO.[Old Prod_ Order No_] AND PO.[Source No_] = SL.No_))

This piece of SQL is part of big wall of text, which works without the IsNULL check. With IsNUll it outputs the error bellow. Could anyone point me in a right direction? I have null on that specific column, and I can get the right results from another table. I don't know how to do it.

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an 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_]
                                FROM NAV_Vermorel_Live.dbo.[SC Vermorel SRL$Production Order] AS PO
                                WHERE SH.[External Document No_] = PO.[Old Prod_ Order No_] AND 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_ to Invoice], 
SL.[Qty_ to 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
FROM NAV_Vermorel_Live.dbo.[SC Vermorel SRL$Sales Line] AS SL 
INNER JOIN NAV_Vermorel_Live.dbo.[SC Vermorel SRL$Sales Header] AS SH ON SL.[Document No_] = SH.No_ 
INNER JOIN NAV_Vermorel_Live.dbo.[SC Vermorel SRL$Customer] AS 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_" AS SRN,
            RE2."Source ID" As SalesOrder,
            PO.[Replan Ref_ No_]
            FROM NAV_Vermorel_Live.dbo."SC Vermorel SRL$Reservation Entry" AS RE1 JOIN NAV_Vermorel_Live.dbo."SC Vermorel SRL$Production Order" AS 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_" 
                        FROM NAV_Vermorel_Live.dbo."SC Vermorel SRL$Reservation Entry" AS RE
                        WHERE RE."Source Subtype"=1 AND RE.[Source Type] = 37) AS RE2 ON RE1.[Entry No_] = RE2.[Entry No_]
            WHERE (RE1."Source Type" = 5406 OR RE1."Source Type" = 5407) AND (RE1."Source Subtype" = 2 OR RE1."Source Subtype" = 3)) AS FPONO 
    ON (SL.[Document No_] = FPONO.[SalesOrder]) 
        AND (FPONO."SRN" = SL.[Line No_]) 
        AND (FPONO.[Item No_] = SL.[No_])
WHERE  (SL.[Outstanding Quantity] > 0) 
    AND (SL.[Location Code] = 'MACH FIN' 
        OR SL.[Location Code] = 'MAGAZIN NC' 
        OR SL.[Location Code] = 'MARFURI')

You could also do a Top 1 and order it the way you want in the inner select statement. — SS_DBA
– SS_DBA, Commented Nov 17, 2016 at 13:52
Skip the sub-query, do a JOIN instead - to get all of the sub-query's result! — jarlh
– jarlh, Commented Nov 17, 2016 at 13:59
Removing the ISNULL() will result in the same problem . . . if you have the same subquery. — Gordon Linoff
– Gordon Linoff, Commented Nov 17, 2016 at 14:07

Thorsten Kettner · Accepted Answer · 2016-11-17 13:51:15Z

1

The message is simple: it is possible to find more than one [Replan Ref_ No_] per [Old Prod_ Order No_] and [Source No_] in your table [SC Vermorel SRL$Production Order].

Either make sure your table doesn't contain duplicates (with a unique constraint) or change the subquery to return one value only, e.g. replace

SELECT DISTINCT PO.[Replan Ref_ No_]

with

SELECT MIN(PO.[Replan Ref_ No_])

answered Nov 17, 2016 at 13:51

Thorsten Kettner

96.6k8 gold badges56 silver badges82 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Catalin Cernat Over a year ago

Thanks! Works! Some NULL remained, ofc, they cannot be found, can I do ISNULL((ISNULL SQL BLA BLA ),'ERROR')?

Thorsten Kettner Over a year ago

Yes, you can nest it. More typical then would be to use COALESCE, which gets you the first non-null value: coalesce(textvalue, (select other_textvalue from ...), 'ERROR').

Jason Byrd · Accepted Answer · 2016-11-17 13:52:41Z

0

You should also be able to use top 1 in your sub query.

answered Nov 17, 2016 at 13:52

Jason Byrd

7264 silver badges7 bronze badges

2 Comments

SS_DBA Over a year ago

I know your reputation limits you to comments, but going forward give a full answer, not just a comment.

Jason Byrd Over a year ago

WEI_DBA I answered the question at exactly the same time.

Collectives™ on Stack Overflow

SQL IsNull with SELECT subquery in a query

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related