0

I´m trying to do the select function of the image below, but the result for "perc_vol_lei" is zero, that is incorrect.

select to_char (tb_esq.datahora, 'yyyy-mm-dd') as data_esq, tb_esq.tick, tb_esq.variacao, tb_esq.quantidade as qtt_26, tb_dir.quantidade as qtt_0,
     (tb_esq.quantidade - tb_dir.quantidade) as volume_no_leilao,
     ((tb_esq.quantidade - tb_dir.quantidade)/tb_esq.quantidade) as perc_vol_lei  

from tb_registros as tb_esq 
     join tb_registros as tb_dir on tb_esq.tick = tb_dir.tick 
        and to_char(tb_esq.datahora,'yyyy-mm-dd') = to_char(tb_dir.datahora,'yyyy-mm-dd')
    
        where (tb_esq.quantidade - tb_dir.quantidade) > 50000 and tb_esq.refer =26 and tb_dir.refer = 0 
                AND tb_esq.tick <>'DOLFUTV' and tb_esq.tick <>'IBOV' and tb_esq.tick <>'WDOFUT'and tb_esq.tick <>'DOLFUT'
order by volume_no_leilao desc`

Anyone can help me?

enter image description here

Marcel
  • 1
  • 3
  • 4
    Please do **not** post code as images. See here for more details why: http://meta.stackoverflow.com/questions/285551 –  Aug 24 '22 at 14:01
  • It's `INTEGER` division. You need to cast your values to `DOUBLE PRECISION` or `NUMERIC`. Ex: `::DOUBLE PRECISION` – J Spratt Aug 24 '22 at 14:05
  • @JSpratt a FLOAT? Are you sure you want all the problems related to a float? That's almost a random result... – Frank Heikens Aug 24 '22 at 14:06
  • Yeah... I'll remove that actually. – J Spratt Aug 24 '22 at 14:07
  • JSpratt, @FrankHeikens it´s not working yet. I tried with the following three options: first: ((tb_esq.quantidade - tb_dir.quantidade)/tb_esq.quantidade):: double precision as perc_vol_lei / second: round(((tb_esq.quantidade - tb_dir.quantidade)/tb_esq.quantidade):: decimal, 5) as perc_vol_lei / third: ((tb_esq.quantidade - tb_dir.quantidade)/tb_esq.quantidade):: numeric as perc_vol_lei. And the result are the same. I still needing help. – Marcel Aug 24 '22 at 15:03
  • Seems you need to force numeric operations not just cast the result. Perhaps: `(tb_esq.quantidade - tb_dir.quantidade)/tb_esq.quantida::numeric)`. – Belayer Aug 24 '22 at 15:27
  • It works Belayer, thank you so much! The ")" was in the wrong place. – Marcel Aug 24 '22 at 15:37

0 Answers0