Home » SQL & PL/SQL » SQL & PL/SQL » SQL Querry to return database columns which got updated
icon6.gif  SQL Querry to return database columns which got updated [message #213698] Thu, 11 January 2007 23:14 Go to next message
vikram1780
Messages: 222
Registered: January 2007
Location: Bangalore
Senior Member
select decode(((t1.dname<>t2.dname)or(t1.dname is null and t2.dname is not null) or
(t1.dname is not null and t2.dname is null)),true,1,false,0,0) from emp_mst t1, tgt_emp t2;


Hey let me know that decode doesnt works for <>,!=;

Here is my querry. I request you to help me in sorting out this problem.

EMP_MST (Master Table)IT HOLDS master data.
NC_MST (Same structure as EMP_MST): It holds the net change data that we had between EMP_MST and TGT_EMP(Before refreshed)
TGT_EMP (Materialized view created on based on EMP_MST it holds same data as EMP_MST).


I just need a database columns which have been changed from EMP_MST comparing with TGT_EMP.

EMP_MST

empno ename
1 a
2 b

NC_EMP

empno ename
1 a

TGT_EMP

empno ename
1 c
2 b

Result what I need is the database column which has been changed.

Here i have presented with some simple example.

Any help really apprecited alot.

Thanks
Vikram

[Updated on: Thu, 11 January 2007 23:24]

Report message to a moderator

Re: SQL Querry to return database columns which got updated [message #213702 is a reply to message #213698] Thu, 11 January 2007 23:39 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
I didn't understand from where the dname comes into picture.

Quote:
Hey let me know that decode doesnt works for <>,!=;

Why don't you use CASE in this scenario?

By
Vamsi
Re: SQL Querry to return database columns which got updated [message #213704 is a reply to message #213702] Thu, 11 January 2007 23:54 Go to previous messageGo to next message
vikram1780
Messages: 222
Registered: January 2007
Location: Bangalore
Senior Member
There are more than 50 columns in each table
Re: SQL Querry to return database columns which got updated [message #213706 is a reply to message #213702] Thu, 11 January 2007 23:56 Go to previous messageGo to next message
vikram1780
Messages: 222
Registered: January 2007
Location: Bangalore
Senior Member
Its ename by mistakely i have written dname. Below i have expalined clearly with example
Re: SQL Querry to return database columns which got updated [message #213708 is a reply to message #213702] Fri, 12 January 2007 00:07 Go to previous messageGo to next message
vikram1780
Messages: 222
Registered: January 2007
Location: Bangalore
Senior Member
Hi Vamshi,

Here is the querry which i tried with case

select case ((t1.dname<>t2.dname)or(t1.dname is null and t2.dname is not null) or
(t1.dname is not null and t2.dname is null)) then 'dname'
else 'null' end from emp_mst t1, tgt_emp t2;

Hence it is giving same error as missing parenthesis.

Thanks for your reply
Re: SQL Querry to return database columns which got updated [message #213711 is a reply to message #213708] Fri, 12 January 2007 00:33 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
Quote:


EMP_MST (Master Table)IT HOLDS master data.
...
TGT_EMP (Materialized view created on based on EMP_MST it holds same data as EMP_MST).




If TGT_EMP is Materialized view based on EMP_MST and holds a same data as EMP_MST then this
I just need a database columns which have been changed from EMP_MST comparing with TGT_EMP.
does not make sense to me because as soon as MV is refreshed TGT_EMP will be same as EMP_MST and hence you cannot know changed values by joining these two.


If I have misunderstood the question then please clarify.


Re: SQL Querry to return database columns which got updated [message #213713 is a reply to message #213708] Fri, 12 January 2007 00:40 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
You should put 'case when'

By
Vamsi
Re: SQL Querry to return database columns which got updated [message #213717 is a reply to message #213708] Fri, 12 January 2007 00:53 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
You missed the WHEN word. Additionally you do not join t1 with t2, which would lead to excess of the result set, as cartesian join would be proceeded. I suppose empno is the row identificator.
select t1.dname, t2.dname,
  case when ((t1.dname<>t2.dname)or(t1.dname is null and t2.dname is not null) or
  (t1.dname is not null and t2.dname is null)) then 'dname'
  else 'null' end
from emp_mst t1 full outer join tgt_emp t2 using (empno);
Re: SQL Querry to return database columns which got updated [message #213723 is a reply to message #213717] Fri, 12 January 2007 01:09 Go to previous messageGo to next message
vikram1780
Messages: 222
Registered: January 2007
Location: Bangalore
Senior Member
Hey Thanks alot

Is there any way to use decode over here.


Thanks
Vikram
Re: SQL Querry to return database columns which got updated [message #213724 is a reply to message #213711] Fri, 12 January 2007 01:10 Go to previous messageGo to next message
vikram1780
Messages: 222
Registered: January 2007
Location: Bangalore
Senior Member
Before the materialzed view gets refreshed I have to find the updated database columns by comparing Master table and Materialized view.
Re: SQL Querry to return database columns which got updated [message #213725 is a reply to message #213713] Fri, 12 January 2007 01:11 Go to previous messageGo to next message
vikram1780
Messages: 222
Registered: January 2007
Location: Bangalore
Senior Member
Yeh thanks Vamsi

Now its working.

Is there anyway to use decode over here.

Thanks
Vikram
Re: SQL Querry to return database columns which got updated [message #213766 is a reply to message #213725] Fri, 12 January 2007 04:18 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
Quote:


Is there anyway to use decode over here.





select t1.dname, t2.dname,
decode(t1.dname, t2.dname,'null','dname') 
from emp_mst t1 full outer join tgt_emp t2 using (empno);




Re: SQL Querry to return database columns which got updated [message #214104 is a reply to message #213717] Sun, 14 January 2007 23:52 Go to previous messageGo to next message
vikram1780
Messages: 222
Registered: January 2007
Location: Bangalore
Senior Member

This is the function which I have prepared to get updated columns. It is producing the follwing querry.

create or replace FUNCTION test_updcolumns
(fieldsfrom VARCHAR2, outtable varchar2,
delimiter VARCHAR2 := ',', qualifier VARCHAR2 := 't1'
) RETURN LONG AS
strnonkeysinequate LONG := '';
strdbcols Long:='';
strdbequate LONG:='';
strnonkeys LONG := '';
strincolumn LONG := '';
stroutcolumn LONG := '';
strkeysequate LONG:='';
BEGIN
strkeysequate := test_getkeycolumns(intable=>fieldsfrom, equate=>TRUE);
FOR nonkeys IN (SELECT column_name
FROM user_tab_columns
WHERE TABLE_NAME = UPPER(fieldsfrom)
AND column_name NOT LIKE 'SYS_%' minus
SELECT ucc.column_name
FROM user_cons_columns ucc,
user_constraints uc
WHERE ucc.constraint_name = uc.constraint_name
AND ucc.TABLE_NAME = UPPER(fieldsfrom)
AND uc.constraint_type = 'P'
AND ucc.column_name NOT LIKE 'SYS_%')
LOOP
strincolumn := 't1' || '.' || nonkeys.column_name;
stroutcolumn := 't2' || '.' || nonkeys.column_name;
strdbequate := strdbcols || ' CASE WHEN( (' || strincolumn || ' <> ' ||
stroutcolumn || ') OR (' || strincolumn || ' IS NULL AND ' ||
stroutcolumn || ' IS NOT NULL )' || ' OR (' || strincolumn ||
' IS NOT NULL AND ' || stroutcolumn || ' IS NULL )' || ') THEN '
|| substr(strincolumn,4) ||' ELSE NULL END ';
END LOOP;
strdbcols:=' select ' || strdbequate || ' from ' || fieldsfrom || ' t1 ,' || outtable || ' t2 ' ||
' where ' || strkeysequate||';' ;
RETURN strdbcols;

END test_updcolumns;


declare
sql1 varchar2(32000);
handle utl_file.file_type;
begin
sql1:=test_updcolumns(fieldsfrom=>'EMP_MST',outtable=>'TGT_EMP');
handle:=utl_file.fopen('LOGAREA','sqlupd2.txt', 'w',32767);
utl_file.put_line(handle,sql1 );
utl_file.fflush(handle);
utl_file.fclose(handle);
exception
when others then
dbms_output.put_line(sqlcode);
dbms_output.put_line(sqlerrm);
end;

This is the querry which got produced from above function
. But while executing the querry there is syntax error i,e the updated database column name should be in single quotes. I request you people to find out the way inorder to get the database column name in single quotes. Its an urgent requirement for me. Pls do needfull asap.

select CASE WHEN( (t1.EMPNAME <> t2.EMPNAME) OR (t1.EMPNAME IS NULL AND t2.EMPNAME IS NOT NULL ) OR (t1.EMPNAME IS NOT NULL AND t2.EMPNAME IS NULL ))
THEN
EMPNAME (This should be displayed in single quotes)
ELSE NULL END
from EMP_MST t1 ,TGT_EMP t2 where t1.EMPNO = t2.EMPNO ;


Please do required changes to the function and let me know.

Thanks a lot

Re: SQL Querry to return database columns which got updated [message #214105 is a reply to message #213698] Sun, 14 January 2007 23:53 Go to previous messageGo to next message
vikram1780
Messages: 222
Registered: January 2007
Location: Bangalore
Senior Member

This is the function which I have prepared to get updated columns. It is producing the follwing querry.

create or replace FUNCTION test_updcolumns
(fieldsfrom VARCHAR2, outtable varchar2,
delimiter VARCHAR2 := ',', qualifier VARCHAR2 := 't1'
) RETURN LONG AS
strnonkeysinequate LONG := '';
strdbcols Long:='';
strdbequate LONG:='';
strnonkeys LONG := '';
strincolumn LONG := '';
stroutcolumn LONG := '';
strkeysequate LONG:='';
BEGIN
strkeysequate := test_getkeycolumns(intable=>fieldsfrom, equate=>TRUE);
FOR nonkeys IN (SELECT column_name
FROM user_tab_columns
WHERE TABLE_NAME = UPPER(fieldsfrom)
AND column_name NOT LIKE 'SYS_%' minus
SELECT ucc.column_name
FROM user_cons_columns ucc,
user_constraints uc
WHERE ucc.constraint_name = uc.constraint_name
AND ucc.TABLE_NAME = UPPER(fieldsfrom)
AND uc.constraint_type = 'P'
AND ucc.column_name NOT LIKE 'SYS_%')
LOOP
strincolumn := 't1' || '.' || nonkeys.column_name;
stroutcolumn := 't2' || '.' || nonkeys.column_name;
strdbequate := strdbcols || ' CASE WHEN( (' || strincolumn || ' <> ' ||
stroutcolumn || ') OR (' || strincolumn || ' IS NULL AND ' ||
stroutcolumn || ' IS NOT NULL )' || ' OR (' || strincolumn ||
' IS NOT NULL AND ' || stroutcolumn || ' IS NULL )' || ') THEN '
|| substr(strincolumn,4) ||' ELSE NULL END ';
END LOOP;
strdbcols:=' select ' || strdbequate || ' from ' || fieldsfrom || ' t1 ,' || outtable || ' t2 ' ||
' where ' || strkeysequate||';' ;
RETURN strdbcols;

END test_updcolumns;


declare
sql1 varchar2(32000);
handle utl_file.file_type;
begin
sql1:=test_updcolumns(fieldsfrom=>'EMP_MST',outtable=>'TGT_EMP');
handle:=utl_file.fopen('LOGAREA','sqlupd2.txt', 'w',32767);
utl_file.put_line(handle,sql1 );
utl_file.fflush(handle);
utl_file.fclose(handle);
exception
when others then
dbms_output.put_line(sqlcode);
dbms_output.put_line(sqlerrm);
end;

This is the querry which got produced from above function
. But while executing the querry there is syntax error i,e the updated database column name should be in single quotes. I request you people to find out the way inorder to get the database column name in single quotes. Its an urgent requirement for me. Pls do needfull asap.

select CASE WHEN( (t1.EMPNAME <> t2.EMPNAME) OR (t1.EMPNAME IS NULL AND t2.EMPNAME IS NOT NULL ) OR (t1.EMPNAME IS NOT NULL AND t2.EMPNAME IS NULL ))
THEN
EMPNAME (This should be displayed in single quotes)
ELSE NULL END
from EMP_MST t1 ,TGT_EMP t2 where t1.EMPNO = t2.EMPNO ;


Please do required changes to the function and let me know.

Thanks a lot
Re: SQL Querry to return database columns which got updated [message #214151 is a reply to message #214105] Mon, 15 January 2007 03:04 Go to previous messageGo to next message
a_developer
Messages: 194
Registered: January 2006
Senior Member
just concatenate single quotes..

. . .
||''''|| substr(strincolumn,4) ||''''||' ELSE NULL END ';
. . .

[Updated on: Mon, 15 January 2007 03:07]

Report message to a moderator

Re: SQL Querry to return database columns which got updated [message #214171 is a reply to message #214151] Mon, 15 January 2007 04:44 Go to previous message
vikram1780
Messages: 222
Registered: January 2007
Location: Bangalore
Senior Member
Really I am very thankful to you. Its helped me a lot in resolving my problem.

I have used replaced function earlier. It was errorred out.

Thanks
Kumar
Previous Topic: COLLATE
Next Topic: NUMBER Datatype
Goto Forum:
  


Current Time: Wed Dec 07 10:28:07 CST 2016

Total time taken to generate the page: 0.12376 seconds