在包含顶级union intersect或except运算符的语句中不允许sql变量赋值

我知道这个问题以前可能有人问过。但我在解决问题。有人能帮我理解这个特定用例中的错误吗？每当我尝试使用在顶部声明变量并使用子查询时。我不确定在这个特定的用例中，这些值是如何分配给变量的。

USE [HRCMS]
    GO
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO

    ALTER PROCEDURE [dbo].[getRSUVestingGrantDataForReport]
    @key varchar(50),
    @Emplid VARCHAR(6),
    @AwardPrice varchar(50)

    AS
    BEGIN

            DECLARE @ParticipantName varchar(50),       
            @AwardValue varchar(50),    
            @NumberofRSU varchar(50);
        SELECT 

            @ParticipantName = convert(VARCHAR(500),DECRYPTBYPASSPHRASE(@key,Name)),    
            @AwardValue = CONVERT(NUMERIC, ISNULL(convert(VARCHAR(500),DECRYPTBYPASSPHRASE(@key,RSU_Deferred_Cash_USD)),0)) 
            + CONVERT(NUMERIC,ISNULL(convert(VARCHAR(500),DECRYPTBYPASSPHRASE(@key,Discretionary_SICP_Deferred)),0)) * ER.Exchange_Rate ,       
            @NumberofRSU = 
            convert(decimal(18,3),
            convert(decimal(18,3),
            CONVERT(decimal(18,3), ISNULL(convert(VARCHAR(500),DECRYPTBYPASSPHRASE(@key,Discretionary_SICP_Deferred)),0)) * ER.Exchange_Rate +
            CONVERT(decimal(18,3), ISNULL(convert(VARCHAR(500),DECRYPTBYPASSPHRASE(@key,RSU_Deferred_Cash_USD)),0)))
            /
            CONVERT(decimal(18,3), ISNULL(@AwardPrice,0)))
            --convert(NUMERIC,CONVERT(NUMERIC, ISNULL(convert(VARCHAR(500),DECRYPTBYPASSPHRASE(@key,RSU_Deferred_Cash_USD)),0))/ CONVERT(NUMERIC, ISNULL(1,0)))
        FROM 
        COMP_REC_ENC    CRE
        Inner Join [dbo].[Exchange_Rates] ER On convert(VARCHAR(500),DECRYPTBYPASSPHRASE(@key,CRE.Local_Currency_Code)) = ER.Currency_Code and CRE.Year = ER.Year
        Where CRE.[YEAR] = dbo.getCurrentYear() AND convert(VARCHAR(500),DECRYPTBYPASSPHRASE(@key,Award_Type)) ='RSU' 
        AND EMPLID = @Emplid
        UNION
            SELECT
        UID,
        MAX(CASE WHEN "KEY" = 'txtHeader'        THEN VALUE END) AS txtHeader,
        MAX(CASE WHEN "KEY" = 'txtHeaderNote' THEN VALUE END) AS txtHeaderNote,
        MAX(CASE WHEN "KEY" = 'txtAwardValue'       THEN VALUE END) AS txtAwardValue,
        MAX(CASE WHEN "KEY" = 'txtPerformancePeriod'    THEN VALUE END) AS txtPerformancePeriod,
        MAX(CASE WHEN "KEY" = 'txtScheduledVesting' THEN VALUE END) AS txtScheduledVesting,
        MAX(CASE WHEN "KEY" = 'txtDividendEquivalents'       THEN VALUE END) AS txtDividendEquivalents,
        MAX(CASE WHEN "KEY" = 'txtPaymentDate'        THEN VALUE END) AS txtPaymentDate,
        MAX(CASE WHEN "KEY" = 'txtTerminationReason1' THEN VALUE END) AS txtTerminationReason1,
        MAX(CASE WHEN "KEY" = 'txtUnvestedRSU1'       THEN VALUE END) AS txtUnvestedRSU1,
        MAX(CASE WHEN "KEY" = 'txtUnvestedRSU2'        THEN VALUE END) AS txtUnvestedRSU2,
        MAX(CASE WHEN "KEY" = 'txtTerminationReason2' THEN VALUE END) AS txtTerminationReason2,
        MAX(CASE WHEN "KEY" = 'txtUnvestedRSU3'       THEN VALUE END) AS txtUnvestedRSU3,
        MAX(CASE WHEN "KEY" = 'txtUnvestedRSU4'       THEN VALUE END) AS txtUnvestedRSU4,
        MAX(CASE WHEN "KEY" = 'txtTerminationReason3'        THEN VALUE END) AS txtTerminationReason3,
        MAX(CASE WHEN "KEY" = 'txtTerminationReason4' THEN VALUE END) AS txtTerminationReason4,
        MAX(CASE WHEN "KEY" = 'txtNonCompetition'       THEN VALUE END) AS txtNonCompetition,
        MAX(CASE WHEN "KEY" = 'txtNonSolicitation'       THEN VALUE END) AS txtNonSolicitation,
        MAX(CASE WHEN "KEY" = 'txtInteraction'       THEN VALUE END) AS txtInteraction,
        MAX(CASE WHEN "KEY" = 'txtCancellation'        THEN VALUE END) AS txtCancellation,
        MAX(CASE WHEN "KEY" = 'txtRegRequirement' THEN VALUE END) AS txtRegRequirement,
        MAX(CASE WHEN "KEY" = 'txtFootNote'       THEN VALUE END) AS txtFootNote,
        MAX(CASE WHEN "KEY" = 'txtFooter'       THEN VALUE END) AS txtFooter

    FROM COMP_STATEMENT
        WHERE UID = 6
    GROUP BY
        UID

    END

你不能这样做：

SELECT @a = a
  FROM x
  UNION
  SELECT b
  FROM y

你必须这样做：

SELECT
  @a = zz.z
FROM
(
  SELECT a as z
  FROM x
  UNION
  SELECT b
  FROM y
) zz

别名不重要。。我只是把它们放在那里，以明确区分不同的值，并显示所分配的内容
但是仔细想想。。如果联合查询返回多行，您知道将得到哪一行吗？变量只能保存一行值。这样做可能更好。 @a = MAX(zz.z) 所以你肯定会得到最高值，或者最低值。。。或者精心设计子查询，使其只返回一行
根据评论：
你误会了工会。第一个查询定义列名：

SELECT 1 as x UNION SELECT 2 UNION SELECT 3

x
----
1
2
3

一列，称为x，3行。正如我所说的，您只能将一个值赋给一个变量（除非您的变量是表）。我所知道的任何编程语言都不会让你声明一个字符串变量并给它赋两个值，这正是你所要做的

SELECT @a = n FROM (SELECT 'Jeffrey' as n UNION SELECT 'Caius')

union子查询的结果是一组两行，你的名字和我的名字。你必须猜测哪一行会出现在 @a 变量-这真的是一个猜测。它可能是结果集中的最后一个，不管db今天给出结果的顺序是什么
如果要捕获所有行，可以创建一个表变量或将它们放入临时表中

在包含顶级union intersect或except运算符的语句中不允许sql变量赋值

1条答案

相关问题

热门标签

最新问答