Oracle Select query with dynamic table name

Question

I m trying to create a complicated select query which uses temp tables and syntax as below

with as
  table1
    (   select start_date, end_date ....somequery - returns only 1 row ),
  table2
    ( select ... somequery use table1 columns in where clause.. ),
  table3
    ( select ... use table1 columns in where clause .. )
select * from 
                select case when  ( start_date < sysdate -1000 and end_date > sysdate ) then 'table2' else 'table3' end  from table1 
         where rownum < 10

So logic is simple based on return value from table1 i may want to query table 2 or may want to query table3

Problem: Oracle doesnt allow table name to be dynamically generated in a sql query

I know i can write a procedure and use EXECUTE IMMEDIATE but for some reason i have to do everything via a single query. Any help will be greatly appreciated.

Gordon Linoff · Accepted Answer · 2014-03-21 22:05:33Z

2

You can do something like this for your main select:

select *
from table1 cross join
     table2
where start_date < sysdate - 1000 and end_date > sysdate and rownum < 10
union all
select *
from table1 cross join
     table3
where not (start_date < sysdate - 1000 and end_date > sysdate) and rownum < 10

The idea is to use union all for the two conditions and where to guarantee that no rows are returned subject to your conditions. Note that this form of the where statement do not take into account NULL values for start_date or end_date.

I suspect that there might be another way to write this query, if more details about table2 and table3` were available.

answered Mar 21, 2014 at 22:05

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Neeraj Over a year ago

Thank you for your response.both tables has 100,000 rows as output but for table2 the query is on a smaller set of data tables e.g. 1,000,000 but table3 queries around 10,000,000 with alot of aggregate functions. Table3 contains queries from tables that are purely like a logging tables which have variable hits corresponding to any entry made to main tables used in the query for table2. If a user wants full report thats why we prefer using table 2 else we have no choice other than to go to table3 (where query takes 3-4 minutes to come out.) I will try your solution and see if that helps.

Collectives™ on Stack Overflow

Oracle Select query with dynamic table name

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related