使用IFNULL语句中的Big-Query中使用If-Else语句

发布于 2025-02-09 02:27:13 字数 1371 浏览 3 评论 0原文

我在bigquery中有一个微妙的困境，其中有follwing sql语句：

ROUND(SUM(r.DeliveredQuantity * IFNULL(a.DirectCost,a.PurchasePrice))) AS TotalCost,

我需要更改它以检查某些文章是否存在，如果是，我必须做一个表达式，如果不是另一种表达式。这是我的检查，

IF(a.ArticleNumber NOT IN ("204","204.2","204.3","204.4","204.5","204.6","204.7")

是否是我的检查不存在，然后我必须在查询之后运行：

ROUND(SUM(r.DeliveredQuantity * IFNULL(a.DirectCost,a.PurchasePrice)))

否则（这是我想不幸的部分出现的地方）我必须运行这样的东西：

ROUND(SUM(r.DeliveredQuantity * IFNULL(a.DirectCost,a.PurchasePrice) / (a.SalesPrice * i.CurrencyRate)))

所以到目前为止，我拥有的整个代码片段是：

IF(a.ArticleNumber NOT IN ("204","204.2","204.3","204.4","204.5","204.6","204.7"),
   ROUND(SUM(r.DeliveredQuantity * IFNULL(a.DirectCost,a.PurchasePrice))),
   ROUND(SUM(r.DeliveredQuantity * IFNULL(a.DirectCost,a.PurchasePrice) / (a.SalesPrice * i.CurrencyRate)))
  ) AS TG,

结果是一个错误。：

division by zero: 0 / 0

不幸的是，我必须运行IFNULL，因为我的用户在数据输入中不一致。但是，即使仅在ifnull内使用任何一种选择，我也会得到相同的响应，即零：0/0

endresult是，如果存在某些rticles，那么我需要得到包括i.currencyrate在内的那些，否则没有i .CurrencyRate。有什么想法吗？

PS！我遵循此 tutorial

原文

I have a delicate dilemma with follwing SQL statement in Bigquery:

ROUND(SUM(r.DeliveredQuantity * IFNULL(a.DirectCost,a.PurchasePrice))) AS TotalCost,

I need to change it to check if some articles are present or not and if they are, I have to do one expressiona and if not another expression.This is my check

IF(a.ArticleNumber NOT IN ("204","204.2","204.3","204.4","204.5","204.6","204.7")

If those articles are not present then I have to run following query:

ROUND(SUM(r.DeliveredQuantity * IFNULL(a.DirectCost,a.PurchasePrice)))

Otherwise ( and here is where the unfortunate part comes in I guess ) I have to run something like this:

ROUND(SUM(r.DeliveredQuantity * IFNULL(a.DirectCost,a.PurchasePrice) / (a.SalesPrice * i.CurrencyRate)))

So the whole code snippet I have so far is:

IF(a.ArticleNumber NOT IN ("204","204.2","204.3","204.4","204.5","204.6","204.7"),
   ROUND(SUM(r.DeliveredQuantity * IFNULL(a.DirectCost,a.PurchasePrice))),
   ROUND(SUM(r.DeliveredQuantity * IFNULL(a.DirectCost,a.PurchasePrice) / (a.SalesPrice * i.CurrencyRate)))
  ) AS TG,

The result is a error saying:

division by zero: 0 / 0

The IFNULL I have to run unfortunately since my users have not be consistent in their data input. But even when only using one of either choices inside the IFNULL, I get the same response saying division by zero: 0/0

Endresult is that IF certaina rticles are present, then I need to get those including the i.CurrencyRate, otherwise without i.CurrencyRate. Any ideas?

PS! I followed this tutorial

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

清音悠歌 2025-02-16 02:27:13


IF(a.ArticleNumber NOT IN ("204","204.2","204.3","204.4","204.5","204.6","204.7"),
   ROUND(SUM(r.DeliveredQuantity * IFNULL(a.DirectCost,a.PurchasePrice))),
   ROUND(SUM(SAFE_DIVIDE((r.DeliveredQuantity * IFNULL(a.DirectCost,a.PurchasePrice)) , (a.SalesPrice * i.CurrencyRate))))
  ) AS TG,


IF(a.ArticleNumber NOT IN ("204","204.2","204.3","204.4","204.5","204.6","204.7"),
   ROUND(SUM(r.DeliveredQuantity * IFNULL(a.DirectCost,a.PurchasePrice))),
   ROUND(SUM(SAFE_DIVIDE((r.DeliveredQuantity * IFNULL(a.DirectCost,a.PurchasePrice)) , (a.SalesPrice * i.CurrencyRate))))
  ) AS TG,

回复收藏 0 原文

~没有更多了~