Change inputs through Google Sheets Query

Question

I am using QUERY function in google sheets to select Province column (H) where R,V,W are not zero. Province names are abreviations like ON, BC, AB, etc. The query below works fine:

QUERY(Data_Dump!A3:W,"SELECT H WHERE (R <> 0 OR V <> 0 OR W <> 0)",0)

However I want to also change the province names while transferring. For example changing "ON" to "Ontario" and "BC" to "British Columbia", etc.

I was thinking to combine SWITCH or SUBSTITUTE with my query similar to this:

=ArrayFormula(substitute(QUERY(Data_Dump!A3:W,"SELECT H WHERE (R <> 0 OR V <> 0 OR W <> 0)",0),"ON","ONTARIO"))

This certainly is wrong and only tries to change ON to Ontario. Just as an example of what I tried.

*** UPDATE: Here is a sample Google Sheet I share.

Appreciate any help here.

Answer 1

try:

=ARRAYFORMULA(QUERY({IFNA(VLOOKUP(Data_Dump!H3:H, {
 "ON", "Ontario"; 
 "AB", "Alberta"; 
 "QC", "Quebec";
 "BC", "British Columbia";
 "NS", "Nova Scotia"}, 2, 0)), Data_Dump!B3:W},
 "select Col1,Col18  
  where Col18 <> 0 
     or Col22 <> 0 
     or Col23 <> 0", 0))

spreadsheet demo