CODEWITHSUNDEEP
sqloracle-databaseoracle11gr2
shwartz
shwartz

Reputation: 671

How to declare a constant in an SQL query?

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.

Upvotes: 8

Views: 20244

Answers (2)

Gerrat
Gerrat

Reputation: 29700

The short answer, is no - you can't do that.

You could create a view though to hide some of the initial complexity.

...or if you really want you could create a function, and kind of use it as a constant...something like (excluding any error handling):

CREATE OR REPLACE FUNCTION config_code (code IN VARCHAR2) 
RETURN configuration.value%TYPE AS

value       configuration.value%TYPE;

BEGIN
    SELECT c.value INTO value FROM configuration c WHERE c.code = code;
    RETURN value;
END;

You could then use it as:

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

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270061

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.

Upvotes: 8

Related Questions