2个子查询的字段怎么相减，可能有空值

dh_n

1. select ERP_MM_StockPlanTemp.*,ERP_MM_BOMDetail.fRationNum as fRationNum,ERP_MeasurementUnit.fName as fName,ERP_StockArchives.fStockName as fStockName,ERP_StockArchives.fNormsType as fNormsType,ERP_WH_Summary.fbalanceAmount as fbalanceAmount,(select  sum(ERP_PU_PurchaseList.fPurchaseNumber) as custom8 from ERP_PU_PurchaseList ERP_PU_PurchaseList optional join ERP_PU_BuyOrder ERP_PU_BuyOrder on ERP_PU_PurchaseList.fMainID = ERP_PU_BuyOrder  where  ERP_PU_PurchaseList.fStockCode = ERP_MM_StockPlanTemp.fMaterielCode AND ERP_PU_BuyOrder.fBillStatus = '批准' ) as custom7,(select  sum(ERP_MM_ResourceAllocation.fAllotNumber) as custom10 from ERP_MM_ResourceAllocation ERP_MM_ResourceAllocation optional join ERP_PU_PurchaseList ERP_PU_PurchaseList on ERP_PU_PurchaseList.fStockCode = ERP_MM_ResourceAllocation.fMaterielCode optional join ERP_PU_BuyOrder ERP_PU_BuyOrder on ERP_PU_PurchaseList.fMainID = ERP_PU_BuyOrder  where  ERP_MM_ResourceAllocation.fResourcesNO = ERP_PU_BuyOrder AND ERP_MM_ResourceAllocation.fIsClose = '否' AND ERP_PU_PurchaseList.fStockCode = ERP_MM_StockPlanTemp.fMaterielCode ) as custom9,custom7 - custom9 as custom2
   
2.     from ERP_MM_StockPlanTemp ERP_MM_StockPlanTemp
   
3.      optional  join ERP_MM_BOMMaster ERP_MM_BOMMaster on ERP_MM_StockPlanTemp.fPartNumber = ERP_MM_BOMMaster.fMaterielCode AND ERP_MM_BOMMaster.fDrawingVersion = ERP_MM_StockPlanTemp.fPartRev
   
4.      optional  join ERP_MM_BOMDetail ERP_MM_BOMDetail on ERP_MM_BOMMaster = ERP_MM_BOMDetail.fMainID AND ERP_MM_BOMDetail.fMaterielCode = ERP_MM_StockPlanTemp.fMaterielCode
   
5.      optional  join ERP_MeasurementUnit ERP_MeasurementUnit on ERP_MM_BOMDetail.fMainMeasurementUnitID = ERP_MeasurementUnit
   
6.      optional  join ERP_StockArchives ERP_StockArchives on ERP_MM_StockPlanTemp.fMaterielCode = ERP_StockArchives.fStockCode
   
7.      optional  join ERP_WH_Summary ERP_WH_Summary on ERP_MM_StockPlanTemp.fMaterielCode = ERP_WH_Summary.fStockCode

复制代码

custom2=coustom7-custom9  ksql编辑器里应该怎么写，类型为float

jishuang

这样复杂的语句推荐用sql语句实现

编辑中不能设置的，可以到源码中去修改

dh_n

SELECT isnull(A,0)-isnull(B,0) AS 结果 FROM 表  --sqlserver语法
SELECT nvl(A,0)-nvl(B,0) AS 结果 FROM 表  --Oracle语法
SELECT ifnull(A,0)-ifnull(B,0) AS 结果 FROM 表  --mysql语法
在orcl里可以用第二个，我在源码里写第一个为什么不可以

jishuang

ksql中可以用SQL.调用数据库本地函数

星星星

jishuang 发表于 2017-7-21 16:30
ksql中可以用SQL.调用数据库本地函数

使用SQL.IFNULL时  报错，提示IFNULL不是可识别的内部函数名称