Thread: isnull() function in pgAdmin3

isnull() function in pgAdmin3

From
dudedoe01
Date:
Hi,

I am trying to emulate the isnull() function used in MySQL into postreSQL. I
have tried different ways such is null but it's not producing the results
desired. I am doing a data migration from MySQL into postgreSQL and need
help with the isnull() in pgAdmin3.

Any assistance provided would be greatly appreciated.

Thanks,

In MySQL:

(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`,



--
View this message in context: http://postgresql.nabble.com/isnull-function-in-pgAdmin3-tp5923122.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


Re: isnull() function in pgAdmin3

From
Igor Neyman
Date:
-----Original Message-----
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of dudedoe01
Sent: Tuesday, September 27, 2016 1:05 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] isnull() function in pgAdmin3

Hi,

I am trying to emulate the isnull() function used in MySQL into postreSQL. I have tried different ways such is null but
it'snot producing the results desired. I am doing a data migration from MySQL into postgreSQL and need help with the
isnull()in pgAdmin3. 

Any assistance provided would be greatly appreciated.

Thanks,

In MySQL:

(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`,

__________________________________________________________________________________________________________


isnull(`s`.`Actual_Close_Date`) in MySQL is equivalent to:

s.actual_close_date  IS NULL  in Postgres.

What exactly didn't work for you?

Regards,
Igor Neyman



Re: isnull() function in pgAdmin3

From
Adrian Klaver
Date:
On 09/27/2016 10:04 AM, dudedoe01 wrote:
> Hi,
>
> I am trying to emulate the isnull() function used in MySQL into postreSQL. I
> have tried different ways such is null but it's not producing the results
> desired. I am doing a data migration from MySQL into postgreSQL and need
> help with the isnull() in pgAdmin3.
>
> Any assistance provided would be greatly appreciated.
>
> Thanks,
>
> In MySQL:
>
> (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`,
>
>

aklaver@test=> select 1 is null;
  ?column?
----------
  f
(1 row)

aklaver@test=> select null is null;
  ?column?
----------
  t

or

                       ^
aklaver@test=> select 1 isnull;
  ?column?
----------
  f
(1 row)

aklaver@test=> select null isnull;
  ?column?
----------
  t
(1 row)


>
> --
> View this message in context: http://postgresql.nabble.com/isnull-function-in-pgAdmin3-tp5923122.html
> Sent from the PostgreSQL - general mailing list archive at Nabble.com.
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com


Re: isnull() function in pgAdmin3

From
Kevin Grittner
Date:
On Tue, Sep 27, 2016 at 12:04 PM, dudedoe01 <marsalanaq@gmail.com> wrote:

> I am trying to emulate the isnull() function used in MySQL into postreSQL.

In the future, please describe the results you are trying to achieve
or at least describe the semantics of the function from elsewhere that
you are trying to emulate.  I had no idea what the ISNLL() function of
MySQL does; on a quick web search, it looks like you can replace:
  isnull(expression)

with:

  (expression) IS NULL


> In MySQL:
>
> (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`,

In PostgreSQL perhaps:

(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" = '')
                    or ("s"."Actual_Close_Date") is null))
            then
                'Builder_Inventory'
            else 'Owner_Inventory'
        end) AS "Lot_Status",

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


Re: isnull() function in pgAdmin3

From
dudedoe01
Date:
Thanks Kevin, Igor and Adrian. In MySQL with the isnull() function I get all
three values RPG INV, Owner Inventory, and Builder Inventory showed up in
the view while when I do the is null function in postgreSQL I only see
Builder Inventory and Owner Inventory show up in the view. I don't know why
the RPG_INV is not showing up.

In MySQL:

Owner Inventory is 4481 rows of data
Builder Inventory is 1312 rows of data
RPG_Inv is 374 rows of data

gives the total of 6167 rows

In postgreSQL:

Owner Inventory is 4521 rows of data
Builder inventory is 1646 rows of data

gives the total of 6167 rows

create view si_model as select
c."DCAD_Prop_ID" as DCAD_ID,
m."Address" as Address,
m."Addition" as Addition,
m."Block" as Block,
m."Lot" as Lot,
m."Lot_Size" as Lot_Size,
m."Lot_Type" as Lot_Type,
l."Funding_Date" as Lot_Sale_Date,
h."Actual_Close_Date" as Home_Closing_Date,
m."District" as District,
g."nhs_builder" as Builder,
g."nhs_sale_date" as NHSContractDate,
'' as "Banks & Ind. Owned Lots",
'' as "Repurchased",
m."Reserved_Lots" as Reserved,
d."Permit__" as "Permit #",
d."Permit_Date" as "Permit Date",
d."Foundation_Date" as "Foundation Date",
d."Frame_Date" as "Frame Date",
d."HCS" as HCS,
'' as "Notes_Comments",
l."Lot_Status" as "Lot Funding Status",
'' as "Property Description",
'' as "GIS Map",
d."Project_ID" as Project_ID,
(case when
        ((l."Funding_Date" = '') and ((h."Actual_Close_Date") is null
            or (h."Actual_Close_Date" = ''))) then 'RPG Inventory'
            when
                (((l."Funding_Date") is null or (l."Funding_Date" <> ''))
                    and ((h."Actual_Close_Date" = '') or
(h."Actual_Close_Date") is null))
            then
                'Builder Inventory'
            else 'Owner Occupied'
        end) AS "Lot_Status",

((case when c."DCAD_Prop_ID" = m."DCAD_Prop_ID" then 'YES' else '' end)) as
"Home Sale",
((case when m."Address" =  s."Address_of_Inventory_Home" then 'C Spec' else
'' end)) as "Current Specs",
((case when g."nhs_address" = m."Address" and g."nhs_can" = 'false' and
g."nhs_build_spec" = 'Build' then 'Build'
when g."nhs_address" = m."Address" and g."nhs_can" = 'false' and
g."nhs_build_spec" = 'Spec' then 'Spec' else '' end))
as "Build/Spec"
from "Calculations" c
left join "MasterLotList" m on ((c."DCAD_Prop_ID" = m."DCAD_Prop_ID"))
left join "HomeClosings" h on ((h."Address" = m."Address"))
left join "GrossNewHomeSales" g on ((g."nhs_address" = m."Address"))
left join "HCSTable" d on ((d."DCAD_Prop_ID" = c."DCAD_Prop_ID"))
left join "LotSales" l on ((l."DCAD_Prop_ID" = c."DCAD_Prop_ID"))
left join "CurrentSpecs" s on ((s."Address_of_Inventory_Home" =
m."Address"))

Any help provided would be greatly appreciated.



--
View this message in context: http://postgresql.nabble.com/isnull-function-in-pgAdmin3-tp5923122p5923162.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


Re: isnull() function in pgAdmin3

From
dudedoe01
Date:
The tables I migrated from MySQL into postgreSQL have exactly the same amount
of rows of data so the problem is inside the view being created.



--
View this message in context: http://postgresql.nabble.com/isnull-function-in-pgAdmin3-tp5923122p5923164.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


Re: isnull() function in pgAdmin3

From
Alexander Farber
Date:

Re: isnull() function in pgAdmin3

From
Adrian Klaver
Date:
On 09/27/2016 01:10 PM, dudedoe01 wrote:
> Thanks Kevin, Igor and Adrian. In MySQL with the isnull() function I get all
> three values RPG INV, Owner Inventory, and Builder Inventory showed up in
> the view while when I do the is null function in postgreSQL I only see
> Builder Inventory and Owner Inventory show up in the view. I don't know why
> the RPG_INV is not showing up.

You don't say what Postgres version you are on, but if it is 9.5+ then
this may come into play:

https://www.postgresql.org/docs/9.5/static/release-9-5.html
"

Adjust operator precedence to match the SQL standard (Tom Lane)

The precedence of <=, >= and <> has been reduced to match that of <, >
and =. The precedence of IS tests (e.g., x IS NULL) has been reduced to
be just below these six comparison operators. Also, multi-keyword
operators beginning with NOT now have the precedence of their base
operator (for example, NOT BETWEEN now has the same precedence as
BETWEEN) whereas before they had inconsistent precedence, behaving like
NOT with respect to their left operand but like their base operator with
respect to their right operand. The new configuration parameter
operator_precedence_warning can be enabled to warn about queries in
which these precedence changes result in different parsing choices.
"

Also from your original post:

(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`,


and from below:

(case when
         ((l."Funding_Date" = '') and ((h."Actual_Close_Date") is null
             or (h."Actual_Close_Date" = ''))) then 'RPG Inventory'
             when
                 (((l."Funding_Date") is null or (l."Funding_Date" <> ''))
                     and ((h."Actual_Close_Date" = '') or
(h."Actual_Close_Date") is null))
             then
                 'Builder Inventory'
             else 'Owner Occupied'
         end) AS "Lot_Status",


I would try something like:

((l."Funding_Date" = '') and ((h."Actual_Close_Date" is null)
             or (h."Actual_Close_Date" = '')))




>
> In MySQL:
>
> Owner Inventory is 4481 rows of data
> Builder Inventory is 1312 rows of data
> RPG_Inv is 374 rows of data
>
> gives the total of 6167 rows
>
> In postgreSQL:
>
> Owner Inventory is 4521 rows of data
> Builder inventory is 1646 rows of data
>
> gives the total of 6167 rows
>
> create view si_model as select
> c."DCAD_Prop_ID" as DCAD_ID,
> m."Address" as Address,
> m."Addition" as Addition,
> m."Block" as Block,
> m."Lot" as Lot,
> m."Lot_Size" as Lot_Size,
> m."Lot_Type" as Lot_Type,
> l."Funding_Date" as Lot_Sale_Date,
> h."Actual_Close_Date" as Home_Closing_Date,
> m."District" as District,
> g."nhs_builder" as Builder,
> g."nhs_sale_date" as NHSContractDate,
> '' as "Banks & Ind. Owned Lots",
> '' as "Repurchased",
> m."Reserved_Lots" as Reserved,
> d."Permit__" as "Permit #",
> d."Permit_Date" as "Permit Date",
> d."Foundation_Date" as "Foundation Date",
> d."Frame_Date" as "Frame Date",
> d."HCS" as HCS,
> '' as "Notes_Comments",
> l."Lot_Status" as "Lot Funding Status",
> '' as "Property Description",
> '' as "GIS Map",
> d."Project_ID" as Project_ID,
> (case when
>         ((l."Funding_Date" = '') and ((h."Actual_Close_Date") is null
>             or (h."Actual_Close_Date" = ''))) then 'RPG Inventory'
>             when
>                 (((l."Funding_Date") is null or (l."Funding_Date" <> ''))
>                     and ((h."Actual_Close_Date" = '') or
> (h."Actual_Close_Date") is null))
>             then
>                 'Builder Inventory'
>             else 'Owner Occupied'
>         end) AS "Lot_Status",
>
> ((case when c."DCAD_Prop_ID" = m."DCAD_Prop_ID" then 'YES' else '' end)) as
> "Home Sale",
> ((case when m."Address" =  s."Address_of_Inventory_Home" then 'C Spec' else
> '' end)) as "Current Specs",
> ((case when g."nhs_address" = m."Address" and g."nhs_can" = 'false' and
> g."nhs_build_spec" = 'Build' then 'Build'
> when g."nhs_address" = m."Address" and g."nhs_can" = 'false' and
> g."nhs_build_spec" = 'Spec' then 'Spec' else '' end))
> as "Build/Spec"
> from "Calculations" c
> left join "MasterLotList" m on ((c."DCAD_Prop_ID" = m."DCAD_Prop_ID"))
> left join "HomeClosings" h on ((h."Address" = m."Address"))
> left join "GrossNewHomeSales" g on ((g."nhs_address" = m."Address"))
> left join "HCSTable" d on ((d."DCAD_Prop_ID" = c."DCAD_Prop_ID"))
> left join "LotSales" l on ((l."DCAD_Prop_ID" = c."DCAD_Prop_ID"))
> left join "CurrentSpecs" s on ((s."Address_of_Inventory_Home" =
> m."Address"))
>
> Any help provided would be greatly appreciated.
>
>
>
> --
> View this message in context: http://postgresql.nabble.com/isnull-function-in-pgAdmin3-tp5923122p5923162.html
> Sent from the PostgreSQL - general mailing list archive at Nabble.com.
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com


Re: isnull() function in pgAdmin3

From
Thomas Kellerer
Date:
dudedoe01 schrieb am 27.09.2016 um 19:04:
> I am trying to emulate the isnull() function used in MySQL into postreSQL. I
> have tried different ways such is null but it's not producing the results
> desired. I am doing a data migration from MySQL into postgreSQL and need
> help with the isnull() in pgAdmin3.
>
> Any assistance provided would be greatly appreciated.
>
> Thanks,
>
> In MySQL:
>
> (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`,
>

Unrelated, but: why are you storing DATEs in VARCHAR columns?

A migration might be a good moment to clean up the data model and store DATEs in a DATE (or TIMESTAMP) column.



Re: isnull() function in pgAdmin3

From
Kevin Grittner
Date:
On Tue, Sep 27, 2016 at 3:12 PM, dudedoe01 <marsalanaq@gmail.com> wrote:
> The tables I migrated from MySQL into postgreSQL have exactly the same amount
> of rows of data so the problem is inside the view being created.

Have you actually confirmed that there are any cases where
isnull(expression) yields a different result than (expression) is
null when the expression gives the same value?  I'm suspicious that
an expression could be yielding a different result, perhaps based
on join conditions handling comparisons between null values
differently.  Remember, for example, that in PostgreSQL NULL = NULL
does not evaluate to TRUE.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


Re: isnull() function in pgAdmin3

From
dudedoe01
Date:
<http://postgresql.nabble.com/file/n5923277/Capture.png>

This is with the DATE data type. I have 9.5 version installed.



--
View this message in context: http://postgresql.nabble.com/isnull-function-in-pgAdmin3-tp5923122p5923277.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


Re: isnull() function in pgAdmin3

From
Adrian Klaver
Date:
On 09/28/2016 02:24 PM, dudedoe01 wrote:
> <http://postgresql.nabble.com/file/n5923277/Capture.png>

In future can you copy and paste the error. It makes it easier to deal
with. Thanks.

>
> This is with the DATE data type. I have 9.5 version installed.

The type really does not matter. The issue is as the error says, there
is no isnull() function in Postgres:

test=# select version();
                                                            version


-----------------------------------------------------------------------------------------------------------------------------
  PostgreSQL 9.5.4 on i686-pc-linux-gnu, compiled by gcc (SUSE Linux)
4.8.3 20140627 [gcc-4_8-branch revision 212064], 32-bit
(1 row)


test=# select isnull();
ERROR:  function isnull() does not exist
LINE 1: select isnull();
                ^
HINT:  No function matches the given name and argument types. You might
need to add explicit type casts.



>
>
>
> --
> View this message in context: http://postgresql.nabble.com/isnull-function-in-pgAdmin3-tp5923122p5923277.html
> Sent from the PostgreSQL - general mailing list archive at Nabble.com.
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com


Re: isnull() function in pgAdmin3

From
dudedoe01
Date:
Select version(); shows

postgreSQL 9.6beta3, compiled by Visual C++ build 1800, 64-bit



--
View this message in context: http://postgresql.nabble.com/isnull-function-in-pgAdmin3-tp5923122p5923791.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


Re: isnull() function in pgAdmin3

From
Adrian Klaver
Date:
On 09/30/2016 08:18 AM, dudedoe01 wrote:
> Select version(); shows
>
> postgreSQL 9.6beta3, compiled by Visual C++ build 1800, 64-bit

There is no isnull() in 9.6 either, if that is what you are asking?

Otherwise what is the question you want answered?

--
Adrian Klaver
adrian.klaver@aklaver.com


Re: isnull() function in pgAdmin3

From
dudedoe01
Date:
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`



--
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.


Re: isnull() function in pgAdmin3

From
Ken Tanzer
Date:
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`




Hi.  Over the years I have gotten a lot of patient assistance from this mailing list, for which I am abundantly grateful.  In light of that, I offer the following "translations," which are specifically in response to your question about emulating the SQL above:

and (isnull(`s`.`Actual_Close_Date`)
--> and (`s`.`Actual_Close_Date` IS NULL)

((isnull(`s`.`Funding_Date`)
--> (`s`.`Funding_Date` IS NULL)

or isnull(`s`.`Actual_Close_Date`)))
-->or (`s`.`Actual_Close_Date` IS NULL)))

HTH, and Cheers.

Ken




 
--
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.


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



--
AGENCY Software  
A Free Software data system
By and for non-profits
(253) 245-3801

learn more about AGENCY or
follow the discussion.

Re: isnull() function in pgAdmin3

From
Adrian Klaver
Date:
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.klaver@aklaver.com


Re: isnull() function in pgAdmin3

From
Raymond O'Donnell
Date:
On 03/10/16 14:39, 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
> 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` = '')))

<snip>

Maybe I missed something earlier in the thread (only tuned in now), but
what's wrong with

    ... and s.actual_close_date is null ....

?

Ray.





Re: isnull() function in pgAdmin3

From
"David G. Johnston"
Date:
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.