I'm hoping to dynamically reference last Fridays date in the weekly sales tables in Oracle SQL Developer i.e. SELECT * FROM Sales_DDMMYY
I can do this in SQL Server (DECLARE
/ SET
/ EXECUTE
) but haven't had any joy with SQL Developer.
Even the ability to create a date variable to be referenced within the code would be a great start.
Stop!
I strongly suggest you not to do that. That's not the way to create a data model. If you have a table which contains values related to different dates, then date should be a column in that table, such as
create table sales
(id number,
datum date,
amount number
);
Insert rows as
insert into sales (id, datum, amount)
select 1, date '2020-06-01', 100 from dual union all
select 2, date '2020-05-13', 240 from dual union all
select 3, date '2020-05-13', 160 from dual;
and use it as
select sum(amount)
from sales
where datum = date '2020-05-13'
That is the way to do it. Naming columns by dates is ... well, close to a suicide.
Aha, now I see: it is a table name that contains dates. Doesn't really matter, my suggestion still stands. Do not do that. Use a date column within a single table.
If you want - and if you can afford it - partition the table on date value. Note that partitioning option exists in Oracle Enterprise Edition which is quite expensive. So - date column it is.
If there's nothing you can do about it, then dynamic SQL it is. For example:
Sample table:
SQL> create table sales_200620 as select * From dept;
Table created.
Function that accepts ddmmyy
value as a parameter, composes table name and returns a refcursor:
SQL> create or replace function f_test (par_ddmmyy in varchar2)
2 return sys_refcursor
3 is
4 l_table_name varchar2(30) := 'sales_' || par_ddmmyy;
5 l_rc sys_refcursor;
6 begin
7 open l_rc for 'select * from ' || dbms_assert.sql_object_name(l_table_name);
8 return l_rc;
9 end;
10 /
Function created.
Testing:
SQL> select f_test('200620') from dual;
F_TEST('200620')
--------------------
CURSOR STATEMENT : 1
CURSOR STATEMENT : 1
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL>
Without creating a function: use substitution variable:
SQL> select * From &tn;
Enter value for tn: sales_200620
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL>