Compare contents of Clob

I need to loop through a table and compare the contents of a clob that have the same clob_num, find a match if it exists, and then look at the dates and keep the first entry of it, and if its not the first entry then set the delete column to 'Y'.

Expected Results:
1.Using the test data below, all the rows that have a clob_num of 5555 would have their clob_Contents compared, rows with the clob_pks 123,124, and 125 match and because clob_pk 124 has the earliest date, clob_pk 123 and 125 would have their delete columns set to 'Y'.

2. Clob_contents of rows with clob_num 5556 are compared, no matches are found so the delete column stays as 'N'

Test Data:

Create table tab1 (clob_pk varchar2(3),clob_num varchar2(4),clob_date date, clob_contents clob, delete char(1));

insert all into tab1 values ('123', '5555','5/5/2015','This is a test','N')
into tab1 values ('124', '5555','5/2/2015','This is a test','N')
into tab1 values ('125', '5555','5/3/2015','This is a test','N')
into tab1 values ('126', '5555','5/5/2015','This is a test and a hard one too','N')
into tab1 values ('127', '5556','5/5/2015','This is a test and another one','N')
into tab1 values ('128', '5556','5/4/2015','This is a test and a hard one too','N')
into tab1 values ('129', '5556','5/5/2015','This is a test','N')
select * from dual;

Open in new window

DBAnewbie77Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
You'll need to be granted execute on the dbms_crypto package to do this but give this a try:
update tab1 set delete_col='Y'
where clob_pk in (
select clob_pk from (
select clob_pk,
	row_number() over(partition by clob_num, dbms_crypto.hash(clob_contents,2) order by clob_date asc) rn
from tab1
) where rn > 1
)
/

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mike EghtebasDatabase and Application DeveloperCommented:
Update d Set delete_clob = 'Y'
From (SELECT  clob_pk, clob_num, clob_date, clob_contents, delete_clob 
    ,RANK() OVER (PARTITION BY clob_num ORDER BY clob_pk DESC) AS TheRank
From #t ) As d
WHERE TheRank > 1

Open in new window

0
slightwv (䄆 Netminder) Commented:
eghtebas,

That doesn't appear to be Oracle Syntax.

It also appear to not solve the problem since I don't see where it compares the CLOB values when deciding to set the delete column or not.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Mike EghtebasDatabase and Application DeveloperCommented:
I created a temp table and tested in SQL Server. I think it should run in Oracle as well.

The inner query produces:
SELECT  clob_pk, clob_num, clob_date, clob_contents, delete_clob 
    ,RANK() OVER (PARTITION BY clob_num ORDER BY clob_pk DESC) AS TheRank
From #t 
returns:
126	5555	2015-05-05	This is a test and a hard one too	N	1
125	5555	2015-05-03	This is a test	                        Y	2
124	5555	2015-05-02	This is a test	                        Y	3
123	5555	2015-05-05	This is a test	                        Y	4
129	5556	2015-05-05	This is a test	                        N	1
128	5556	2015-05-04	This is a test and a hard one too	Y	2
127	5556	2015-05-05	This is a test and another one	        Y	3

Open in new window


Now, following excludes all rows with rank = 1

WHERE TheRank > 1

And it works fine.

Mike
0
slightwv (䄆 Netminder) Commented:
>> I think it should run in Oracle as well.

It won't.  I suggest sqlfiddle.

>>The inner query produces:

That is fine but I don't think that is the correct results.

From the original post I think the only 'Y' values should be 123 and 125.
0
Mike EghtebasDatabase and Application DeveloperCommented:
I took 126 and 129 in each type have highest auto number meaning they are the latest. If I have misunderstood this then the following line should change as highlighted:

,RANK() OVER (PARTITION BY clob_num ORDER BY clob_pk DESC) AS TheRank
,RANK() OVER (PARTITION BY clob_num ORDER BY clob_pk ASC) AS TheRank
0
slightwv (䄆 Netminder) Commented:
I used the words of the asker.

You missed "table and compare the contents of a clob that have the same clob_num"

Read the "Expected results".  Everything for clob_pk 5556 remains a 'N'.  Only 123 and 125 are set to a 'Y'.
0
Mike EghtebasDatabase and Application DeveloperCommented:
I agree, the following line should do it then:
,RANK() OVER (PARTITION BY clob_num ORDER BY clob_pk ASC) AS TheRank
0
slightwv (䄆 Netminder) Commented:
>>the following line should do it then:

No it won't.

That only ranks by clob_pk.  Nothing takes the clob_contents values into consideration or the date.

AGAIN:  The expected results are driven by the CONTENTS OF THE CLOB and the date.  Nothing you have posted touches the clob_contents column.

Please go to sqlfiddle and set up an Oracle test case.  Once you get the actual expected results, and if it is different than mine, post the update statement here along with the sqlfiddle link.
0
Mike EghtebasDatabase and Application DeveloperCommented:
slightwv,

I had basically given the short and sweet structure to do the job. I was not expecting for it to be graded like school work. I am sure DBAnewbie77 is familar with order by and where clause. I suppose the following will be satisfactory to you. If so, you may also agree this is a better solution.

Mike

Update d Set delete_clob = 'Y'
From (SELECT  clob_pk, clob_num, clob_date, clob_contents, delete_clob 
    ,RANK() OVER (PARTITION BY clob_num ORDER BY clob_date) AS TheRank
From #t ) As d
WHERE TheRank > 1

Open in new window

0
slightwv (䄆 Netminder) Commented:
>>I had basically given the short and sweet structure to do the job

But the update you posted does not provide the correct results.
It is not Oracle Syntax.

There is nothing about it that even begins to answer the question.
0
slightwv (䄆 Netminder) Commented:
>>If so, you may also agree this is a better solution.

Still wrong.  It is all about the CLOB_CONTENTS field and repeating values.

Care to explain how your latest attempt accounts for that?
0
Mike EghtebasDatabase and Application DeveloperCommented:
From this thread, I have learned to read the posts carefully. You are correct and thank you for your patience.

Mike
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.