Select using NVL wrote letters. Why?

Question

I wrote this select:

    SELECT 
    nvl(SUM(vl_item),0)     AS SUM_VALOR_ITENS
   ,nvl(SUM(vl_discount),0) AS SUM_VALOR_DESCONTO
   ,nvl(SUM(qty_item) is null   ,0,0)    AS SUM_QTD_ITENS
   ,COUNT(DISTINCT cod_product_rms) AS QTD_ITENS_UNICOS
   FROM db.my_table
   where SAFRA = 202006

When I ran on DBeaver, the result was 0 (zero), like I expected. But, the result when I ran this SQL in a HQL file using Java was "0E-10", "0E-18" and "0E-18" respectively. I don't understand why I received this result. Somebody can help me?

Does this answer your question? [Hive Converting from Double to String Not in Scientific](https://stackoverflow.com/questions/32576187/hive-converting-from-double-to-string-not-in-scientific) — Digvijay S, Jun 18 '20 at 14:05
Ok, I understand now. The question now is: How can include a treatment to convert the NULL value in zero integer (or decimal)? — Fernando Delago, Jun 18 '20 at 14:36

OrangeSubmarine121 · Answer 1 · 2020-06-18T14:52:51.227

I'd suggest using coalesce, nvl will not work for the 3rd column, as NVL only takes 2 arguments (this one takes 3), whereas coalesce can take as many as you wish.

    SELECT 
    coalesce(SUM(vl_item),0)     AS SUM_VALOR_ITENS
   ,coalesce(SUM(vl_discount),0) AS SUM_VALOR_DESCONTO
   ,coalesce(SUM(qty_item) is null   ,0,0)    AS SUM_QTD_ITENS
   ,COUNT(DISTINCT cod_product_rms) AS QTD_ITENS_UNICOS
   FROM db.my_table
   where SAFRA = 202006

Select using NVL wrote letters. Why?

1 Answers1