Adding Extra Column to View, Which Is Not Present in Table

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

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
);

SQL: How can I define an extra column that is not present in a table?

You can define an extra column by the selecting the column value or an expression. Optionally you can use 'as' and define your column name.
For example:

SELECT 
    T1.ID,
    T1.AMOUNT,
    T1.STATUS,
    T1.ADDEDBY,
    T1.ADDEDON,
    concat(T1.ID,T1.STATUS) as NEW_COLUMN1, //using an expression
    'output sample' as NEW_COLUMN2, //fill with a value
    1+3 AS NEW_COLUMN3 //using an expresion
FROM Table1 T1

I hope this can help you.
Regards

Adding auto increment extra column to view which is not present in table in SQL Server

You need to use row_number function

Schema:

CREATE TABLE #TAB (ID INT, MARKS INT)

INSERT INTO  #TAB
SELECT 1 ,  89   
UNION ALL
SELECT 2 ,  99  
UNION ALL
SELECT 4 ,  67 
UNION ALL
SELECT 6  , 77  

Do select the above table with Rownumber for Extra column

SELECT 
    ID, MARKS, 
    ROW_NUMBER() OVER(ORDER BY (SELECT 1)) EXTRA_COL 
FROM #TAB

The result will be

+----+-------+-----------+
| ID | MARKS | EXTRA_COL |
+----+-------+-----------+
|  1 |    89 |         1 |
|  2 |    99 |         2 |
|  4 |    67 |         3 |
|  6 |    77 |         4 |
+----+-------+-----------+

Is there a way to add a column to a view in ORACLE SQL?

You can do it as the way you've already described.

CREATE OR REPLACE VIEW V_PRODUCT
AS
   SELECT K.PRODUCT_ID,
      K.PRODUCT_UNIT_PRICE,
      S.SALES_UNIT_PRICE,
      S.SALES_UNIT_PRICE - K.PRODUCT_UNIT_PRICE AS PROFIT
 FROM TABLE_STOCK K
      INNER JOIN TABLE_SALES S ON S.PRODUCT_ID = K.PRODUCT_ID

---

Related Topics