| Data validation in staging table [message #610312] |
Wed, 19 March 2014 06:33  |
 |
anujindia12
Messages: 4
Registered: March 2014
|
Junior Member |
|
|
Hi guys need urgent help for following problem.
I have 2 tables emp1(ename,empno,job,mgr,hiredate,sal,comm,deptno)
and dept1(deptno,dname,loc), i filled some data into following staging table
emp_validate_staging(empno,ename,deptno,dname,status) which has both correct and incorrect data.
Now i have t write a package which validates the data given in the staging table(Eg- the following data exists/not exists in emp1/dept1 table, use seperate blocks for seperate tables) and update the status in the staging table with 'W','R'. if the status is 'R' then insert that data into another table emp_valid_transaction or else should throw an exception.
Thanks
|
|
|
|
| Re: Data validation in staging table [message #610316 is a reply to message #610312] |
Wed, 19 March 2014 07:02  |
John Watson
Messages: 8931
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Would I be right to think that this is a college homework question? If so:
I would begin be defining the validations you need. For example, if an EMPNO in your staging table already exists, is it the same ENAME and DEPTNO or not? If not, should you discard the row, or merge it into the existing row? What if a staging DEPTNO/DNAME combinaton does not exist? INSERT into DEPT1, discard, or update?
When you define these validations, you will probably find that the SQL becomes obvious.
|
|
|
|
| Re: Data validation in staging table [message #610317 is a reply to message #610316] |
Wed, 19 March 2014 07:13 |
|
anujindia12
Messages: 4
Registered: March 2014
|
Junior Member |
|
|
No actually a task given to me in office. Since new to programming so m not able to get it right, actually i tried the following but its partially working......
create or replace package body XXANUJ_VALIDATE_PKG is
FUNCTION emp1_validate(e_id number) return number is -- Emp1 validate Function
rec_count number;
begin
select count(*) into rec_count from emp1 where empno = e_id;
return rec_count;
end emp1_validate;
FUNCTION dept1_validate(d_id number) return number is --Dept1 validate Function
rec1_count number;
begin
select count(*) into rec1_count from dept1 where deptno = d_id;
return rec1_count;
end dept1_validate;
FUNCTION emp1_status(e_id number, d_id number) return number is --Emp1 Status Function
status number;
begin
update emp_validate_staging
set status = 'W'
where empno = e_id and deptno = d_id;
status := 0;
return status;
end emp1_status;
FUNCTION dept1_status(e_id number, d_id number) return number is --Dept1 Status Function
status number;
begin
update emp_validate_staging
set status = 'R'
where empno = e_id and deptno = d_id;
status := 1;
return status;
end dept1_status;
procedure data_validate is -- Procedure to verify the data
cursor c1 is
select empno, ename,deptno, dname from emp_validate_staging;
c_record c1%rowtype;
emp_stat number;
dep_stat number;
begin
open c1;
loop
fetch c1
into c_record;
exit when c1%notfound;
emp_stat := emp1_validate(c_record.empno);
dep_stat := dept1_validate(c_record.deptno);
if (emp_stat = 0 and dep_stat = 0) then
dbms_output.put_line(c_record.empno ||
'does not exist in the base table');
dbms_output.put_line(c_record.deptno ||
'does not exist in the base table');
elsif (emp_stat != 0 and dep_stat != 0) then
dbms_output.put_line(c_record.empno || 'exists in the base table');
dbms_output.put_line(c_record.deptno || 'exists in the base table');
INSERT INTO emp_valid_transaction
(empno, ename,deptno, dname)
select empno, ename, deptno, dname
from emp_validate_staging
where status = 'R';
commit;
exit;
end if;
/*if data_stat = 1 then
\*INSERT INTO emp_valid_transaction
(empno, ename,deptno, dname)
select empno, ename, deptno, dname
from emp_validate_staging
where status = 'R';
commit;*\
else
exit;
end if;*/
end loop;
close c1;
end data_validate;
procedure main is
begin
data_validate;
end;
end XXANUJ_VALIDATE_PKG;
now it shows that the data is right or not but does not insert.
and deptno is the common column from both the tables.
|
|
|
|
| Re: Data validation in staging table [message #610320 is a reply to message #610317] |
Wed, 19 March 2014 07:21 |
John Watson
Messages: 8931
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
I do not know what you mean by "the data is right". This is what I was trying to say: you have to define what your validations are, nd what to do next. Your code as written is checking whether the rows in the staging table have EMPNO and DEPTNO that already exist, but then you do nothing with this information.
You should also consider whether you need to use any PL/SQL at all. You my find the SQL is adequate.
|
|
|
|
|
|
|
|
| Re: Data validation in staging table [message #610332 is a reply to message #610330] |
Wed, 19 March 2014 07:46 |
John Watson
Messages: 8931
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
anujindia12 wrote on Wed, 19 March 2014 12:40It means that the data is matching from both the emp and dept tables, since the staging table has data from these two tables so only that data is valid which matches from both tables. And I do have to use package,functions and procedures Thank you for explaining the validation. It is now clear.
You do NOT have to use package,functions and procedures: you can to do this with one SQL statement, which will insert into your valid transactions table all rows from your staging table that have matching values in the emp and dept tables.
|
|
|
|
|
|
|
|
| Re: Data validation in staging table [message #610364 is a reply to message #610333] |
Wed, 19 March 2014 10:14 |
John Watson
Messages: 8931
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
anujindia12 wrote on Wed, 19 March 2014 12:52He he thanks but the problem this task which my team lead assigned it to me have mentioned that I need to use all of these..... THANKS
BUT STILL PLEASE GIVE THE SQL QUERY ALSO Your team leader is wrong. You do not need to use all of these. I suggest that you write it the slow way, using pl/sql, and post the solution here.
Then try to write it in one SQL statement, and if you get stuck people here will help.
And when it is working (it will not take more than a few minutes) you can go to your team leader and tell him he is an ignoramus.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
