无法使用as设置新列名

14ifxucb  于 2021-07-29  发布在  Java
关注(0)|答案(1)|浏览(291)

我正在尝试为各自的 IIF 循环,在这个过程中,给它们一个新的列名,以便在输出中很好地打印出来。但当我试图执行时,我发现了一个语法错误:
关键字“as”附近的语法不正确。
我试着把它取下来 AS ... 从每行 IIF 语句,并且我可以成功地检索结果—只需不使用列名即可。
这是我删除前的问题 AS :

SELECT product.productName, price.basePrice, price.promotionValid, discount.finalPrice,
    IIF(((DATEDIFF(dd,startPromotion,GETDATE()) >= 0) AND (DATEDIFF(dd,GETDATE(),endPromotion) >= 0)),
        IIF(price.promotionValid = 0, price.basePrice,
            IIF(discount.promotionType = 0 AND discount.discountMethod = 0, (discount.finalPrice - price.basePrice) AS currencyDiscount,
                IIF(discount.promotionType = 0 AND discount.discountMethod = 1, (price.basePrice / discount.finalPrice)*100 AS percentageDiscount,
                    IIF(discount.promotionType = 1, (discount.finalPrice/discount.bundleQuantity) AS unitPrice, price.basePrice)))), price.basePrice)
FROM product
INNER JOIN price ON price.productName = product.productName
INNER JOIN discount ON price.productName = discount.productName
41zrol4v

41zrol4v1#

不能有条件地为列指定列名。假设得到三行结果。在每一行中,计算出不同的iif结果,因此其含义可能不同。那么应该如何调用包含这些值的列呢?
可以包括所有三个附加列。或者可以创建一个“generic”列来保存“calculation”结果。
无论哪种方式,您都需要一个额外的列来指示结果类型(基价、货币折扣、百分比折扣或单价)。
为了得到一些启发,我在这里加入了我对你的质疑的变体。它包括上述所有策略。注意,我首先转换了 IIF s到a CASE -阻止。我试着尽可能小心,但是我没有测试这个查询,所以它可能包含bug。

SELECT
  product.productName,
  price.basePrice,
  price.promotionValid,
  discount.finalPrice,
  -- separate calculation result fields:
  discount.finalPrice - price.basePrice AS currencyDiscount,
  (price.basePrice / discount.finalPrice) * 100 AS percentageDiscount,
  discount.finalPrice / discount.bundleQuantity AS unitPrice,
  -- single column containing the conditional calculation result:
  CASE
      WHEN DATEDIFF(dd, startPromotion, GETDATE()) >= 0 AND DATEDIFF(dd, GETDATE(), endPromotion) >= 0 THEN price.basePrice
      WHEN price.promotionValid = 0 THEN price.basePrice
      WHEN discount.promotionType = 0 AND discount.discountMethod = 0 THEN discount.finalPrice - price.basePrice --currencyDiscount
      WHEN discount.promotionType = 0 AND discount.discountMethod = 1 THEN (price.basePrice / discount.finalPrice) * 100 --percentageDiscount
      WHEN discount.promotionType = 1 THEN discount.finalPrice / discount.bundleQuantity --unitPrice
      ELSE price.basePrice
  END AS calculationResult,
  -- column indicating the effective result type:
  CASE
      WHEN DATEDIFF(dd, startPromotion, GETDATE()) >= 0 AND DATEDIFF(dd, GETDATE(), endPromotion) >= 0 THEN 'base price (promotion not active)'
      WHEN price.promotionValid = 0 THEN 'base price (promotion not valid)'
      WHEN discount.promotionType = 0 AND discount.discountMethod = 0 THEN 'currency discount'
      WHEN discount.promotionType = 0 AND discount.discountMethod = 1 THEN 'percentage discount'
      WHEN discount.promotionType = 1 THEN 'unit price'
      ELSE 'base price (no promotion found)'
  END AS calculationResultType
FROM
  product
  INNER JOIN price ON price.productName = product.productName
  INNER JOIN discount ON price.productName = discount.productName

相关问题