4

Is there any way to skip rows that are null or empty? I could use some help with sorting the output of a subtable. My having statement is returning an error I can’t fix.

It returns the error: ERROR: invalid input syntax for integer: " "

This seems to be because some rows in my table will have either null values or be empty and the having statement is getting hung up there.

Here is the full query

SELECT
    count(job),
    year,
    zipcode
FROM
(
    SELECT
        substring (cast(dobjobs.prefilingdate AS varchar), '^\d\d\d\d') AS year,
        dobjobs.job,
        dobjobs.bbl,
        pluto_17v1.zipcode
   FROM
        dobjobs
        JOIN pluto_17v1 ON dobjobs.bbl = pluto_17v1.bbl
   GROUP BY
        dobjobs.prefilingdate,
        dobjobs.bbl,
        pluto_17v1.zipcode,
        dobjobs.job
   ORDER BY
        year
) AS sub
GROUP BY
    year, zipcode
HAVING
    CAST( zipcode AS int ) IN (10039, 10039, 10026, 10030, 10037, 10027, 10032, 10033, 10040, 10034, 10031)
ORDER BY
    year;
Dai
  • 141,631
  • 28
  • 261
  • 374
M. Albasi
  • 41
  • 1
  • 2
    Why not just exclude null/empty zips first in `WHERE`, possibly inside the `sub` query? Could also avoid the cast and use `zipcode IN ('10039',..)` which would have the same effect as excluding prior due to no null/"" matching. Avoiding the cast might even allow a better query plan if it opens up a sargable index (ymmv, but something to consider). I'd probably opt for the pre-filter myself.. if nothing else it's more clear: "There are null/empty values, and they should be excluded". – user2864740 Jun 28 '18 at 22:41
  • 3
    An an aside: zip codes are **not** *really* numbers and should generally not be treated as integers. They just use lots of digits. Like phone numbers. And some license plate numbers. For example, [zip codes can start with 0](https://smartystreets.com/docs/zip-codes-101) and the [ZIP+4](https://en.wikipedia.org/wiki/ZIP_Code) contains a dash. – user2864740 Jun 28 '18 at 22:47
  • 2
    It didn't occur to me that I could change my list to '10039', '10026' etc of zips instead of casting the column to an integer. Changing that fixed my issue. Thanks for the tips on best practice too and, in the future, I'll try to pre-filter in the subtable. I appreciate your help! – M. Albasi Jun 28 '18 at 22:58
  • 1
    Unrelated, but: why don't you use `extract(year from dobjobs.prefilingdate)` and treat the year as a proper number? –  Jun 29 '18 at 05:38
  • Instead of casting and using a regex? No reason, other than a general lack of understanding of how sql handles dates. Thanks for leading me in the right direction! – M. Albasi Jul 04 '18 at 14:27

0 Answers0