Outer Join with Where

I may be the last one to figure this out, but a WHERE statement causes grief with an outer JOIN. Here I’m trying to join on a code table to get a description, but I only want those descriptions used for ‘Status,’ and not all records in Store have a status value.

Doesn't Work

Don't use ON key and WHERE condition.

SELECT client_id                       AS "Client#"
     , cs.status                       AS "Status"
     , dc.cd_meaning                   AS "Description"
     , TO_CHAR(cs.dcnt, '999,999,999') AS "Rec Cnt"
  FROM (
        SELECT client_id
             , status
             , COUNT(*)        AS dcnt
          FROM Store
         GROUP BY client_id, status
       ) cs
  LEFT
  JOIN def_codes dc
    ON cs.status = dc.cd
 WHERE column_name = 'STATUS';

Works

Use ON (key and AND condition).

SELECT client_id                       AS "Client#"
     , cs.status                       AS "Status"
     , dc.cd_meaning                   AS "Description"
     , TO_CHAR(cs.dcnt, '999,999,999') AS "Rec Cnt"
  FROM (
        SELECT client_id
             , status
             , COUNT(*)        AS dcnt
          FROM Store
         GROUP BY client_id, status
       ) cs
  LEFT
  JOIN def_codes dc
    ON ( cs.status = dc.cd
         AND
        column_name = 'STATUS'
       );