Creating a view that selects 4 specific table names

How can I make a view that selects these 4 table names?

TEST_TABLE_A
,TEST_TABLE_B
,TEST_TABLE_C
,TEST_TABLE_D

Open in new window


CREATE VIEW vw_all_tables AS
TEST_TABLE_A
,TEST_TABLE_B
,TEST_TABLE_C
,TEST_TABLE_D
is way wrong so I need some help. Thanks
Mark_CoAsked:
Who is Participating?
 
slightwv (䄆 Netminder) Commented:
>>I know, but would you be able to make that into a regexp for me?

Why do you 'need' regex?

But OK:  any single character after TEST_TABLE_
select table_name from user_tables where regexp_like(table_name,'TEST_TABLE_[[:alnum:]]');


Only A-D:
select table_name from user_tables where regexp_like(table_name,'TEST_TABLE_[A-D]');
0
 
Mark_CoAuthor Commented:
CREATE VIEW vw_all_tables AS
SELECT 
TEST_TABLE_A
,TEST_TABLE_B
,TEST_TABLE_C
,TEST_TABLE_D
FROM
TEST_TABLE_A
,TEST_TABLE_B
,TEST_TABLE_C
,TEST_TABLE_D

Open in new window


still very wrong. I've not made views before so forgive this for seeming ridiculously easy or silly
0
 
slightwv (䄆 Netminder) Commented:
As long as the data types ALL match, a union all or union query depending on what you want:

CREATE VIEW vw_all_tables AS
SELECT col1,col2,col3 TEST_TABLE_A
union all
SELECT col1,col2,col3 TEST_TABLE_B
union all
SELECT col1,col2,col3 TEST_TABLE_C
union all
SELECT col1,col2,col3 TEST_TABLE_D


The column names can be different but the data types must all match,

So if col2 in table_a is a date,  the second column in all the rest of the selects must be a date.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
slightwv (䄆 Netminder) Commented:
The difference in 'union all' and 'union' is duplicate removal which requires a sort operation.
0
 
Mark_CoAuthor Commented:
How about a regexp? How can I use a regexp to answer this?
0
 
Mark_CoAuthor Commented:
I apologize but I need to make a regexp statement that selects
TEST_TABLE_A
,TEST_TABLE_B
,TEST_TABLE_C
,TEST_TABLE_D
0
 
Mark_CoAuthor Commented:
Just the table names, not the data of the tables
0
 
Mark_CoAuthor Commented:
sort of like this

SELECT
*
FROM User_Tables
WHERE TABLE_NAME = regexp_like('TEST_TABLE_', 'A' || 'B' || 'C' || 'D')

Open in new window

0
 
slightwv (䄆 Netminder) Commented:
So you want all table names in a database/schema that start with TEST_TABLE ?

select table_name from user_tables where table_name like 'TEST_TABLE_%';

replace user_tables with either all_tables or dba_tables depending on what you need.
0
 
Mark_CoAuthor Commented:
I know, but would you be able to make that into a regexp for me? That's what I need. Or can you see what is wrong with mine?
0
 
slightwv (䄆 Netminder) Commented:
Note the '_' is a single character wildcard and 'can' bring back false positives.

You can use the ESCAPE clause to get around that:

drop table test1table1a purge;
drop table test_table_a purge;

create table test1table1a (col1 char(1));
create table test_table_a (col1 char(1));

select table_name from user_tables where table_name like 'TEST\_TABLE\_%' escape '\';

Open in new window

0
 
slightwv (䄆 Netminder) Commented:
Zero to many alpha-numeric characters after TEST_TABLE:
select table_name from user_tables where regexp_like(table_name,'TEST_TABLE_[[:alnum:]]*');
0
 
slightwv (䄆 Netminder) Commented:
To 'correct' your attempt using the 'or' operator:
select table_name from user_tables where regexp_like(table_name,'TEST_TABLE_(A|B|C|D)');
0
 
Mark_CoAuthor Commented:
Thanks!!!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.