Who's on First?

It's important to know which SQL statements happen before others. Hopefully, this list will make it obvious why you can't use an alias in a WHERE statement, and why analytic functions can only be a SELECT:

1. From / Join
2. Connect By
3. Where
4. Group By
5. Having
6. Select / Analytic Functions
7. Distinct
8. Order By
9. Fetch / Offset

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'
       );

Where Case

I wish I could think of a good reason to have a CASE statement in a WHERE statement -- but I can't so I'll just show something that works.

One day...

SELECT ename
     , job
     , deptno
  FROM scott.emp
WHERE job LIKE
              CASE
                 WHEN deptno = '20' THEN '%AL%'
                 WHEN deptno = '30' THEN '%MAN%'
              END
ORDER BY deptno, job
;

Where: How to Search for Various Conditions

This query presents the operator with a choice of how to find data. After selecting a search field, and entering a value, a decode is used to generate SQL code. The New_Value clause sets that equal to &xWhere, and it is used with the 'Where' clause.

(I have tried incorporating it directly into the Where with no luck.)

Prompt
Prompt                +------------------------------------------+
Accept Choice Prompt '| Search by 1-EmpNo 2-EName : '
Accept Value  Prompt '| Please enter Value        : '
Prompt                +------------------------------------------+

Set Term Off
Column WhereClause New_Value xWhere
Select         Decode(&Choice, 1, 'EmpNo=&Value',
                               2, 'EName='||Upper('''&Value''')
                     )
               As 
       WhereClause
  From Dual;
Set Term On

Select EmpNo, EName, Job, HireDate, DeptNo
  From Emp
 Where &xWhere;