Home » Developer & Programmer » Forms » Dynamic query for the table name
Dynamic query for the table name [message #82769] Wed, 02 July 2003 07:22 Go to next message
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 Go to previous messageGo to next message
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 #82785 is a reply to message #82781] Fri, 04 July 2003 02:37 Go to previous messageGo to next message
Sudeep
Messages: 17
Registered: March 2002
Junior Member
in forms ur given solution did not worked can u give an another example or send me a form with the given example it would be a great help

thans in advance

Sudeep
Re: Dynamic query for the table name [message #82833 is a reply to message #82769] Tue, 08 July 2003 10:52 Go to previous messageGo to next message
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;
Re: Dynamic query for the table name [message #82837 is a reply to message #82833] Tue, 08 July 2003 12:46 Go to previous message
like this.. nm
Messages: 1
Registered: July 2003
Junior Member
nevermind I take that back.. I wasnt thinking clearly
Previous Topic: Change Insert message
Next Topic: key-enter trigger
Goto Forum:
  


Current Time: Thu Feb 29 21:31:14 CST 2024