| Dynamically add alias to column name for a view. [message #649720] |
Mon, 04 April 2016 14:37  |
 |
lallaj
Messages: 1
Registered: April 2016
|
Junior Member |
|
|
I am looking to create a view.
Select *
from Table a, Table b
where a.id = b.id
There are columns that are duplicated - I do not want to expand and add all column names.
i.e I would like some way where I can determine the alias "a" and append it to the column name.
Is there some way to do this besides generating the plsql and redefining the view.
|
|
|
|
|
|
|
|
| Re: Dynamically add alias to column name for a view. [message #649726 is a reply to message #649720] |
Mon, 04 April 2016 17:38  |
 |
Barbara Boehmer
Messages: 9097
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
Here is an example procedure that will accept as parameters a view name, two table names and two column names for one join condition, then output the view syntax and create the view. You can modify the procedure for more tables and columns if you like. You can also modify it to just output the view syntax or just create the view. You can also convert it to an anonymous pl/sql block and substitute values for the parameters or use SQL*Plus substitution variables. I have provided a sample execution following the example procedure.
-- procedure:
create or replace procedure create_view
(p_viewname IN varchar2,
p_table1 IN varchar2,
p_table2 IN varchar2,
p_joincol1 IN varchar2,
p_joincol2 IN varchar2)
as
v_sql varchar2(4000);
begin
v_sql := 'create or replace view ' || p_viewname || ' as' || chr(10) || 'select ';
for r in
(select column_name
from user_tab_columns
where table_name = upper (p_table1)
intersect
select column_name
from user_tab_columns
where table_name = upper (p_table2))
loop
v_sql := v_sql || 'a.' || r.column_name || ' a_' || r.column_name || ', ';
end loop;
for r in
(select column_name
from user_tab_columns
where table_name = upper (p_table1)
and column_name not in
(select column_name
from user_tab_columns
where table_name = upper (p_table2)))
loop
v_sql := v_sql || r.column_name || ', ';
end loop;
v_sql := v_sql || chr(10) || ' ';
for r in
(select column_name
from user_tab_columns
where table_name = upper (p_table1)
intersect
select column_name
from user_tab_columns
where table_name = upper (p_table2))
loop
v_sql := v_sql || 'b.' || r.column_name || ' b_' || r.column_name || ', ';
end loop;
for r in
(select column_name
from user_tab_columns
where table_name = upper (p_table2)
and column_name not in
(select column_name
from user_tab_columns
where table_name = upper (p_table1)))
loop
v_sql := v_sql || r.column_name || ', ';
end loop;
v_sql := rtrim (v_sql, ', ') || chr(10) || 'from ' || p_table1 || ' a, ' || p_table2 || ' b' || chr(10);
v_sql := v_sql || 'where a.' || p_joincol1 || ' = b.' || p_joincol2;
dbms_output.put_line (v_sql);
execute immediate v_sql;
end create_view;
/
-- execution:
SYS@orcl> grant create view to scott
2 /
Grant succeeded.
SYS@orcl> connect scott/tiger
Connected.
SCOTT@orcl> create or replace procedure create_view
2 (p_viewname IN varchar2,
3 p_table1 IN varchar2,
4 p_table2 IN varchar2,
5 p_joincol1 IN varchar2,
6 p_joincol2 IN varchar2)
7 as
8 v_sql varchar2(4000);
9 begin
10 v_sql := 'create or replace view ' || p_viewname || ' as' || chr(10) || 'select ';
11 for r in
12 (select column_name
13 from user_tab_columns
14 where table_name = upper (p_table1)
15 intersect
16 select column_name
17 from user_tab_columns
18 where table_name = upper (p_table2))
19 loop
20 v_sql := v_sql || 'a.' || r.column_name || ' a_' || r.column_name || ', ';
21 end loop;
22 for r in
23 (select column_name
24 from user_tab_columns
25 where table_name = upper (p_table1)
26 and column_name not in
27 (select column_name
28 from user_tab_columns
29 where table_name = upper (p_table2)))
30 loop
31 v_sql := v_sql || r.column_name || ', ';
32 end loop;
33 v_sql := v_sql || chr(10) || ' ';
34 for r in
35 (select column_name
36 from user_tab_columns
37 where table_name = upper (p_table1)
38 intersect
39 select column_name
40 from user_tab_columns
41 where table_name = upper (p_table2))
42 loop
43 v_sql := v_sql || 'b.' || r.column_name || ' b_' || r.column_name || ', ';
44 end loop;
45 for r in
46 (select column_name
47 from user_tab_columns
48 where table_name = upper (p_table2)
49 and column_name not in
50 (select column_name
51 from user_tab_columns
52 where table_name = upper (p_table1)))
53 loop
54 v_sql := v_sql || r.column_name || ', ';
55 end loop;
56 v_sql := rtrim (v_sql, ', ') || chr(10) || 'from ' || p_table1 || ' a, ' || p_table2 || ' b' || chr(10);
57 v_sql := v_sql || 'where a.' || p_joincol1 || ' = b.' || p_joincol2;
58 dbms_output.put_line (v_sql);
59 execute immediate v_sql;
60 end create_view;
61 /
Procedure created.
SCOTT@orcl> show errors
No errors.
SCOTT@orcl> execute create_view ('test_view', 'dept', 'emp', 'deptno', 'deptno')
create or replace view test_view as
select a.DEPTNO a_DEPTNO, DNAME, LOC,
b.DEPTNO b_DEPTNO, HIREDATE, JOB, EMPNO, COMM, SAL, ENAME, MGR
from dept a,
emp b
where a.deptno = b.deptno
PL/SQL procedure successfully completed.
SCOTT@orcl> set linesize 150
SCOTT@orcl> select * from test_view
2 /
A_DEPTNO DNAME LOC B_DEPTNO HIREDATE JOB EMPNO COMM SAL ENAME MGR
---------- -------------- ------------- ---------- --------------- --------- ---------- ---------- ---------- ---------- ----------
10 ACCOUNTING NEW YORK 10 Tue 09-Jun-1981 MANAGER 7782 2450 CLARK 7839
10 ACCOUNTING NEW YORK 10 Tue 17-Nov-1981 PRESIDENT 7839 5000 KING
10 ACCOUNTING NEW YORK 10 Sat 23-Jan-1982 CLERK 7934 1300 MILLER 7782
20 RESEARCH DALLAS 20 Thu 02-Apr-1981 MANAGER 7566 2975 JONES 7839
20 RESEARCH DALLAS 20 Thu 03-Dec-1981 ANALYST 7902 3000 FORD 7566
20 RESEARCH DALLAS 20 Sat 23-May-1987 CLERK 7876 1100 ADAMS 7788
20 RESEARCH DALLAS 20 Wed 17-Dec-1980 CLERK 7369 800 SMITH 7902
20 RESEARCH DALLAS 20 Sun 19-Apr-1987 ANALYST 7788 3000 SCOTT 7566
30 SALES CHICAGO 30 Sun 22-Feb-1981 SALESMAN 7521 500 1250 WARD 7698
30 SALES CHICAGO 30 Tue 08-Sep-1981 SALESMAN 7844 0 1500 TURNER 7698
30 SALES CHICAGO 30 Fri 20-Feb-1981 SALESMAN 7499 300 1600 ALLEN 7698
30 SALES CHICAGO 30 Thu 03-Dec-1981 CLERK 7900 950 JAMES 7698
30 SALES CHICAGO 30 Fri 01-May-1981 MANAGER 7698 2850 BLAKE 7839
30 SALES CHICAGO 30 Mon 28-Sep-1981 SALESMAN 7654 1400 1250 MARTIN 7698
14 rows selected.
|
|
|
|
| Re: Dynamically add alias to column name for a view. [message #649758 is a reply to message #649720] |
Tue, 05 April 2016 06:57 |
 |
EdStevens
Messages: 1376
Registered: September 2013
|
Senior Member |
|
|
lallaj wrote on Mon, 04 April 2016 14:37
There are columns that are duplicated - I do not want to expand and add all column names.
Why not?
That's called "programming".
It's what we're paid to do.
Using 'select *' should never be done for anything but an ad hoc, one-off query.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
