Assign values insde sql statement - SQL Server

Question

我正在尝试在SQL语句中分配默认值。

SELECT (CASE WHEN sd.IID IS NULL THEN 0 ELSE sd.IID END) AS IID, 
    pd.IID AS PurchaseOrerDetailsId, 
    i.[Description] AS Item, 
    sd.BatchNo, s.[Description] AS Unit,        
    CONVERT(varchar, sd.MfgDt, 103) AS MfgDt,
    sd.Qty = 0,
    CONVERT(varchar, sd.ExpiryDate, 103) AS ExpiryDate,
    sd.PackSize ='',
    pd.Qty = 0 AS QtyOrdered,
    sd.MRP, sd.PTR,
    sd.PurchaseRate,
    sd.PTS,
    sd.CGST,
    sd.SGST,
    sd.IGST,
    DiscPer,
    DiscVal,
    sd.Qty * sd.PurchaseRate AS PurchaseValue,
   (sd.Qty * sd.PurchaseRate * sd.CGST)/100 AS CGSTAmt,
   (sd.Qty * sd.PurchaseRate * sd.SGST)/100 AS SGSTAmt,
   (sd.Qty * sd.PurchaseRate * sd.IGST)/100 AS IGSTAmt,
    i.IID AS ItemId
FROM PurchaseOrderDetails pd

但是您可以在 SELECT 语句中看到"Qty = 0"或 PackSize ='' 将不起作用。如何在 SELECT 语句中为多个字段分配值。

谢谢帕尔塔

Answer 1

您是否正在寻找这样的东西：

SELECT 
(CASE WHEN sd.IID IS NULL THEN 0 ELSE sd.IID END) AS IID, 
pd.IID AS PurchaseOrerDetailsId, 
i.[Description] AS Item, 
sd.BatchNo, 
s.[Description] AS Unit, 
CONVERT(varchar, sd.MfgDt, 103) AS MfgDt, 
'0' AS sd.Qty, 
CONVERT(varchar, sd.ExpiryDate, 103) AS ExpiryDate, 
'' AS sd.PackSize, 
pd.Qty = 0 AS QtyOrdered, 
sd.MRP,
sd.PTR, 
sd.PurchaseRate, sd.PTS, sd.CGST, sd.SGST, sd.IGST, DiscPer, DiscVal, sd.Qty * sd.PurchaseRate AS PurchaseValue, (sd.Qty * sd.PurchaseRate * sd.CGST)/100 AS CGSTAmt, 
(sd.Qty * sd.PurchaseRate * sd.SGST)/100 AS SGSTAmt, (sd.Qty * sd.PurchaseRate * sd.IGST)/100 AS IGSTAmt, i.IID AS ItemId
            FROM PurchaseOrderDetails pd

不确定"数量"列

Answer 2

参考我之前的评论...为什么不直接用这个呢？

ISNULL(Qty,0)

Answer 3

试试这个：

SELECT COALESCE(sd.IID,0) AS IID, 
    pd.IID AS PurchaseOrerDetailsId, i.[Description] AS Item, 
    sd.BatchNo, s.[Description] AS Unit,        
    CONVERT(varchar, sd.MfgDt, 103) AS MfgDt,
    COALESCE(sd.Qty,0) As Qty,
    CONVERT(varchar, sd.ExpiryDate, 103) AS ExpiryDate,
    COALESCE(sd.PackSize,'') As PackSize,
    COALESCE(pd.Qty,0) AS QtyOrdered,
    sd.MRP, sd.PTR, sd.PurchaseRate,
    sd.PTS, sd.CGST, sd.SGST, sd.IGST, 
    DiscPer, DiscVal,
    COALESCE(sd.Qty,0) * sd.PurchaseRate AS PurchaseValue,
   (COALESCE(sd.Qty,0) * sd.PurchaseRate * sd.CGST)/100 AS CGSTAmt,
   (COALESCE(sd.Qty,0) * sd.PurchaseRate * sd.SGST)/100 AS SGSTAmt,
   (COALESCE(sd.Qty,0) * sd.PurchaseRate * sd.IGST)/100 AS IGSTAmt,
    i.IID AS ItemId
FROM PurchaseOrderDetails pd