Search case insensitive all duplicate records and print out

Toad for Oracle & DevOps Toolkit
1

Hi folks

Is this SQL correct? I wish to print out all duplicate records (case
insensitive). I have zero output and not sure if no duplicates or
wrong SQL statement.

SELECT lower(leaf_user_name), COUNT (lower(leaf_user_name)) AS numoccurrences
FROM rss_user
WHERE rss_name = ‘sunone_west’
GROUP BY leaf_user_name
HAVING COUNT (lower(leaf_user_name)) > 1;

Is there a Toad way to do the same?

cheers Sven

2

The GROUP BY should also be (lower(leaf_user_name)). And I would use COUNT(*)
but that makes no difference as far as I know.

Did you try this query? I would expect errors because of the fact that the group
by does not match the first select expression.

Groetjes,
Wim

On Tue, Mar 1, 2011 at 13:05, Sven Aluoor wrote:

Hi folks

Is this SQL correct? I wish to print out all duplicate records (case
insensitive). I have zero output and not sure if no duplicates or
wrong SQL statement.

SELECT lower(leaf_user_name), COUNT (lower(leaf_user_name)) AS
numoccurrences
FROM rss_user
WHERE rss_name = 'sunone_west'
GROUP BY leaf_user_name
HAVING COUNT (lower(leaf_user_name)) > 1;

Is there a Toad way to do the same?

cheers Sven
3

On Wed, Mar 2, 2011 at 7:56 AM, Wim de Lange wrote:

The GROUP BY should also be (lower(leaf_user_name)). And I would use COUNT(*) but that makes no difference as far as I know.
Did you try this query? I would expect errors because of the fact that the group by does not match the first select expression.

I run this query without error message in Toad. Thanks for help.

4

Hi Sven,

Is this SQL correct? I wish to print out all duplicate records (case
insensitive). I have zero output and not sure if no duplicates or
wrong SQL statement.
It is correct.

SELECT lower(leaf_user_name), COUNT
(lower(leaf_user_name)) AS numoccurrences
FROM rss_user
WHERE rss_name = 'sunone_west'
Are you really only checking this one name though?

GROUP BY leaf_user_name
HAVING COUNT (lower(leaf_user_name)) > 1;

Is there a Toad way to do the same?
Scheme Browser, Tables.
Select the table(s) - possibly one by one.
Right click.
Select Data Duplicates.
Work through the screens that appear.

Cheers,
Norm. [TeamT]

Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else.

We have checked this email and its attachments for viruses. But you should still check any attachment before opening it.
We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes.

If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk