SQL - Integer and Decimal Number Conversion

Question

I have a field qtfornecedor of the kind Numeric(10, 3). When its stored value is 10, he returns me in consultation as '10,000'.

I tried to manipulate the value with a IIF, works for when the stored value is integer, example 10. But for cases where the stored value is decimal, such as '10,000', he returns me whole 10.

SELECT 
    cdempresa                               
    ,nmempresa
    ,cdproduto
    ,nmproduto      
    ,IIF(RIGHT(qtfornecedor,3) = 0, FORMAT(qtfornecedor, '#'), FORMAT(qtfornecedor, '##########.###')) as qtfornecedor                            
    ,unidade
    ,vlfornecedor                                     
FROM 
    [dbo].[negociacao]
WHERE 
    nrochamado  = 11104 

Answer 1

Try to apply a CAST() at the consultation, specifying that three decimal places:

SELECT 
    cdempresa                               
    ,nmempresa
    ,cdproduto
    ,nmproduto      
    ,CAST(qtfornecedor AS DECIMAL(10,3)) as qtfornecedor                            
    ,unidade
    ,vlfornecedor                                     
FROM 
    [dbo].[negociacao]
WHERE 
    nrochamado  = 11104 

Answer 2

I don’t know if I understand the question correctly, but try changing the condition used in IIF as follows::

,IIF(qtfornecedor = floor(qtfornecedor),

In this suggestion it is being checked whether the column value is equal to the integer value of the column that is returned by the Floor function.

I hope it helps