Catalin Cernat Catalin Cernat - 14 days ago 5
SQL Question

SQL Select with a set statement in it

I am trying to get the values for column

fpo
based on the values of other columns. The results are not in accordance with the
CASE
statement in the query. Can some one point me in a good direction?

SELECT SL.[Document No_], SL.[Sell-to Customer No_], SL.Type, SL.[Line No_], ISNULL(PO.No_, 'BUY') AS RPO,
CASE
WHEN PO.[Replan Ref_ No_] is null AND PO.[No_] IS NOT NULL THEN 'Neplanificata'
WHEN PO.[Replan Ref_ No_] IS NULL AND PO.[No_] = 'Buy' THEN 'Buy'
ELSE PO.[Replan Ref_ No_]
END AS fpo
FROM [SC Vermorel SRL$Sales Line] AS SL
INNER JOIN [SC Vermorel SRL$Sales Header] AS SH ON SL.[Document No_] = SH.No_
INNER JOIN [SC Vermorel SRL$Customer] AS Cust ON SL.[Sell-to Customer No_] = Cust.No_
LEFT OUTER JOIN [SC Vermorel SRL$Production Order] AS PO
ON (SH.[External Document No_] = PO.[Old Prod_ Order No_]) AND (PO.[Source No_] = SL.No_) AND (SL.Quantity = PO.Quantity)


Results:

enter image description here

enter image description here

Answer

Is this what you want?

SELECT SL.[Document No_], SL.[Sell-to Customer No_], SL.Type, SL.[Line No_], ISNULL(PO.No_, 'BUY') AS RPO,
        CASE 
            WHEN ISNULL(PO.[Replan Ref_ No_],'') = '' AND PO.[No_] IS NOT NULL THEN 'Neplanificata'
            WHEN ISNULL(PO.[Replan Ref_ No_],'') = '' AND ISNULL(PO.No_, 'BUY') = 'BUY' THEN 'Buy'
            ELSE PO.[Replan Ref_ No_]
        END AS fpo
    FROM   [SC Vermorel SRL$Sales Line] AS SL 
    INNER JOIN [SC Vermorel SRL$Sales Header] AS SH ON SL.[Document No_] = SH.No_
    INNER JOIN [SC Vermorel SRL$Customer] AS Cust ON SL.[Sell-to Customer No_] = Cust.No_
    LEFT OUTER JOIN [SC Vermorel SRL$Production Order] AS PO 
        ON (SH.[External Document No_] = PO.[Old Prod_ Order No_]) AND (PO.[Source No_] = SL.No_) AND (SL.Quantity = PO.Quantity)