| Dynamic query for the table name [message #82769] |
Wed, 02 July 2003 07:22  |
priya
Messages: 108
Registered: February 2000
|
Senior Member |
|
|
Dear all
Ive never used a dynamic query before.
My requirement is as follows.
I have an sql query where the table name changes acording to the year.
select column1,column2,column3
from table1 a,
table2_yyq b,
where
a.date=b.date;
--yyq is a string which will be assigned dynamically
--yy is the year and q is the quarter .
Please let me know bout a solution.
thank you all
|
|
|
|
| Re: Dynamic query for the table name [message #82781 is a reply to message #82769] |
Thu, 03 July 2003 06:04  |
magnetic
Messages: 324
Registered: January 2003
|
Senior Member |
|
|
are you using forms or just a sql script?
with forms you can do like:
...
..
.
if v_date=.. then
v_table:=t1;
else
v_table:=t2;
end if;
v_string:='select c1,c2,c3 from '||v_table||
execute_immediate(v_string);
-----------
another way is to work with dynamic sql
see dbms_sql [[package of sys]] and read some document about this and it will work.
|
|
|
|
|
|
| Re: Dynamic query for the table name [message #82833 is a reply to message #82769] |
Tue, 08 July 2003 10:52 |
like this...
Messages: 1
Registered: July 2003
|
Junior Member |
|
|
you could do something like this depending upon where in the from you are trying to query.. (a trigger perhaps?)
of course you need to declare vars..
----------------------
if to_char(sysdate, 'MON') in ('JAN', 'FEB', 'MAR')
then quarter := 1;
elsif to_char(sysdate, 'MON') in ('APR', 'MAY', 'JUN')
then quarter := 2;
...
end if;
year := to_char(sysdate,'RR');
table_name := 'table2_'||year||quarter;
select column1,column2,column3
from table1 a,
table_name b,
where
a.date=b.date;
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
