Constants in Oracle SQL query

Question

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.

What tool are you using? SQL*Plus? – jva Aug 18 '09 at 11:16 — jva, Aug 18 '09 at 11:16

score 10 · Answer 1 · answered Sep 11 '13 at 10:34

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.

score 4 · Accepted Answer · answered Aug 18 '09 at 11:58

4

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
)

answered Aug 18 '09 at 11:58

Jens Schauder

77,657
34
181
348

That's probably causing some of my confusion. As you see from my comment above, I am used to being able to create local variables & constants in my SQL (not to mention temporary tables!). – FourOaks Aug 18 '09 at 12:15
Ah! I catch your drift. Could well work, I'll give it a go. Thanks. – FourOaks Aug 18 '09 at 12:18

score 3 · Answer 3 · answered Aug 18 '09 at 10:37

3

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

answered Aug 18 '09 at 10:37

jva

2,797
1
26
41

Hi, Thanks for the prompt response, but perhaps I oversimplified the example. What I need to do is to return the results of a query based on the value of DateIndex. Eg. (slightly less simple example!)... SELECT * FROM SomeTable WHERE SomeColumn = DateIndex; – FourOaks Aug 18 '09 at 10:53

Chris Cameron-Mills · Answer 4 · 2009-08-18T11:33:45.087

1

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

edited Aug 18 '09 at 11:33

answered Aug 18 '09 at 11:01

Chris Cameron-Mills

4,587
1
27
28

Yes I am trying to return a table of results to the VB running the query for display on a web page. It is all too simple in my more familiar domain... DECLARE @DateIndex int SET @DateIndex = MONTH(GETDATE()) + 12 * YEAR(GETDATE()) SELECT * FROM Log WHERE MonthIndex = @DateIndex – FourOaks Aug 18 '09 at 12:12

score 0 · Answer 5 · answered Aug 18 '09 at 18:02

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

Constants in Oracle SQL query

5 Answers5

Linked