This related to my question here Getting Error while creating a view in oracle DB
I tried the below,but getting duplicate Error column at line 6
CREATE VIEW ReturnCode90
AS
SELECT t.ret_code,
TO_CHAR (t.creation_dt, 'DD-MON-YYYY HH24:MI:SS') Created_Date,
CURRENT_TIMESTAMP Curr_tmp,
t.*
FROM buff.log t
WHERE t.ret_Code = '90'
order by t.creation_dt desc;
You are creating a view with the same column ret_code twice; if you the same column to appera twice in the view, you need to use an alias for one of the two occurrences:
CREATE VIEW ReturnCode90
AS
SELECT t.ret_code as someAlias,
TO_CHAR (t.imx_creation_dt, 'DD-MON-YYYY HH24:MI:SS') Created_Date,
CURRENT_TIMESTAMP Curr_tmp,
t.*
FROM imxbuff.bank_imx_pmt_head t
WHERE t.ret_Code = '90'
order by t.imx_creation_dt desc;
If you don't want this, you can remove the column from the select list
SELECT TO_CHAR (t.imx_creation_dt, 'DD-MON-YYYY HH24:MI:SS') Created_Date,
CURRENT_TIMESTAMP Curr_tmp,
t.*
or, better, explicitly write the column names instead of using *:
SELECT t.ret_code,
TO_CHAR (t.imx_creation_dt, 'DD-MON-YYYY HH24:MI:SS') Created_Date,
CURRENT_TIMESTAMP Curr_tmp,
t.otherColumn1,
t.otherColumn2,
...
