我一直用ADP设计存储过程,用到临时表的时候,都无法正常显示临时表的字段,只有一个*代替的,我只能靠手写....我查过GOOGLE,发现,很多工具都有这个问题,包括PB,VF,.NET的设计器...求一个解决的办法...下面是我的SQL语句
tmptable,PiDeplete,sumPiDeplete,都无法显示字段...
SELECT dbo.Pro.ProNumber AS 编号, dbo.Pro.ChiDescription AS 描述,
dbo.ProType.ProTypeName AS 类型, dbo.Stuff.StuffName AS 材料,
dbo.[Level].LevelName AS 等级, tmptable.qty AS 定单量, dbo.SMGS.Num AS 库存量,
PiDeplete.Dnum AS 定单消耗量, TotalDeplete.DNum AS 已消耗量,
dbo.SMGS.Num - ISNULL(TotalDeplete.DNum, 0) AS 可消耗量,
dbo.SMGS.Num - ISNULL(TotalDeplete.DNum, 0) AS 基数,
CASE WHEN tmptable.qty - ISNULL(SumPiDeplete.DNum, 0)
> 0 THEN tmptable.qty - ISNULL(SumPiDeplete.DNum, 0) ELSE 0 END AS 实需求量,
dbo.Pro.ProID, dbo.[Level].LevelID
FROM (SELECT Deplete.ProID, SUM(Deplete.Num) AS DNum
FROM Deplete INNER JOIN
(SELECT CostList.CProID AS ProID
FROM CostList INNER JOIN
Cost ON CostList.CostID = Cost.CostID INNER JOIN
PiList ON Cost.ProID = PiList.ProID
WHERE (PiList.PINO = @PiNo)
UNION
SELECT ProID
FROM PiList
WHERE (NOT ProID IN
(SELECT ProID
FROM Cost)) AND (PINO = @PiNo)) tmptable3 ON
tmptable3.ProID = Deplete.ProID
GROUP BY Deplete.ProID, Deplete.PiNo
HAVING (Deplete.PiNo = @PiNO)) SumPiDeplete RIGHT OUTER JOIN
dbo.Pro ON SumPiDeplete.ProID = dbo.Pro.ProID LEFT OUTER JOIN
(SELECT Deplete.ProID, Deplete.LevelID, SUM(Deplete.Num) AS DNum
FROM Deplete INNER JOIN
(SELECT CostList.CProID AS ProID
FROM CostList INNER JOIN
Cost ON CostList.CostID = Cost.CostID INNER JOIN
PiList ON Cost.ProID = PiList.ProID
WHERE (PiList.PINO = @PiNo)
UNION
SELECT ProID
FROM PiList
WHERE (NOT ProID IN
(SELECT ProID
FROM Cost)) AND (PINO = @PiNo)) tmptable3 ON
tmptable3.ProID = Deplete.ProID
GROUP BY Deplete.ProID, Deplete.LevelID) TotalDeplete RIGHT OUTER JOIN
dbo.[Level] INNER JOIN
dbo.SMGS ON dbo.[Level].LevelID = dbo.SMGS.LevelID LEFT OUTER JOIN
(SELECT Deplete.ProID, Deplete.LevelID, SUM(Deplete.Num) AS DNum
FROM Deplete INNER JOIN
(SELECT CostList.CProID AS ProID
FROM CostList INNER JOIN
Cost ON CostList.CostID = Cost.CostID INNER JOIN
PiList ON Cost.ProID = PiList.ProID
WHERE (PiList.PINO = @PiNo)
UNION
SELECT ProID
FROM PiList
WHERE (NOT ProID IN
(SELECT ProID
FROM Cost)) AND (PINO = @PiNo)) tmptable3 ON
tmptable3.ProID = Deplete.ProID
GROUP BY Deplete.ProID, Deplete.PiNo, Deplete.LevelID
HAVING (Deplete.PiNo = @PiNO)) PiDeplete ON
dbo.[Level].LevelID = PiDeplete.LevelID ON
TotalDeplete.LEVELID = dbo.[Level].LevelID ON dbo.Pro.ProID = PiDeplete.ProID AND
dbo.Pro.ProID = TotalDeplete.ProID LEFT OUTER JOIN
dbo.Stuff ON dbo.Pro.StuffID = dbo.Stuff.StuffID LEFT OUTER JOIN
dbo.ProType ON dbo.Pro.ProTypeID = dbo.ProType.ProTypeID FULL OUTER JOIN
(SELECT ProID, SUM(qty) AS qty
FROM (SELECT CostList.CProID AS ProID,
CostList.Num * PiList.QTY AS QTY
FROM CostList INNER JOIN
Cost ON CostList.CostID = Cost.CostID INNER JOIN
PiList ON Cost.ProID = PiList.ProID
WHERE (PiList.PINO = @PiNo)
UNION ALL
SELECT ProID, QTY
FROM PiList
WHERE (NOT (ProID IN
(SELECT ProID
FROM Cost))) AND (PINO = @PiNo)) Tmptable
GROUP BY ProID) tmptable ON dbo.SMGS.ProID = tmptable.proid
tmptable,PiDeplete,sumPiDeplete,都无法显示字段...
SELECT dbo.Pro.ProNumber AS 编号, dbo.Pro.ChiDescription AS 描述,
dbo.ProType.ProTypeName AS 类型, dbo.Stuff.StuffName AS 材料,
dbo.[Level].LevelName AS 等级, tmptable.qty AS 定单量, dbo.SMGS.Num AS 库存量,
PiDeplete.Dnum AS 定单消耗量, TotalDeplete.DNum AS 已消耗量,
dbo.SMGS.Num - ISNULL(TotalDeplete.DNum, 0) AS 可消耗量,
dbo.SMGS.Num - ISNULL(TotalDeplete.DNum, 0) AS 基数,
CASE WHEN tmptable.qty - ISNULL(SumPiDeplete.DNum, 0)
> 0 THEN tmptable.qty - ISNULL(SumPiDeplete.DNum, 0) ELSE 0 END AS 实需求量,
dbo.Pro.ProID, dbo.[Level].LevelID
FROM (SELECT Deplete.ProID, SUM(Deplete.Num) AS DNum
FROM Deplete INNER JOIN
(SELECT CostList.CProID AS ProID
FROM CostList INNER JOIN
Cost ON CostList.CostID = Cost.CostID INNER JOIN
PiList ON Cost.ProID = PiList.ProID
WHERE (PiList.PINO = @PiNo)
UNION
SELECT ProID
FROM PiList
WHERE (NOT ProID IN
(SELECT ProID
FROM Cost)) AND (PINO = @PiNo)) tmptable3 ON
tmptable3.ProID = Deplete.ProID
GROUP BY Deplete.ProID, Deplete.PiNo
HAVING (Deplete.PiNo = @PiNO)) SumPiDeplete RIGHT OUTER JOIN
dbo.Pro ON SumPiDeplete.ProID = dbo.Pro.ProID LEFT OUTER JOIN
(SELECT Deplete.ProID, Deplete.LevelID, SUM(Deplete.Num) AS DNum
FROM Deplete INNER JOIN
(SELECT CostList.CProID AS ProID
FROM CostList INNER JOIN
Cost ON CostList.CostID = Cost.CostID INNER JOIN
PiList ON Cost.ProID = PiList.ProID
WHERE (PiList.PINO = @PiNo)
UNION
SELECT ProID
FROM PiList
WHERE (NOT ProID IN
(SELECT ProID
FROM Cost)) AND (PINO = @PiNo)) tmptable3 ON
tmptable3.ProID = Deplete.ProID
GROUP BY Deplete.ProID, Deplete.LevelID) TotalDeplete RIGHT OUTER JOIN
dbo.[Level] INNER JOIN
dbo.SMGS ON dbo.[Level].LevelID = dbo.SMGS.LevelID LEFT OUTER JOIN
(SELECT Deplete.ProID, Deplete.LevelID, SUM(Deplete.Num) AS DNum
FROM Deplete INNER JOIN
(SELECT CostList.CProID AS ProID
FROM CostList INNER JOIN
Cost ON CostList.CostID = Cost.CostID INNER JOIN
PiList ON Cost.ProID = PiList.ProID
WHERE (PiList.PINO = @PiNo)
UNION
SELECT ProID
FROM PiList
WHERE (NOT ProID IN
(SELECT ProID
FROM Cost)) AND (PINO = @PiNo)) tmptable3 ON
tmptable3.ProID = Deplete.ProID
GROUP BY Deplete.ProID, Deplete.PiNo, Deplete.LevelID
HAVING (Deplete.PiNo = @PiNO)) PiDeplete ON
dbo.[Level].LevelID = PiDeplete.LevelID ON
TotalDeplete.LEVELID = dbo.[Level].LevelID ON dbo.Pro.ProID = PiDeplete.ProID AND
dbo.Pro.ProID = TotalDeplete.ProID LEFT OUTER JOIN
dbo.Stuff ON dbo.Pro.StuffID = dbo.Stuff.StuffID LEFT OUTER JOIN
dbo.ProType ON dbo.Pro.ProTypeID = dbo.ProType.ProTypeID FULL OUTER JOIN
(SELECT ProID, SUM(qty) AS qty
FROM (SELECT CostList.CProID AS ProID,
CostList.Num * PiList.QTY AS QTY
FROM CostList INNER JOIN
Cost ON CostList.CostID = Cost.CostID INNER JOIN
PiList ON Cost.ProID = PiList.ProID
WHERE (PiList.PINO = @PiNo)
UNION ALL
SELECT ProID, QTY
FROM PiList
WHERE (NOT (ProID IN
(SELECT ProID
FROM Cost))) AND (PINO = @PiNo)) Tmptable
GROUP BY ProID) tmptable ON dbo.SMGS.ProID = tmptable.proid
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货