Re: isnull() function in pgAdmin3

On 10/03/2016 06:39 AM, dudedoe01 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

One more time, Postgres does not have an isnull() function in any
version AFAIK. You need to use IS NULL:

https://www.postgresql.org/message-id/MWHPR07MB28777547180DC028EF812E10DACC0%40MWHPR07MB2877.namprd07.prod.outlook.com

https://www.postgresql.org/message-id/80becd5e-2fcf-5660-574b-82bcb040e18a%40aklaver.com

https://www.postgresql.org/message-id/CACjxUsOkFgiGKRjmhWonE3yfiPpsrn9dsmHW%2B9KaZSh9RWB_ow%40mail.gmail.com

> 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 I am following correctly:

(
CASE
WHEN
(`s`.`Funding_Date` = '')
AND
(
(`s`.`Actual_Close_Date` IS NULL)
OR
(`s`.`Actual_Close_Date` = '')
)
THEN
'RPG_INV'
WHEN
(
(`s`.`Funding_Date` IS NULL)
OR
(`s`.`Funding_Date` <> '')
)

AND
(
(`s`.`Actual_Close_Date` IS NULL)
OR
(`s`.`Actual_Close_Date` = '')
)
THEN
'Builder_Inventory'
ELSE
'Owner_Inventory'
END
)
AS
`Lot_Status`
>
>
>
> --
> View this message in context: http://postgresql.nabble.com/isnull-function-in-pgAdmin3-tp5923122p5924161.html
> Sent from the PostgreSQL - general mailing list archive at Nabble.com.
>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com