代码之家 › 专栏 › 技术社区 › user3811444

带有INNER JOINS和CASE的SQL视图中的错误

sapb1 sql-view sql-server sql

user3811444 · 技术社区 · 11 年前

我想创建一个sql查询,它将返回一个包含以下字段的视图:
s.ItemCode, s.ItemName, s.ItmsGrpCod, s.ItmsGrpNam, t.Name, t.FormulaResult_Cost

t.FormulaResult_Cost 将使用以下公式计算:

CASE WHEN IsNull(T3.U_VAR5, 0) = 0 THEN 0 ELSE ROUND(((T0.AvgPrice * T3.U_VAR1 * T3.U_VAR2) + T3.U_VAR3) * T3.U_VAR4 / IsNull(T3.U_VAR5, 0), 5) END AS FormulaResult_Cost

变量( U_VAR4 , U_VAR5 等等) 用户定义表 打电话 dbo.[@Z_VARIABLES]

我写的查询是:

SELECT TOP (100) PERCENT s.ItemCode, s.ItemName, s.ItmsGrpCod, s.ItmsGrpNam, t.Name, t.FormulaResult_Cost
FROM  (SELECT DISTINCT T0.ItemCode, T0.ItemName, T0.ItmsGrpCod, T1.ItmsGrpNam
               FROM   dbo.OITM AS T0 INNER JOIN
                              dbo.OITB AS T1 ON T0.ItmsGrpCod = T1.ItmsGrpCod
               WHERE (T0.QryGroup10 = 'Y')) AS s INNER JOIN
                   (SELECT T0.ItemCode, T3.Name, CASE WHEN IsNull(T3.U_VAR5, 0) = 0 THEN 0 ELSE ROUND(((T0.AvgPrice * T3.U_VAR1 * T3.U_VAR2) + T3.U_VAR3) 
                                   * T3.U_VAR4 / IsNull(T3.U_VAR5, 0), 5) END AS FormulaResult_Cost
                    FROM   dbo.[@Z_VARIABLES] AS T3 CROSS JOIN
                                   dbo.OITM AS T0) AS t ON s.ItemCode = t.ItemCode
ORDER BY s.ItemCode

如果每个类别只有一个公式,则此查询非常有效( t.Name :字段 [@Z_VARIABLES] ).

现在我想对每个类别都有不同的公式。
因此,我尝试使用以下代码:

SELECT TOP (100) PERCENT s.ItemCode, s.ItemName, s.ItmsGrpCod, s.ItmsGrpNam, t.Name, t.FormulaResult_Cost
FROM  (
        (SELECT DISTINCT T0.ItemCode, T0.ItemName, T0.ItmsGrpCod, T1.ItmsGrpNam
        FROM   dbo.OITM AS T0 INNER JOIN
               dbo.OITB AS T1 ON T0.ItmsGrpCod = T1.ItmsGrpCod
        WHERE (T0.QryGroup10 = 'Y')) AS s INNER JOIN          
            (SELECT * FROM dbo.[@Z_VARIABLES] AS T3 CROSS JOIN dbo.OITM AS T0,
                (CASE t3.Name
                    WHEN 'KOUZINES' THEN
                        SELECT T0.ItemCode, T3.Name, CASE WHEN IsNull(T3.U_VAR5, 0) = 0 THEN 0 ELSE ROUND(((T0.AvgPrice * T3.U_VAR1 * T3.U_VAR2) + T3.U_VAR3)  * T3.U_VAR4 / IsNull(T3.U_VAR5, 0), 5) END AS FormulaResult_Cost
                        FROM   dbo.[@Z_VARIABLES] AS T3 CROSS JOIN dbo.OITM AS T0
                    WHEN 'NYPTIRES' THEN
                        SELECT T0.ItemCode, T3.Name, '3.333' AS FormulaResult_Cost
                        FROM   dbo.[@Z_VARIABLES] AS T3 CROSS JOIN dbo.OITM AS T0
                    WHEN 'PATOUDES & KAPPAKIA' THEN
                        SELECT T0.ItemCode, T3.Name, '4.444' AS FormulaResult_Cost
                        FROM   dbo.[@Z_VARIABLES] AS T3 CROSS JOIN dbo.OITM AS T0
                    ELSE
                       SELECT T0.ItemCode, T3.Name, '11.11' AS FormulaResult_Cost
                       FROM   dbo.[@Z_VARIABLES] AS T3 CROSS JOIN dbo.OITM AS T0
                END)
            ) AS t ON s.ItemCode = t.ItemCode
)
ORDER BY s.ItemCode

这个查询有很多错误,例如:

Msg 156, Level 15, State 1, Line 9
Incorrect syntax near the keyword 'CASE'.
Msg 156, Level 15, State 1, Line 15
Incorrect syntax near the keyword 'WHEN'.
Msg 156, Level 15, State 1, Line 18
Incorrect syntax near the keyword 'WHEN'.
Msg 156, Level 15, State 1, Line 21
Incorrect syntax near the keyword 'WHEN'.
Msg 156, Level 15, State 1, Line 24
Incorrect syntax near the keyword 'WHEN'.
Msg 156, Level 15, State 1, Line 27
Incorrect syntax near the keyword 'WHEN'.
Msg 156, Level 15, State 1, Line 30
Incorrect syntax near the keyword 'WHEN'.
Msg 156, Level 15, State 1, Line 33
Incorrect syntax near the keyword 'WHEN'.
Msg 156, Level 15, State 1, Line 36
Incorrect syntax near the keyword 'WHEN'.
Msg 156, Level 15, State 1, Line 39
Incorrect syntax near the keyword 'ELSE'.
Msg 102, Level 15, State 1, Line 42
Incorrect syntax near ')'.

1 回复 | 直到 4 年前

Darka 11 年前

我的意思是你可以更简单地重写它:

  (SELECT * FROM dbo.[@Z_VARIABLES] AS T3 
    CROSS JOIN dbo.OITM AS T0
    INNER JOIN (
                SELECT T0.ItemCode, T3.Name
                , CASE t3.Name
                     WHEN 'KOUZINES' THEN     CASE WHEN IsNull(T3.U_VAR5, 0) = 0 THEN 0 ELSE ROUND(((T0.AvgPrice * T3.U_VAR1 * T3.U_VAR2) + T3.U_VAR3)  * T3.U_VAR4 / IsNull(T3.U_VAR5, 0), 5) END
                     WHEN 'NYPTIRES' THEN '3.333' 
                     WHEN 'PATOUDES & KAPPAKIA' THEN '4.444'
                     ELSE '11.11'
                  END AS FormulaResult_Cost
                FROM   dbo.[@Z_VARIABLES] AS T3 CROSS JOIN dbo.OITM AS T0
                )

这是未测试的。可以找到一些bug。我也不知道你的逻辑,但对我来说,很多人都觉得 CROSS JOINS 但就像我说的,不懂逻辑。