SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GOALTER View vwl_JustStock_ky AS
select
vwl_JustStock.ItemID,
vwl_JustStock.ItemName,
vwl_JustStock.MainSpec,
(Select CodeValue from gg_FieldValidD where InfoID='GG01' and gg_FieldValidD.Code = vwl_JustStock.GradeID) as Grade,
vwl_JustStock.LotNo,
(Select CodeValue from gg_FieldValidD where InfoID='GG08' and gg_FieldValidD.Code = vwl_JustStock.ColorID) as Color,
gg_Unit.UnitName as UnitName,
SUM(isNull(vwl_JustStock.MonthOriQTY,0) + isNull(vwl_JustStock.InQTY,0) - isNull(vwl_JustStock.OutQTY,0)) QTY,
(SELECT UnitName FROM GG_UNIT WHERE GG_UNIT.UNITID = vwl_JustStock.AssUnitID)as AssUnitName,
SUM(isNull(vwl_JustStock.AssMonthOriQTY,0) + isNull(vwl_JustStock.AssInQTY,0) - isNull(vwl_JustStock.AssOutQTY,0)) AssQTY,
vwl_JustStock.UserID,
vwl_JustStock.Attr1,
vwl_JustStock.Attr2,
vwl_JustStock.Attr3,
vwl_JustStock.HypoSpec,
(select notifyqty from wl_saleorderd) as ddqty,
(select assnotifyqty from wl_saleorderd) as assddqty
FROM vwl_JustStock Left join gg_StorageUnit on (vwl_JustStock.StorageUnitID = gg_StorageUnit.StorageUnitID)
Left Join gg_Unit on (gg_Unit.UnitID = vwl_JustStock.UnitID)
Left Join gg_dept on (gg_dept.deptid = vwl_JustStock.deptid)
GROUP BY vwl_JustStock.AccMonth,
vwl_JustStock.ItemID,
vwl_JustStock.ItemName,
vwl_JustStock.MainSpec,
vwl_JustStock.GradeID,
vwl_JustStock.LotNo,
vwl_JustStock.ColorID,
vwl_JustStock.StorageID,
vwl_JustStock.StorageUnitID,
gg_StorageUnit.UnitName,
vwl_JustStock.MerchantID,
gg_Unit.UnitName,
vwl_JustStock.AssUnitID,
vwl_JustStock.UserID,
vwl_JustStock.Attr1,
vwl_JustStock.Attr2,
vwl_JustStock.Attr3,
vwl_JustStock.HypoSpecGO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO在加入(select notifyqty from wl_saleorderd) as ddqty,
(select assnotifyqty from wl_saleorderd) as assddqty
这2列以后就报子查询返回的值多于一个,这个应该怎么解决啊
GO
SET ANSI_NULLS ON
GOALTER View vwl_JustStock_ky AS
select
vwl_JustStock.ItemID,
vwl_JustStock.ItemName,
vwl_JustStock.MainSpec,
(Select CodeValue from gg_FieldValidD where InfoID='GG01' and gg_FieldValidD.Code = vwl_JustStock.GradeID) as Grade,
vwl_JustStock.LotNo,
(Select CodeValue from gg_FieldValidD where InfoID='GG08' and gg_FieldValidD.Code = vwl_JustStock.ColorID) as Color,
gg_Unit.UnitName as UnitName,
SUM(isNull(vwl_JustStock.MonthOriQTY,0) + isNull(vwl_JustStock.InQTY,0) - isNull(vwl_JustStock.OutQTY,0)) QTY,
(SELECT UnitName FROM GG_UNIT WHERE GG_UNIT.UNITID = vwl_JustStock.AssUnitID)as AssUnitName,
SUM(isNull(vwl_JustStock.AssMonthOriQTY,0) + isNull(vwl_JustStock.AssInQTY,0) - isNull(vwl_JustStock.AssOutQTY,0)) AssQTY,
vwl_JustStock.UserID,
vwl_JustStock.Attr1,
vwl_JustStock.Attr2,
vwl_JustStock.Attr3,
vwl_JustStock.HypoSpec,
(select notifyqty from wl_saleorderd) as ddqty,
(select assnotifyqty from wl_saleorderd) as assddqty
FROM vwl_JustStock Left join gg_StorageUnit on (vwl_JustStock.StorageUnitID = gg_StorageUnit.StorageUnitID)
Left Join gg_Unit on (gg_Unit.UnitID = vwl_JustStock.UnitID)
Left Join gg_dept on (gg_dept.deptid = vwl_JustStock.deptid)
GROUP BY vwl_JustStock.AccMonth,
vwl_JustStock.ItemID,
vwl_JustStock.ItemName,
vwl_JustStock.MainSpec,
vwl_JustStock.GradeID,
vwl_JustStock.LotNo,
vwl_JustStock.ColorID,
vwl_JustStock.StorageID,
vwl_JustStock.StorageUnitID,
gg_StorageUnit.UnitName,
vwl_JustStock.MerchantID,
gg_Unit.UnitName,
vwl_JustStock.AssUnitID,
vwl_JustStock.UserID,
vwl_JustStock.Attr1,
vwl_JustStock.Attr2,
vwl_JustStock.Attr3,
vwl_JustStock.HypoSpecGO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO在加入(select notifyqty from wl_saleorderd) as ddqty,
(select assnotifyqty from wl_saleorderd) as assddqty
这2列以后就报子查询返回的值多于一个,这个应该怎么解决啊
SELECT vwl_JustStock.ItemID, vwl_JustStock.ItemName, vwl_JustStock.MainSpec, (
SELECT TOP 1 CodeValue
FROM gg_FieldValidD
WHERE InfoID = 'GG01'
AND gg_FieldValidD.Code = vwl_JustStock.GradeID
) AS Grade, vwl_JustStock.LotNo, (
SELECT TOP 1 CodeValue
FROM gg_FieldValidD
WHERE InfoID = 'GG08'
AND gg_FieldValidD.Code = vwl_JustStock.ColorID
) AS Color, gg_Unit.UnitName AS UnitName, SUM(
ISNULL(vwl_JustStock.MonthOriQTY, 0)+ISNULL(vwl_JustStock.InQTY, 0)- ISNULL(vwl_JustStock.OutQTY, 0)
) QTY, (
SELECT TOP 1 UnitName
FROM GG_UNIT
WHERE GG_UNIT.UNITID = vwl_JustStock.AssUnitID
) AS AssUnitName, SUM(
ISNULL(vwl_JustStock.AssMonthOriQTY, 0)+ISNULL(vwl_JustStock.AssInQTY, 0)- ISNULL(vwl_JustStock.AssOutQTY, 0)
) AssQTY, vwl_JustStock.UserID, vwl_JustStock.Attr1, vwl_JustStock.Attr2, vwl_JustStock.Attr3,
vwl_JustStock.HypoSpec, (
SELECT TOP 1 notifyqty
FROM wl_saleorderd
) AS ddqty, (
SELECT TOP 1 assnotifyqty
FROM wl_saleorderd
) AS assddqty
FROM vwl_JustStock
LEFT JOIN gg_StorageUnit
ON (vwl_JustStock.StorageUnitID=gg_StorageUnit.StorageUnitID)
LEFT JOIN gg_Unit
ON (gg_Unit.UnitID=vwl_JustStock.UnitID)
LEFT JOIN gg_dept
ON (gg_dept.deptid=vwl_JustStock.deptid)
GROUP BY vwl_JustStock.AccMonth, vwl_JustStock.ItemID, vwl_JustStock.ItemName, vwl_JustStock.MainSpec,
vwl_JustStock.GradeID, vwl_JustStock.LotNo, vwl_JustStock.ColorID, vwl_JustStock.StorageID,
vwl_JustStock.StorageUnitID, gg_StorageUnit.UnitName, vwl_JustStock.MerchantID, gg_Unit.UnitName,
vwl_JustStock.AssUnitID, vwl_JustStock.UserID, vwl_JustStock.Attr1, vwl_JustStock.Attr2,
vwl_JustStock.Attr3, vwl_JustStock.HypoSpecGO