将Excel公式转换为SQL语法计算
将Excel公式转换为SQL语法计算
提问于 2018-04-06 02:28:36
我需要帮助解决我的问题，将Excel公式转换为SQL。
公式如下：
=IF(
([@[Net Available Stock Quantity]]+[@Subcon]+[@[In Progress WIP]]+
IFERROR([@[Raw Material In Store]]/[@[Demand Material]],0)+
IFERROR([@[Outstanding Raw Material]]/[@[Demand Material]],0)+
[@[Outstanding WIP]])>[@[Maximun Stock]],
([@[Net Available Stock Quantity]]+[@Subcon]+[@[In Progress WIP]]+
IFERROR([@[Raw Material In Store]]/[@[Demand Material]],0)+
IFERROR([@[Outstanding Raw Material]]/[@[Demand Material]],0)+
[@[Outstanding WIP]]-[@[Maximun Stock]]),
IF(([@[Net Available Stock Quantity]]+[@Subcon]+[@[In Progress WIP]]+
IFERROR([@[Raw Material In Store]]/[@[Demand Material]],0)+
IFERROR([@[Outstanding Raw Material]]/[@[Demand Material]],0)+[@[Outstanding WIP]])<[@[Minimum Stock]],
-[@[Minimum Stock]]+([@[Net Available Stock Quantity]]+[@Subcon]+[@[In Progress WIP]]+
IFERROR([@[Raw Material In Store]]/[@[Demand Material]],0)+
IFERROR([@[Outstanding Raw Material]]/[@[Demand Material]],0)+[@[Outstanding WIP]]),0)
)以下是我所做的：
USE myDB
SELECT [Item No],
CASE
WHEN ([Net Available Stock Quantity]+Subcon+[In Progress WIP]+CASE (
WHEN ([Raw Material In Store] AND [Demand Material] != 0 THEN [Raw Material In Store]/[Demand Material] ELSE 0) +
WHEN ([Outstanding Raw Material] AND [Demand Material] != 0 THEN [Outstanding Raw Material]/[Demand Material] ELSE 0) +
[Outstanding WIP]) > [Maximum Stock])
THEN ([Net Available Stock]+Subcon+[Outstanding WIP]+CASE (
WHEN ([Raw Material] AND [Demand Material] != 0 THEN [Raw Material]/[Demand Material] ELSE 0) +
WHEN ([Outstanding Raw Material] AND [Demand Material] != 0 THEN [Outstanding Raw Material]/[Demand Material] ELSE 0) +
[Outstanding WIP) - [Maximum Stock])
ELSE (
CASE
WHEN([Net Available Stock Quantity]+Subcon+[Outstanding WIP]+CASE (
WHEN ([Raw Material In Store AND [Demand Material] != 0 THEN [Raw Material In Store]/[Demand Material] ELSE 0) +
WHEN ([Outstanding Raw Material] AND [Demand Material] != 0 THEN [Outstanding Raw Material]/[Demand Material] ELSE 0) +
[Outstanding WIP] < [Minimum Stock])
THEN ([Minimum Stock]+[Net Available Stock Quantity]+Subcon+[Outstanding WIP]+CASE (
WHEN ([Raw Material In Store] AND [Demand Material] != 0 THEN [Raw Material In Store]/[Demand Material] ELSE 0) +
WHEN ([Outstanding Raw Material] AND [Demand Material] != 0 THEN [Outstanding Raw material]/[Demand Material] ELSE 0) +
[Outstanding WIP])
END AS [Final Stock Over Max],
FROM myTable所以，当我粘贴到Server查询时，
- 在项目无无效列名上。(在某些名字上有很多像这样的错误)
- 第二次无效语法。
- 关于“其他无效语法”的案例。
- 作为无效语法。
我使用THIS LINK作为向导来转换excel公式。
你们觉得我做错了什么？我试着转换了两天，我已经尝试了很多例子，但是仍然出现了错误。
更新：
myTable结构：
[Item No] nchar(24)
Description nchar(60)
UOM nchar(10)
Stock int
Sales int
[Minimum Stock] int
[Maximum Stock] int
[Subcon Code] char(24)
[Raw Material] char(24)
[Description Material] nchar(60)
[Demand Material] char(10)
[In Progress WIP] char(10)
Subcon int
[Net Available Stock Quantity] int
[Stock Over Maximum] int
[Raw Material Outstanding] int
[Raw Material In Store] int
[Outstanding WIP] int回答 1
Stack Overflow用户
回答已采纳
发布于 2018-04-06 03:39:56
试试这段代码，随时重新排序.
CASE
WHEN ([Net Available Stock Quantity] + [Subcon] + [In Progress WIP] +
COALESCE([Raw Material In Store] / NULLIF([Demand Material], 0), 0) +
COALESCE([Outstanding Raw Material] / NULLIF([Demand Material], 0), 0) +
[Outstanding WIP]) > [Maximun Stock] THEN CASE
WHEN
([Net Available Stock Quantity] + [Subcon] + [In Progress WIP] +
COALESCE([Raw Material In Store] / NULLIF([Demand Material], 0), 0) +
COALESCE([Outstanding Raw Material] / NULLIF([Demand Material], 0), 0) + [Outstanding WIP]) < [Minimum Stock] THEN [Minimum Stock] + ([Net Available Stock Quantity] + [Subcon] + [In Progress WIP] +
COALESCE([Raw Material In Store] / NULLIF([Demand Material], 0), 0) +
COALESCE([Outstanding Raw Material] / NULLIF([Demand Material], 0), 0) + +[Outstanding WIP])
ELSE ([Net Available Stock Quantity] + [Subcon] + [In Progress WIP] +
COALESCE([Raw Material In Store] / NULLIF([Demand Material], 0), 0) +
COALESCE([Outstanding Raw Material] / NULLIF([Demand Material], 0), 0) +
[Outstanding WIP] - [Maximun Stock])
END
ELSE 0
END页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/49684339
复制相关文章
相似问题
腾讯云开发者
