CODEWITHSUNDEEP
sqldatabaseoracle-databaseconstants
FourOaks
FourOaks

Reputation: 190

Constants in Oracle SQL query

I am new to Oracle (though familiar with SQL) and have to write a fairly complex query where a value derived from the current date is used many times. Rather than calculate the value each time, it would seem obvious to declare a constant for the purpose.

However, when I then try to use my DateIndex constant in the subsequent SELECT statement (which I wish to return values based on "DateIndex"), the parser tells me that it is exepcting SELECT INTO.

What I have (simplified to the lowest form) is...

 DECLARE DateIndex CONSTANT NUMBER(10,0) := 24;

 BEGIN
      SELECT DateIndex
      FROM DUAL;
END;

Is it only possible to use constants when selecting into a table rather than returning results? Seems very odd.

Note that I do not have write permissions on the database.

Many thanks for any assistance.

Upvotes: 4

Views: 37956

Answers (5)

f3lix
f3lix

Reputation: 29877

I prefer the following use of WITH and the DUAL table:

WITH 
const AS ( SELECT 
    3.14 AS pi,
    1    AS one 
    FROM DUAL
)
SELECT * FROM sometable t,  const
 WHERE t.value = const.pi;

This lets you define constants before the use in a statement and the actual statement is not cluttered with subselects.

Upvotes: 10

tuinstoel
tuinstoel

Reputation: 7306

When you want to return a result set you need a ref cursor. 

create or replace procedure getlogs(p_sys_refcursor out sys_refcursor)
is
begin
  open p_sys_refcursor for
    select *
    from   log
    where  monthindex = 12 * to_char(sysdate,'yyyy') + to_char(sysdate,'mm');
end;
/

The value of sysdate will be determined only once at the start of the query, so there is no need for declaring some kind of constant inside sql or pl/sql.

Edit1

When you don't want to call a stored proc, do:

select *
from   log
where  monthindex = 12 * to_char(sysdate,'yyyy') + to_char(sysdate,'mm');

Upvotes: 0

Jens Schauder
Jens Schauder

Reputation: 81950

Your code is not Oracle SQL but PL/SQL. In PL/SQL the result of a query has to be assigned to a variable. So you either have have to use a "select into clause" if you expect exactly one result, or you use a cursor.

In SQL on the other hand you can't declare a constant. You can sometimes work around this limitation by using an inline view like so

select something-complex-here, x.pi 
from sometable, (
    select 3.1415 as pi, 1234 other_constant 
    from dual
)

Upvotes: 4

Chris Cameron-Mills
Chris Cameron-Mills

Reputation: 4657

The error is not to do with your constant, the error is because you are using a SELECT statement without an INTO. A SELECT in an anonymous block is not the same as if you were to run a SELECT statement from SQL*Plus for example. It doesn't make sense to select something and do nothing with it, which is why it is prompting you for an into:

SELECT colA, colB
INTO variable_a, variable_b
WHERE something = DateIndex;

This of course assumes your query will only return one row. I have a feeling what you are really after is writing a function that contains your logic and returns a nested table type that you could select from.

EDIT: nevermind, I see that are not able to create type

Upvotes: 1

jva
jva

Reputation: 2807

DECLARE 
DateIndex CONSTANT NUMBER(10,0) := 24;
TargetVariable NUMBER;
BEGIN
      SELECT DateIndex
      INTO TargetVariable
      FROM DUAL;
END;

Upvotes: 3

Related Questions