I want to load target table as totalsal for each record

I have source table emp in that columns are empno, ename, job, sal, comm, deptno, finally

I want to load target table as totalsal for each record (total sal=sal+comm), which transformation I have used.

one of the most simplest way is doing the same with an sql loader program.

Also you can use a External table and then do the change.

First take all data from source table into excel sheet.
Add one more column to the sheet name it total sal and do the calculation to join sal and comm column and place the sum in that column.Now save the Excel file in dat format.

Now write a ctl file maintaing the sequence and oonce u run the program. The data will be loaded.

There could be simple soln. trying to find that

create table totalsal as
select emp.*, (nvl(sal,0)+nvl(comm,0))totalsal from emp

This is the simplest solution.

[QUOTE=Ash4u]create table totalsal as
select emp.*, (nvl(sal,0)+nvl(comm,0))totalsal from emp

This is the simplest solution.[/QUOTE]

You are correct, but by using this, your target table totalsal will be having all the columns of Emp and this totalsal.

As per the thread, he wants to have totalsal column only,
[QUOTE=hanumca07]I have source table emp in that columns are empno, ename, job, sal, comm, deptno, finally

I want to load target table as totalsal for each record (total sal=sal+comm), which transformation I have used.[/QUOTE]

Therefore the Query will be,
[FONT="Courier New"][SIZE="2"][COLOR="DarkRed"]create table totalsal as
select (nvl(sal,0)+nvl(comm,0))totalsal from emp[/COLOR][/SIZE][/FONT]

Sanghala

Hi,


yes you are correct.Thanks for rectifying.

[quote=Ash4u;16724]Hi,


yes you are correct.Thanks for rectifying.[/quote]

Good +ve thinking... Thanks

[B]Sanghala[/B]