How to declare a constant in an SQL query?

Question

I am using Oracle 11g R2. Is there a way to give a name (alias) to a single value selected from a table before an SQL query in the same expression? That is a single SQL command, I mean, and no PL/SQL.

The closest I've come to is:

WITH
Approved AS (SELECT c.value FROM configuration c WHERE c.code = 'Approved'),
Arrived AS (SELECT c.value FROM configuration c WHERE c.code = 'Arrived'),
Scheduled AS (SELECT c.value FROM configuration c WHERE c.code = 'Scheduled')

SELECT *
FROM list l WHERE l.status_key > (SELECT value FROM Approved);

I am looking for something similar to, say:

WITH
Approved AS CONSTANT (SELECT c.value FROM configuration c WHERE c.code = 'Approved'),
Arrived AS CONSTANT (SELECT c.value FROM configuration c WHERE c.code = 'Arrived'),
Scheduled AS CONSTANT (SELECT c.value FROM configuration c WHERE c.code = 'Scheduled')

SELECT *
FROM list l WHERE l.status_key > Approved;

The reason I don't want to inline the select statement for the value is that my query is complex enough as it is and I'd rather take some of that complexity out, if possible.

Answer 1

I sometimes use a construct like this:

WITH const as
      (select max(case when c.code = 'Approved' then c.value end) as Approved,
              max(case when c.code = 'Approved' then c.value end) as Approved,
              max(case when c.code = 'Scheduled' then c.value end) as Scheduled
       from configuration c
      ),
      . . .
SELECT
FROM const cross join
     list l
WHERE status_key > Approved;

Sometimes if I need the constants at different places in the query, then I have to bring in the const CTE more than once.