Search Postgresql Archives

Re: isnull() function in pgAdmin3

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxxxxx


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



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux