Method to Create New Column in Oracle Table with Combination Other Columns

advertisements

_____________________________________________________________________________________________________________________

The Eucharistic Miracles of the World
It is not a regular requirement. But sometimes we need it such cases. This can be achieved by the virtual columns in the Oracle.

See some examples below.

SQL>alter table emp add name_job as (ename||'-'||job);
Table altered.

SQL> select * from emp;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO NAME_JOB
---------- ---------- --------- ---------- --------- ---------- ---------- ---------- --------------------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20 SMITH-CLERK
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30 ALLEN-SALESMAN
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30 WARD-SALESMAN
 
14 rows selected.

You should not use + symbol because the target column will get create with number as data type. Then you will get ORA-01722 error message while selecting from the table.

SQL> alter table emp add name_job as (ENAME+'-'+job);
Table altered.

SQL> select * from emp;
select * from emp
*
ERROR at line 1:
ORA-01722: invalid number
 
Method to create combination column of 2 columns with different data type.
alter table emp add name_sal as (ename||'-'||to_char(sal));
 
  1*  select * from emp
SQL> /

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO NAME_SAL
---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ----------------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20 SMITH-800
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30 ALLEN-1600
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30 WARD-1250

_____________________________________________________________________________________________________________________

Website Stats

0 comments:

Post a Comment

Labels

Oracle (629) Script (86) General (77) Unix (47) Blog (23) Technology (19) gadget (6) games (6) Business (3) OCI (3) SQL* Loader (3) Datapump (2)
 

acehints.com Copyright 2011-23 All Rights Reserved | Site Map | Contact | Disclaimer