Re: isnull() function in pgAdmin3 - Mailing list pgsql-general

From David G. Johnston
Subject Re: isnull() function in pgAdmin3
Date
Msg-id CAKFQuwYe=mFh5JM8sqCpUKob_ittkGa-C=1hpAHoU=MA1SrGtw@mail.gmail.com
Whole thread Raw
In response to Re: isnull() function in pgAdmin3  (dudedoe01 <marsalanaq@gmail.com>)
List pgsql-general
On Mon, Oct 3, 2016 at 6:39 AM, dudedoe01 <marsalanaq@gmail.com> wrote:
What is the most feasible way to emulate the below MySQL function into
postgreSQL. Since the isnull() function is no longer supported in 9.6
version. I have tried every trick in the hat to get the desired results.
Still 'RPG INV' doesn't show only the other two then options show up.

(case
            when
                ((`s`.`Funding_Date` = '')
                    and (isnull(`s`.`Actual_Close_Date`)
                    or (`s`.`Actual_Close_Date` = '')))
            then
                'RPG_INV'
            when
                ((isnull(`s`.`Funding_Date`)
                    or (`s`.`Funding_Date` <> ''))
                    and ((`s`.`Actual_Close_Date` = '')
                    or isnull(`s`.`Actual_Close_Date`)))
            then
                'Builder_Inventory'
            else 'Owner_Inventory'
        end) AS `Lot_Status`


​If you include something like the following people are going to be more willing to provide help and better able to provide good help.

WITH dt (funding_date, actual_date) AS (
VALUES (null, null), ('X', 'X'), 
       (null, ''), (null, 'X'),
       ('', null), ('X', null)
)
SELECT funding_date, actual_date, 
       CASE WHEN funding_date IS NULL
                 AND actual_date IS NULL
            THEN 'Both Null'
            ELSE 'write more WHEN statements...'
        END 
FROM dt
​;

That's a self-contained example and it does wonders for problem solving.

David J.

pgsql-general by date:

Previous
From: otar shavadze
Date:
Subject: Re: [GENERAL] Understanding “max_wal_size” and “min_wal_size” parameters default values from postgresql.conf file
Next
From: "David G. Johnston"
Date:
Subject: Make psql print number values right-aligned with locale group separator?