尝试计算两列时出错

Question

当我运行查询时，我收到精度错误。它来自我的函数之后 *

我尝试将列数据类型更改为整数并to_number。 它是一个瓦尔查尔。

SELECT * , (tableb.difference - tablea.order_bloomnet_msg_price) As Answer
From 
  (Select order_bloomnet_msg_type, order_bloomnet_msg_text, order_bloomnet_num,order_bloomnet_msg_price, order_bloomnet_id  
   FROM pos.order_bloomnet_msg 
   Where order_bloomnet_msg_type = 'ORDR'     
   Order By order_bloomnet_num) as TableA 
Inner Join 
  (Select Trunc(order_bloomnet_msg_sent) as Date,order_bloomnet_msg_username, order_bloomnet_msg_price as difference, order_bloomnet_num, order_bloomnet_id  
   FROM pos.order_bloomnet_msg     
   Where order_bloomnet_msg_type = 'PCHG'     
   Order By order_bloomnet_num) as TableB ON
      (TableA.order_bloomnet_num = TableB.order_bloomnet_num and
       TableA.order_bloomnet_id = TableB.order_bloomnet_ID ) 
Where order_bloomnet_msg_price is not Null and TableB.Date = '2019/01/03'

我希望最后一列具有 TableB.difference 的结果，并order_bloomnet_msg_price

Answer 1

您应该强制转换值以避免错误

喜欢这个

SELECT * , (tableb.difference::float - tablea.order_bloomnet_msg_price::float) As Answer
From 
  (Select order_bloomnet_msg_type, order_bloomnet_msg_text, order_bloomnet_num,order_bloomnet_msg_price, order_bloomnet_id  
   FROM pos.order_bloomnet_msg 
   Where order_bloomnet_msg_type = 'ORDR'     
   Order By order_bloomnet_num) as TableA 
Inner Join 
  (Select Trunc(order_bloomnet_msg_sent) as Date,order_bloomnet_msg_username, order_bloomnet_msg_price as difference, order_bloomnet_num, order_bloomnet_id  
   FROM pos.order_bloomnet_msg     
   Where order_bloomnet_msg_type = 'PCHG'     
   Order By order_bloomnet_num) as TableB ON
      (TableA.order_bloomnet_num = TableB.order_bloomnet_num and
       TableA.order_bloomnet_id = TableB.order_bloomnet_ID ) 
Where order_bloomnet_msg_price is not Null and TableB.Date = '2019/01/03'