Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Creating a view that selects 4 specific table names

Posted on 2012-08-13
14
Medium Priority
?
355 Views
Last Modified: 2012-08-13
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
0
Comment
Question by:Mark_Co
  • 7
  • 7
14 Comments
 

Author Comment

by:Mark_Co
ID: 38289923
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
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38289931
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
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38289934
The difference in 'union all' and 'union' is duplicate removal which requires a sort operation.
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

Author Comment

by:Mark_Co
ID: 38289935
How about a regexp? How can I use a regexp to answer this?
0
 

Author Comment

by:Mark_Co
ID: 38289941
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
 

Author Comment

by:Mark_Co
ID: 38289945
Just the table names, not the data of the tables
0
 

Author Comment

by:Mark_Co
ID: 38289950
sort of like this

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

Open in new window

0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38289974
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
 

Author Comment

by:Mark_Co
ID: 38289985
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
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38289989
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
 
LVL 78

Accepted Solution

by:
slightwv (䄆 Netminder) earned 2000 total points
ID: 38289994
>>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
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38290001
Zero to many alpha-numeric characters after TEST_TABLE:
select table_name from user_tables where regexp_like(table_name,'TEST_TABLE_[[:alnum:]]*');
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38290002
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
 

Author Comment

by:Mark_Co
ID: 38290012
Thanks!!!!
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Suggested Courses

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question