title: Lookup based on three criteria, with fallback values for two
body: <p>I have data on projects in different countries. Each project has a <code>devlevel</code>, a <code>sector</code> and a <code>subsector</code>, for example (simplified):</p>
  
  <pre><code> pid  |                  sector                  |      subsector       | devlevel
  ------+------------------------------------------+----------------------+----------
    612 | Transport                                | Airports &amp; Logistics | EM
   1473 | Information &amp; Communication Technologies | Broadband            | RoW
   1424 | Other                                    | Other                | RoW
   1476 | Transport                                | Urban Highways       | RoW
   1443 | Water                                    | Water Supply         | EM
  </code></pre>
  
  <p>Depending on the combination of these three characteristics, each project has a certain <code>efficiency</code>. For the time being, I have structured the lookup table like this:</p>
  
  <pre><code>  sector   |     subsector      | devlevel | efficiency
  -----------+--------------------+----------+------------
   Transport | (null)             | EM       |      16000
   Transport | (null)             | RoW      |       9600
   Water     | (null)             | EM       |      16000
   Water     | (null)             | RoW      |       9600
   Energy    | Generation — Hydro | EM       |      16000
   Energy    | Generation — Hydro | RoW      |       9600
   Energy    | (null)             | EM       |      11200
   Energy    | (null)             | RoW      |       6720
   (null)    | (null)             | EM       |       8000
   (null)    | (null)             | RoW      |       8000
  </code></pre>
  
  <p>The logic is: if the project has a <code>sector</code> and <code>subsector</code> match, use the <code>efficiency</code> corresponding to its <code>devlevel</code>. If it has a <code>sector</code> match but not <code>subsector</code>, use the <code>efficiency</code> corresponding to <code>devlevel</code> and <code>subsector</code> <code>NULL</code>. And if there is no match on <code>sector</code> at all, use the <code>efficiency</code> with both <code>sector</code> and <code>subsector</code> values of <code>NULL</code>.</p>
  
  <p>I've struggled to implement a query that looks up the relevant <code>efficiency</code>. My latest attempt is below. It attempts to narrow down the possible lookup values based on <code>sector</code> in an initial subquery, then narrow them down further based on <code>subsector</code> in a subsequent (parent) subquery, then join the slimmed-down lookup table to the project data using a match on <code>devlevel</code>. But I get this error message:</p>
  
  <pre><code>ERROR:  invalid reference to FROM-clause entry for table "p"
  LINE 26:                 SELECT 1 FROM devlookup d1 WHERE p.sector = ...
                                                            ^
  HINT:  There is an entry for table "p", but it cannot be referenced from this part of the query.
  SQL state: 42P01
  Character: 4079
  </code></pre>
  
  <p>Pointers would be much appreciated.</p>
  
  <p><strong>Query</strong>:</p>
  
  <pre><code>WITH devlookup (id, sector, subsector, devlevel, efficiency)
  AS (VALUES
      (1, 'Transport', NULL, 'EM', 16000),
      (2, 'Transport', NULL, 'RoW', 9600),
      (3, 'Water', NULL, 'EM', 16000),
      (4, 'Water', NULL, 'RoW', 9600),
      (5, 'Energy', 'Generation — Hydro', 'EM', 16000),
      (6, 'Energy', 'Generation — Hydro', 'RoW', 9600),
      (7, 'Energy', NULL, 'EM', 11200),
      (8, 'Energy', NULL, 'RoW', 6720),
      (9, NULL, NULL, 'EM', 8000),
      (10, NULL, NULL, 'RoW', 8000)
  ),
  EMcountries AS (VALUES ('United Arab Emirates'), ('Afghanistan'), ('Antigua and Barbuda'), ('Anguilla'), ('Armenia'), ('Netherlands Antilles'), ('Angola'), ('Antarctica'), ('Argentina'), ('American Samoa'), ('Aruba'), ('Azerbaijan'), ('Barbados'), ('Bangladesh'), ('Burkina'), ('Bahrain'), ('Burundi'), ('Benin'), ('Brunei'), ('Bolivia'), ('Brazil'), ('Bahamas'), ('Bhutan'), ('Bouvet Island'), ('Botswana'), ('Belarus'), ('Belize'), ('Cocos (Keeling) Islands'), ('Congo {Democratic Rep}'), ('Central African Republic'), ('Congo'), ('Ivory Coast'), ('Cook Islands'), ('Chile'), ('Cameroon'), ('China'), ('Colombia'), ('Costa Rica'), ('Cuba'), ('Cape Verde'), ('Christmas Island'), ('Djibouti'), ('Dominica'), ('Dominican Republic'), ('Algeria'), ('Ecuador'), ('Egypt'), ('Western Sahara'), ('Eritrea'), ('Ethiopia'), ('Fiji'), ('Falkland Islands (Malvinas)'), ('Micronesia), (Federated States of'), ('Gabon'), ('Grenada'), ('Georgia'), ('French Guiana'), ('Ghana'), ('Gambia'), ('Guinea'), ('Guadeloupe'), ('Equatorial Guinea'), ('South Georgia and the South Sandwich Islands'), ('Guatemala'), ('Guam'), ('Guinea-Bissau'), ('Guyana'), ('Heard Island and McDonald Islands'), ('Honduras'), ('Haiti'), ('Indonesia'), ('India'), ('British Indian Ocean Territory'), ('Iraq'), ('Iran'), ('Jamaica'), ('Jordan'), ('Kenya'), ('Kyrgyzstan'), ('Cambodia'), ('Kiribati'), ('Comoros'), ('Saint Kitts and Nevis'), ('Korea), (Democratic People''s Republic of'), ('Kuwait'), ('Cayman Islands'), ('Kazakhstan'), ('Lao People''s Democratic Republic'), ('Lebanon'), ('St Lucia'), ('Sri Lanka'), ('Liberia'), ('Lesotho'), ('Libya'), ('Morocco'), ('Madagascar'), ('Marshall Islands'), ('Mali'), ('Myanmar), ({Burma}'), ('Mongolia'), ('Northern Mariana Islands'), ('Martinique'), ('Mauritania'), ('Montserrat'), ('Mauritius'), ('Maldives'), ('Malawi'), ('Mexico'), ('Malaysia'), ('Mozambique'), ('Namibia'), ('New Caledonia'), ('Niger'), ('Norfolk Island'), ('Nigeria'), ('Nicaragua'), ('Nepal'), ('Nauru'), ('Niue'), ('Oman'), ('Panama'), ('Peru'), ('French Polynesia'), ('Papua New Guinea'), ('Philippines'), ('Pakistan'), ('Pitcairn Islands'), ('Puerto Rico'), ('Palestinian Territory'), ('Palau'), ('Paraguay'), ('Qatar'), ('Reunion'), ('Rwanda'), ('Saudi Arabia'), ('Solomon Islands'), ('Seychelles'), ('South Sudan'), ('Sudan'), ('Saint Helena'), ('Sierra Leone'), ('Senegal'), ('Somalia'), ('Suriname'), ('Sao Tome &amp; Principe'), ('El Salvador'), ('Syria'), ('Swaziland'), ('Turks and Caicos Islands'), ('Chad'), ('French Southern Territories'), ('Togo'), ('Thailand'), ('Tajikistan'), ('Tokelau'), ('Turkmenistan'), ('Tunisia'), ('Tonga'), ('Timor-Leste'), ('Trinidad &amp; Tobago'), ('Tuvalu'), ('Tanzania'), ('Ukraine'), ('Uganda'), ('United States Minor Outlying Islands'), ('Uruguay'), ('Uzbekistan'), ('Saint Vincent and the Grenadines'), ('Venezuela'), ('Virgin Islands), (British'), ('Virgin Islands), (U.S.'), ('Vietnam'), ('Vanuatu'), ('Wallis and Futuna'), ('Samoa'), ('Yemen'), ('Mayotte'), ('South Africa'), ('Zambia'), ('Zimbabwe'), ('Saint Barthelemy'), ('Saint Martin')),
  projectinfo AS (
      SELECT p.pid, p.country, p.projectname, p.sector, p.subsector,
          (CASE WHEN p.country IN (SELECT * FROM EMcountries) THEN 'EM' ELSE 'RoW' END) AS devlevel
      FROM exp_projects p
      )
  SELECT p.pid, p.country, p.projectname, p.sector, p.subsector, p.devlevel, d.sector devsector, d.subsector devsubsector, d.efficiency
  FROM projectinfo p 
  LEFT OUTER JOIN (
      SELECT * FROM (
          SELECT * FROM devlookup WHERE id IN (
              CASE WHEN EXISTS (
                  SELECT 1 FROM devlookup d1 WHERE p.sector = d1.sector
                  )
              THEN (SELECT id FROM devlookup d1 WHERE d1.sector = p.sector)
              ELSE (SELECT id FROM devlookup d1 WHERE d1.sector IS NULL)
              END
          ) 
      ) AS devlookup_matching_sector
      WHERE id IN (
          CASE WHEN EXISTS (
              SELECT 1 FROM devlookup_matching_sector dms WHERE dms.subsector = p.subsector
              )
          THEN (SELECT id FROM devlookup_matching_sector dms WHERE dms.subsector = p.subsector)
          ELSE (SELECT id FROM devlookup_matching_sector dms WHERE dms.subsector IS NULL)
          END
      )
  ) devlookup_matching_sector_subsector
  ON (devlookup_matching_sector_subsector.devlevel = p.devlevel)
  WHERE p.country IN ('Australia', 'Afghanistan', 'Brazil')
  ORDER BY pid ASC;
  </code></pre>