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

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