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
