0

Hi I am using postgresql in my project. I want to make a validation that if query returns negative then i want to change output to null.

(to_char (NULLIF(coalesce(0::numeric,0) - 
(coalesce(26776.94::numeric,0) - 
(coalesce(2760::numeric,0) + (coalesce(1800::numeric,0)))),0)::numeric/3600,'FM99,999,999,999.00'))::character varying as clds

it is giving -6.17 as output , I want to make it null

2
  • Why are you trying to treat NUMERICs as VARCHARs?
    – Vérace
    Aug 7, 2020 at 11:56
  • @Vérace is GREATEST good approach ? Aug 7, 2020 at 12:56

1 Answer 1

1

You could do something like this:

CASE
  LEFT
  (
    to_char (NULLIF(coalesce(0::numeric,0) - 
    (coalesce(26776.94::numeric,0) - 
    (coalesce(2760::numeric,0) + 
   (coalesce(1800::numeric,0)))),0)::numeric/3600,'FM99,999,999,999.00'))::VARCHAR
    ,1                -- <<<---- the '1' gets the minus sign from the string!
  )
  = '-' THEN NULL
  ELSE 
    to_char (NULLIF(coalesce(0::numeric,0) - 
    (coalesce(26776.94::numeric,0) - 
    (coalesce(2760::numeric,0) + 
    (coalesce(1800::numeric,0)))),0)::numeric/3600,'FM99,999,999,999.00'))::VARCHAR
END AS clds

I have to say though that this has to be one of the most horrible SQL constructs that I've ever seen!

I would give serious consideration to devoting some (lots?) of effort towards an in-depth refactoring!

2
  • I tried with GREATEST , is it good approach ? Aug 7, 2020 at 12:15
  • @humbertoherrara - I'm afraid that I don't exactly understand where GREATEST could be of use here - maybe you could expand on that in the question and leave a comment for me (using @Vérace) so that I can see what you're driving at?
    – Vérace
    Aug 8, 2020 at 10:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.