CODEWITHSUNDEEP
sqlamazon-redshift
Thomas Stevens
Thomas Stevens

Reputation: 33

Getting An Error When trying to Calculate two columns

When I run my query I am getting a Precision Error. It is coming from my function after *

I tried changing the columns data type to an integer and to_number. it is a varchar. 

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'

I want the final column to have the result of the TableB.difference and order_bloomnet_msg_price

Upvotes: 0

Views: 37

Answers (1)

Jon Scott
Jon Scott

Reputation: 4354

You should cast your values to avoid the error

Like this

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'

Upvotes: 1

Related Questions