How do I make a dynamic table name ?

building semantic layer
1

I want to make a dynamic table name like below :

when sysdate = 20070801 then
select * from test1

and when sysdate = 20070804 then
select * from test4

so I want to dynamic define table name test’ ’ ~

I try to make a function , but it don’t work like
'select * from function_name(‘test’) ;

Anyone have a good idea ?

:hb:

Arthur Huang :taiwan: (BOB member since 2007-07-04)

2

Are you trying to access date partitions?

Please try and state what you are trying to achieve, rather than just a dynamic table name.

Mak 1 :uk: (BOB member since 2005-01-06)

3

Thanks for your quick reply.

But I don’t really understand your mention, can you give me a cue or a sample about that.

Thanks a lot. :slight_smile:

Arthur Huang :taiwan: (BOB member since 2007-07-04)

4

I’m trying to understand you question.

I know that tables can be assigned dynamically using a prompt, see

https://bobj-board.org/t/17995

Mak 1 :uk: (BOB member since 2005-01-06)

5

My question is :

I have 10 tables , log1 , log2 … log 9 , log 0 .
When today is 20070704 , that I should select * from log4.

so I want to dynamic table name for this.

I create a function like this :

--------------------------------------------------------------------------------
Function table_name(tname char )
return char as
tname1 char(30) ;
begin
tname1 := tname||to_char(to_number(to_char((sysdate),‘DD’))) ;
return tname1 ;
end table_name

--------------------------------------------------------------------------------

I want use this sql like " select * from table_name(‘log’)

Pls help me , thanks.

Arthur Huang :taiwan: (BOB member since 2007-07-04)

6

I dont think it is possible to use the table name dynamically as you try.

Possible workaround can be:
Create a derived table in the universe defined as:

SELECT *
  FROM log1
 WHERE '01' = TO_CHAR (SYSDATE, 'DD')
UNION ALL
SELECT *
  FROM log2
 WHERE '02' = TO_CHAR (SYSDATE, 'DD')
UNION ALL
SELECT *
  FROM log3
 WHERE '03' = TO_CHAR (SYSDATE, 'DD')
UNION ALL
SELECT *
  FROM log4
 WHERE '04' = TO_CHAR (SYSDATE, 'DD')

(the above is the example for 4 tables. Add more as you need)

Such derived table will select data only from 1 log table depending on the day of the month.

Marek Chladny :slovakia: (BOB member since 2003-11-27)

7

Thanks for your kindly reply .

Your answer as the same as my first thinking, and that is can work good.

But the sql is too long , so I try to make a dynamic table name choice .

It really can not use dynamic table name ? I have no idea of this …

:blue: :wah: :?:

Arthur Huang :taiwan: (BOB member since 2007-07-04)

8

If the final SQL is too long that derived table can not handle then create a DB view. Oracle DB (I assume from sysdate syntax that you use Oracle) can handle it easily.

No, not to my knowledge.

Marek Chladny :slovakia: (BOB member since 2003-11-27)