0

I need to emulate the insull() function from MySQL into pgAdmin but it doesn't seem to work.

What is the PostgreSQL equivalent for ISNULL()

I tried to follow the above link but it isn't producing the results as same as MySQL. Can someone shade some light on this please.

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

pgAdmin:

case when
     Funding_Date = '' and (Actual_Close_Date is null or Actual_Close_Date= '') 
     then 'RPG Inventory'
     when (Funding_Date is null or Funding_Date <> '') 
     and (Actual_Close_Date = '' or Actual_Close_Date is null) 
     then'Builder Inventory' else 'Owner Inventory' 
     end as "Lot Status",
Community
  • 1
  • 1
Jason Smith
  • 127
  • 1
  • 3
  • 14
  • `SQL Server` from your link above differs in syntax from `MySQL`. Check out http://dev.mysql.com/doc/refman/5.7/en/working-with-null.html – James Gould Sep 27 '16 at 14:46
  • 1
    try `coalesce`... – Felypp Oliveira Sep 27 '16 at 14:46
  • 4
    can't you just use the sql standard `something IS NULL`? – Marc B Sep 27 '16 at 14:46
  • 1
    I tried to use the is null function it doesn't produce the same results as expected. I'm doing a data migration from MySQL to postgreSQL. – Jason Smith Sep 27 '16 at 14:48
  • 1
    Go for @MarcB proposition... he's not talking about a function ISNULL, but the regular way of checking if things are NULL, using the `IS` operator... – Felypp Oliveira Sep 27 '16 at 14:49
  • You don't seem to want an `ISNULL()` function, but rather a PostgreSQL equivalent of `is_empty()` where the value can be an empty string or null? – R. Chappell Sep 27 '16 at 14:52
  • 1
    Yes @ R. Chappell. I tried to emulate the isnull() in pgAdmin it's not producing the desired results. – Jason Smith Sep 27 '16 at 14:53
  • 1
    Then check out http://stackoverflow.com/questions/23766084/best-way-to-check-for-empty-or-null-value – R. Chappell Sep 27 '16 at 14:54
  • Try @MarcB approach: `SELECT s.Actual_Close_Date IS NULL FROM s;`... should return true or false... – Felypp Oliveira Sep 27 '16 at 14:55
  • 1
    When I apply the is null function in the above case statement in pgAdmin it only shows me the values of Builder Inventory and Owner inventory and not RPG INV while in MySQL it shows all three values in the table. – Jason Smith Sep 27 '16 at 15:00
  • Your PG SQL is missing parenthesis. `1 AND 2 OR 3` is not the same as `1 AND (2 OR 3)`. – R. Chappell Sep 27 '16 at 15:23
  • 1
    Thanks @ R. Chappell I didn't catch that but still only the Owner Inventory and the Builder Inventory are being showed not RPG Inventory. – Jason Smith Sep 27 '16 at 15:32
  • 1
    `CASE WHEN Funding_Date = '' AND (Actual_Close_Date <> '') IS NOT TRUE THEN 'RGP Inventory' WHEN (Funding_Date IS NULL OR Funding_Date <> '') AND (Actual_Close_Date <> '') IS NOT TRUE THEN 'Builder Inventory' ELSE 'Owner Inventory' END AS "Lot Status"` Did you try with the examples from the link I gave you? – R. Chappell Sep 27 '16 at 15:40

0 Answers0