compare case result

Asked

Viewed 36 times

-2

I have a table customedio guy numeric, and when the value is less than 0, I want you to bring the message "Negative value" and when it is null, in the different case of larger and smaller than 0 bring "null value".

But running the command below is giving the error:

ERROR: invalid input syntax for type Numeric: "Negative"

I believe it’s because I’m comparing a text with a numerical field.

How to solve?

select  pr.ean, upper(pr.nome) as nome, pr.unidademedida, se.quantidade, pr.customedioinicial,
(CASE  
when 
fpp.customedio>=0 then  fpp.customedio
when 
fpp.customedio<=0 then  'Negativo'

   else 'Valor nulo' end) as customedio, se.quantidade * pr.customedioinicial as Total

FROM produto PR
LEFT JOIN saldoestoque SE ON(pr.id=se.idproduto)
LEFT JOIN formacaoprecoproduto FPP ON(fpp.idproduto=pr.id)
 where se.quantidade is not  null   order by se.quantidade desc
sql

2 answers

0

I found that in the end I must convert the customedio text-based ::text

select pr.ean, upper(pr.nome) as nome, pr.unidademedida, se.quantidade, pr.customedioinicial, (CASE
when (fpp.customedio>=0) then (fpp.customedio)::text
when (fpp.customedio<=0) then (fpp.customedio)::text
ELSE 'Não definido'::text end) as customedio, se.quantidade * pr.customedioinicial as Total

FROM produto PR LEFT JOIN saldoestoque SE ON(pr.id=se.idproduto) LEFT JOIN formacaoprecoproduto FPP ON(fpp.idproduto=pr.id) where se.quantidade is not null order by se.quantidade desc

0


In Postgresql you could do:

SELECT pr.ean, upper(pr.nome) AS nome, pr.unidademedida, se.quantidade, pr.customedioinicial, 
 (CASE WHEN fpp.customedio>=0 THEN TO_CHAR(fpp.customedio, '99999') WHEN fpp.customedio<0 THEN 'Negativo' ELSE 'Valor nulo' END) AS customedio, 
 (se.quantidade * pr.customedioinicial)::TEXT AS Total
FROM produto PR LEFT JOIN saldoestoque se ON (pr.id=se.idproduto) 
                        LEFT JOIN formacaoprecoproduto fpp ON (fpp.idproduto=pr.id) 
WHERE se.quantidade IS NOT NULL
ORDER BY se.quantidade DESC;

In other DBMS you will have to use the proper formatting function.

  • This command didn’t work because it converted the decimals from my average cost of 2.0000 to 2. that would be a problem. I will try to summarize my command better. My field is NUMERIC it is not accepting I type a word. select (CASE&#xA;&#xA;when (fpp.customedio>=0) then fpp.customedio&#xA;when (fpp.customedio<=0) then 'Negativo'&#xA;ELSE 'DIFERENTE' end) as customedio&#xA;&#xA;FROM produto PR LEFT JOIN saldoestoque SE ON(pr.id=se.idproduto) LEFT JOIN formacaoprecoproduto FPP ON(fpp.idproduto=pr.id) Where se.quantidade is not null order by se.quantidade desc

  • I understood your problem so the answer was for you to convert your number to a string and then your output field would contain only string, whether the number is the alternate text. For your number to be considered to be decimal use TO_CHAR(fpp.customedio, '99999.9999') using the appropriate amount of 9’s according to the maximum content of your field.

  • Got it, man thanks a lot for the help. :-)

Browser other questions tagged sql

You are not signed in. Login or sign up in order to post.