CODEWITHSUNDEEP
sqloracle-database
user1181942
user1181942

Reputation: 1607

Adding extra column to view, which is not present in table

I want to create view with union of three tables. But in result query I want one extra column like 'tableId'.

My code is like

CREATE OR REPLACE VIEW DETAILS
AS SELECT 
* FROM
(
SELECT 
    T1.ID,
    T1.AMOUNT,
    T1.STATUS,
    T1.ADDEDBY,
    T1.ADDEDON
FROM Table1 T1
UNION ALL
SELECT 
    T2.ID,
    T2.AMOUNT,
    T2.STATUS,
    T2.ADDEDBY,
    T2.ADDEDON
FROM Table2 T2
UNION ALL
SELECT
    T3.ID,
    T3.BILLAMOUNT,
    T3.STATUS,
    T3.ADDEDBY,
    T3.ADDEDON  
FROM Table3 T3
);

This gives me union of required three tables. But how can i get table Id column in resulted output? This column is not present in any of the three tables.

Upvotes: 9

Views: 69847

Answers (6)

S.Yadav
S.Yadav

Reputation: 4509

What i did for the same.

  1. open phpmyadmin.
  2. select database.
  3. click on sql menu
  4. run following command as-
CREATE VIEW table_name AS SELECT tableA.id, tableA.name, tableA.duty_hours, tableA.rate, NULL AS 'additional_field1',NULL AS 'additional_field2', NULL AS 'additional_field3', tableB.name, tableB.email, tableB.charge, NULL AS 'additional_field4' FROM tableA join tableB using (id) ;

That worked for me.

Upvotes: 0

hurricane
hurricane

Reputation: 6724

There is a better solution for this;

NULL AS COLUMN_NAME is an identifier to.

CREATE VIEW MY_VIEW
    AS
        (SELECT A.*,NULL AS COLUMN1,NULL AS COLUMN2,NULL AS COLUMN3
            FROM MY_TABLE A)

Upvotes: 0

kanha senapati
kanha senapati

Reputation: 1

create or replace view view1(col1,col2,col3) as select col1,col2,nul

Upvotes: -2

juergen d
juergen d

Reputation: 204766

CREATE OR REPLACE VIEW DETAILS
AS SELECT 
* FROM
(
SELECT 
    T1.ID,
    T1.AMOUNT,
    T1.STATUS,
    T1.ADDEDBY,
    T1.ADDEDON,
    'Table1' as tableid
FROM Table1 T1
UNION ALL
SELECT 
    T2.ID,
    T2.AMOUNT,
    T2.STATUS,
    T2.ADDEDBY,
    T2.ADDEDON,
    'Table2' as tableid
FROM Table2 T2
UNION ALL
SELECT
    T3.ID,
    T3.BILLAMOUNT as AMOUNT,
    T3.STATUS,
    T3.ADDEDBY,
    T3.ADDEDON,    
    'Table3' as tableid
FROM Table3 T3
);

Upvotes: 7

Justin Cave
Justin Cave

Reputation: 231661

It sounds like you just want to add an additional hard-coded value to your SELECT list

CREATE OR REPLACE VIEW DETAILS
AS SELECT 
* FROM
(
SELECT 
    T1.ID,
    T1.AMOUNT,
    T1.STATUS,
    T1.ADDEDBY,
    T1.ADDEDON,
    'T1' tableID
FROM Table1 T1
UNION ALL
SELECT 
    T2.ID,
    T2.AMOUNT,
    T2.STATUS,
    T2.ADDEDBY,
    T2.ADDEDON,
    'T2' tableID
FROM Table2 T2
UNION ALL
SELECT
    T3.ID,
    T3.BILLAMOUNT,
    T3.STATUS,
    T3.ADDEDBY,
    T3.ADDEDON,
    'T3' tableID  
FROM Table3 T3
);

Upvotes: 14

Thilo
Thilo

Reputation: 262504

Where does the data for the column come from?

You can add completely static data if that is what you are looking for:

select 'hello there' from Table1;

create view v1 as select 123 as table_id, a_column from Table2;

select 'Table1' as table_id, a_column from Table1
union all
select 'Table2', a_column from Table2

Upvotes: 0

Related Questions