CASE语句SQL Server的执行顺序 [英] Execution order of CASE statement SQL Server

问题描述

按顺序执行 CASE 语句，该语句具有多个部分，这些语句属于 INSERT 语句的一部分，并执行规则"以使单词更好地保留在原处甚至在下一行之后?在下面的查询中，PO_TYPE分配是否否决下一个命令-例如查看文章列表?因此，即使该文章位于语句第二部分的列表中(如果它是05或07类型)，它仍将分配给Andrew?

Does a CASE statement that has multiple parts that is part of an INSERTstatement execute in order and do the 'rules' for lack of a better word stay in place even after the next line? in the query below, does the PO_TYPE assignment overrule the next command - to look in a list of articles for example? So even if that article was in the list in the second part of the statement if it was type 05 or 07 it will still assign to Andrew?

谢谢.

/*INSERT values into the table using SELECT making sure to exclude vendor 20800 - (see last line of code)*/

INSERT INTO SCM_PO_EMPLOYEE_NAME (PO_NUMBER, PO_ITEM_NUMBER, MATERIAL, BUSINESS_UNIT_CODE,PO_TYPE,TEAM_MEMBER_NAME)

                                  SELECT I.PO_NUMBER, 
                                         I.PO_ITEM_NUMBER, 
                                         I.MATERIAL,
                                         B.BU_CODE, 
                                         H.PO_TYPE,
                                         CASE WHEN H.PO_TYPE IN ('05','07') -- Promo PO type - should be on both po type and stock category
                                              AND  I.STOCK_CATEGORY LIKE ('A60383%') -- stock category is second part of the check
                                              THEN 'AZ'
                                              WHEN H.PO_TYPE = '02' -- ma PO type
                                              THEN 'MB'
                                              WHEN I.MATERIAL IN ( SELECT ARTICLE
                                                                   FROM   ADI_USER_MAINTAINED.dbo.SCM_EMPLOYEE_ARTICLE A ) -- Check the Employee to article table next
                                              THEN A.TEAM_MEMBER_NAME -- If the PO number matches that conditions then assign the employee from the employee article table
                                              WHEN M.BUSINESS_UNIT_CODE = B.BU_CODE -- if not use then go to the BU assignment (below)
                                              THEN B.TEAM_MEMBER_NAME  --- Use the team member name from the Employee_BU table
                                         END  AS   [TEAM_MEMBER_NAME]

                                  FROM   PDX_SAP_USER.dbo.VW_PO_HEADER H
                                  JOIN   PDX_SAP_USER.dbo.VW_PO_ITEM I ON H.PO_NUMBER = I.PO_NUMBER 
                                  JOIN   PDX_SAP_USER.dbo.VW_MM_MATERIAL M ON I.MATERIAL = M.MATERIAL 
                                  JOIN   ADI_USER_MAINTAINED.dbo.SCM_EMPLOYEE_ARTICLE A ON I.MATERIAL = A.ARTICLE
                                  JOIN   ADI_USER_MAINTAINED.dbo.SCM_EMPLOYEE_BU B ON B.BU_CODE = M.BUSINESS_UNIT_CODE


                                  WHERE H.VENDOR_NO <> '20800'; --Exclude '20800' as a vendor!!

推荐答案

一个 case expression 会被顺序评估.

因此，仅当第一个 then 不返回true时，才评估第二个 then .作为一个极端的例子，请考虑:

So, the second then is only evaluated when the first then does not return true. As an extreme example of this, consider:

select (case when 1=1 then 'true'
             when 1/0 = 0 then 'error'
        end)

这将返回'true'而不是错误提示信息.

This returns 'true' instead of error'ing out.

这篇关于CASE语句SQL Server的执行顺序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！