2个相关联的案例陈述 [英] 2 case statements which are linked

问题描述

我对 SQL 编码比较陌生，对 case 语句有疑问.

I am relatively new in SQL coding and have a question regarding case statements.

我想要实现的目标:我想创建一个用于计算正确报废条款的查询.

What I want to achieve: I want to create a query being used for calculating correct obsolescence provision.

为此，我需要创建一个名为 Inventory Reach 的列和一个名为 Devaluation Class 的列.将计算两个字段.

For this I need to create a column called Inventory Reach and one called Devaluation Class. Both fields will be calculated.

现场贬值类的结果取决于库存范围计算.IE.根据库存到达，将确定贬值等级.例如.如果 inventoryreach 大于 9 ，则贬值等级为 1 (100%).执行下面的代码时，我收到以下错误消息:

The outcome in the field devaluation class is depending on the inventory reach calculation. I.e. depending on the inventory reach the devaluation class will be determined. E.g. if inventory reach is bigger than 9 , the devaluation class is 1 (100%). When executing the code below I get the following error message:

无效的列名库存范围".

Invalid column name 'Inventory Reach'.

所以对我来说，第二个 case 语句似乎失败了，它与第一个 case 语句挂在一起.但我很难找到问题的答案.如果有人能给黑暗带来一些光明，我将不胜感激.

So for me it seems to be so that the 2nd case statement fails and it hangs together with the first case statement. But I struggle to find an answer what is wrong. I would appreciate if someone could bring some light into the dark.

提前致谢

Select 
    [Material]
    ,[Plnt]
    ,case
        when [calculate 5-year demand] = 0
            then 9.01
        when  [BAAS SO GI (601) 36] = 0
            then 9.01
        when [MS] <> 'BI' or [MS] <> 'BO' 
            then ([Stock all SP WH]/([calculate 5-year demand]/5))
        when [MS] = 'BO'
            then ([Stock all SP WH]/[BAAS SO GI (601) 36])
        when [MS] ='BI'
            then 0
        else 9.01
    end as [Inventory Reach]
    ,case
        when [Inventory Reach] > 9
            then 1
        else 0.9
    end as [Devaluation Class]
from [BAAS_PowerBI].[dbo].[Obs]

推荐答案

您无权访问同一 select 子句中的 select 子句中的别名.有一个简单的修复方法，即使用派生表或公用表表达式:

You don't have access to the aliases in the select clause in the same select clause. There is a simple fix and that is using a derived table or a common table expression:

;with cte as
(
    Select 
        [Material]
        ,[Plnt]
        ,case
            when [calculate 5-year demand] = 0
                then 9.01
            when  [BAAS SO GI (601) 36] = 0
                then 9.01
            when [MS] <> 'BI' or [MS] <> 'BO' 
                then ([Stock all SP WH]/([calculate 5-year demand]/5))
            when [MS] = 'BO'
                then ([Stock all SP WH]/[BAAS SO GI (601) 36])
            when [MS] ='BI'
                then 0
            else 9.01
        end as [Inventory Reach]
    from [BAAS_PowerBI].[dbo].[Obs]
)

select [Material]
        ,[Plnt]
        ,[Inventory Reach]
        ,case
            when [Inventory Reach] > 9
                then 1
            else 0.9
        end as [Devaluation Class]
from cte

上面的代码使用了一个公用表表达式，它(在这个方案中)与使用派生表基本相同，只是更具可读性.这是等效的派生表:

The above code is using a common table expression which is (in this schenario) basically the same as using a derived table, only more readable. Here's the derived table equivalent:

select [Material]
        ,[Plnt]
        ,[Inventory Reach]
        ,case
            when [Inventory Reach] > 9
                then 1
            else 0.9
        end as [Devaluation Class]
from 
(
    Select 
        [Material]
        ,[Plnt]
        ,case
            when [calculate 5-year demand] = 0
                then 9.01
            when  [BAAS SO GI (601) 36] = 0
                then 9.01
            when [MS] <> 'BI' or [MS] <> 'BO' 
                then ([Stock all SP WH]/([calculate 5-year demand]/5))
            when [MS] = 'BO'
                then ([Stock all SP WH]/[BAAS SO GI (601) 36])
            when [MS] ='BI'
                then 0
            else 9.01
        end as [Inventory Reach]
    from [BAAS_PowerBI].[dbo].[Obs]
) derived

这篇关于2个相关联的案例陈述的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！