What's the "PRQL Way"? Cleaning up raw data with empty fields

[richb-hanover]

I have a database of real estate parcels that has a column with a two or three character code for the Zoning District: LCD, RD, BD, etc. The raw data is indexed by a primary key (parcel ID, or PID) but it also has a bunch of typos. I wrote a function to patch this up...

# Clean up zoning districts
# Returns two or three character district
let CleanZoningDistrict = zd -> case [
  zd == "ES" => "RD",
  zd == "R" => "RD",
  zd == "SFR" => "RD",
  zd == "URD" => "RD",
  zd == "LCD" => "LCD",
  zd == "CD" => "LCD",
  zd == "LDC" => "LCD",
  zd == "SD" => "RD",
  zd == "" => null,
  true => zd,
]

The raw data also has a bunch of empty fields ("") that are simply missing (bad data entry, I suspect.) I created a separate table with rows for each entry that has an empty Zoning District and I manually filled in another column containing a "CorrectedZoningDistrict" indexed on the same PID.

My thought was to join the two tables and if the Zoning District is "", use the "CorrectedZoningDistrict".

I can see how to do it in two steps - something like:

ZoningDistrict = CleanedZoningDistrict zd 
RealZoningDistrict = ZoningDistrict ?? CorrectedZoningDistrict # if main table is null, use the corrected table entry

Is there a "PRQL Way" to fold that lookup into the CleanZoningDistrict function? Thanks.

[richb-hanover]

I suspect the two-step process outlined above is the easiest way to do this. (It's clearly straightforward.)

I will need to use the resulting table in lots of different queries, so I plan to create a view (letting PRQL generate the SQL) so that I don't have to clutter up my (new) queries with all the setup. Thanks!