**

Hi folks ~ I'm trying to run a SQL query to find duplicates in an integer field. For sake of argument, let's call the field 'fldInteger' and the form, 'frmForm'. Since I don't know much SQL, I'm having problems. Can someone help? Thank you in advance!

Select 'fldInteger'
From 'frmForm'
Where 'fldInteger' = 'fldInteger';


O/S: Win2K
Database: Oracle 8.1.6.3
User/Admin: Completely customized ARS 4.05.02
ARServer 4.05.02 (patch 1077)

Laurie Muhlethaler

G

Senior Remedy Administrator

(415) 878-5060

This posting was submitted via the Web interface

** Try:


Select DISTINCT 'fldInteger'
From 'frmForm'

and then add whatever WHERE clause would be appropriate.





On 12/19/05, Muhlethaler, Laurie - GPM < Laurie.Muhlethaler@greenpoint.com > wrote:

**

Hi folks ~ I'm trying to run a SQL query to find duplicates in an integer field. For sake of argument, let's call the field 'fldInteger' and the form, 'frmForm'. Since I don't know much SQL, I'm having problems. Can someone help? Thank you in advance!

Select 'fldInteger'
From 'frmForm'
Where 'fldInteger' = 'fldInteger';


O/S: Win2K
Database: Oracle 8.1.6.3
User/Admin: Completely customized ARS 4.05.02
ARServer 4.05.02 (patch 1077)

Laurie Muhlethaler

G

Senior Remedy Administrator

(415) 878-5060

This posting was submitted via the Web interface




--
Frank Caruso
Specific Integration, Inc.
Senior Remedy Engineer
www.specificintegration.com
703-376-1249
AIM:FMCAR10 This posting was submitted via the Web interface

**
Something like:

Select 'fldInteger', count('fldInteger')
From 'frmForm'
Where count('fldInteger') > 1;

will work better.

Fluxman

-----Original Message-----
From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG] On Behalf Of Muhlethaler, Laurie - GPM
Sent: 12/19/2005 8:20 PM
To: arslist@ARSLIST.ORG
Subject: SQL Help


**

Hi folks ~ I'm trying to run a SQL query to find duplicates in an integer field. For sake of argument, let's call the field 'fldInteger' and the form, 'frmForm'. Since I don't know much SQL, I'm having problems. Can someone help? Thank you in advance!

Select 'fldInteger'
From 'frmForm'
Where 'fldInteger' = 'fldInteger';


O/S: Win2K
Database: Oracle 8.1.6.3
User/Admin: Completely customized ARS 4.05.02
ARServer 4.05.02 (patch 1077)

Laurie Muhlethaler

G

Senior Remedy Administrator

(415) 878-5060

This posting was submitted via the Web interface
This posting was submitted via the Web interface

**
Thank you for responding, Frank. Wouldn't that statement give me non-duplicates?



From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG] On Behalf Of Frank Caruso
Sent: Monday, December 19, 2005 5:31 PM
To: arslist@ARSLIST.ORG
Subject: Re: SQL Help


** Try:


Select DISTINCT 'fldInteger'
From 'frmForm'

and then add whatever WHERE clause would be appropriate.





On 12/19/05, Muhlethaler, Laurie - GPM < Laurie.Muhlethaler@greenpoint.com > wrote:

**

Hi folks ~ I'm trying to run a SQL query to find duplicates in an integer field. For sake of argument, let's call the field 'fldInteger' and the form, 'frmForm'. Since I don't know much SQL, I'm having problems. Can someone help? Thank you in advance!

Select 'fldInteger'
From 'frmForm'
Where 'fldInteger' = 'fldInteger';


O/S: Win2K
Database: Oracle 8.1.6.3
User/Admin: Completely customized ARS 4.05.02
ARServer 4.05.02 (patch 1077)

Laurie Muhlethaler

G

Senior Remedy Administrator

(415) 878-5060

This posting was submitted via the Web interface




--
Frank Caruso
Specific Integration, Inc.
Senior Remedy Engineer
www.specificintegration.com
703-376-1249
AIM:FMCAR10 This posting was submitted via the Web interface
This posting was submitted via the Web interface

select count(entryid), fldInteger
from frmForm
group by fldInteger
having count(entryid) > 1
order by count(entryid) desc;

Gives you value and the count for the value.

Axton

On 12/19/05, Muhlethaler, Laurie - GPM
wrote:
> **
> Thank you for responding, Frank. Wouldn't that statement give me
> non-duplicates?
>
> From: Action Request System discussion list(ARSList)
> [mailto:arslist@ARSLIST.ORG] On Behalf Of Frank Caruso
> Sent: Monday, December 19, 2005 5:31 PM
> To: arslist@ARSLIST.ORG
> Subject: Re: SQL Help
>
> ** Try:
>
>
> Select DISTINCT 'fldInteger'
> From 'frmForm'
>
> and then add whatever WHERE clause would be appropriate.
>
>
>
> On 12/19/05, Muhlethaler, Laurie - GPM <
> Laurie.Muhlethaler@greenpoint.com> wrote:
> > **
> >
> > Hi folks ~ I'm trying to run a SQL query to find duplicates in an integer
> field. For sake of argument, let's call the field 'fldInteger' and the
> form, 'frmForm'. Since I don't know much SQL, I'm having problems. Can
> someone help? Thank you in advance!
> >
> > Select 'fldInteger'
> > From 'frmForm'
> > Where 'fldInteger' = 'fldInteger';
> >
> >
> > O/S: Win2K
> > Database: Oracle 8.1.6.3
> > User/Admin: Completely customized ARS 4.05.02
> > ARServer 4.05.02 (patch 1077)
> >
> >
> > Laurie Muhlethaler
> >
> > G
> >
> > Senior Remedy Administrator
> >
> > (415) 878-5060This posting
> was submitted via the Web interface
>
>
>
> --
> Frank Caruso
> Specific Integration, Inc.
> Senior Remedy Engineer
> www.specificintegration.com
> 703-376-1249
> AIM:FMCAR10 This posting was
> submitted via the Web interface
> This posting was submitted
> via the Web interface


UNSUBSCRIBE or access ARSlist Archives at http://www.ARSLIST.org
(Support: mailto:support@arslist.org)

Hi Laurie,

My apologies if this has already been suggested, I receive the ARSList digest (that's my excuse for being behind the rest of the ARS world & I'm sticking to it) with yesterday's posts.

I believe you want to know which rows in your frmForm table have duplicate values, not just that duplicates exist or for which integer values. Therefore you want to link the table to itself (an "a" copy and a "b" copy) on the fldInteger column, which will return all duplicates but could also link each row to itself (avoided via where clause).

This could be done something like this:

select requestid, fldInteger from frmForm a, frmForm b where a.fldInteger = b.fldInteger and a.requestid != b.requestid

HTH,
Doug Anderson
Mayo Clinic
Rochester, MN

Original message:
Date: Mon, 19 Dec 2005 17:19:43 -0800
From: "Muhlethaler, Laurie - GPM"
Subject: SQL Help

This is a multi-part message in MIME format.

------=NextPart00101C60503.74FB0C62
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

Hi folks ~ I'm trying to run a SQL query to find duplicates in an
integer field. For sake of argument, let's call the field 'fldInteger'
and the form, 'frmForm'. Since I don't know much SQL, I'm having
problems. Can someone help? Thank you in advance!

Select 'fldInteger'
From 'frmForm'
Where 'fldInteger' =3D 'fldInteger';


O/S: Win2K
Database: Oracle 8.1.6.3
User/Admin: Completely customized ARS 4.05.02
ARServer 4.05.02 (patch 1077)

Laurie Muhlethaler
G
Senior Remedy Administrator
(415) 878-5060


UNSUBSCRIBE or access ARSlist Archives at http://www.ARSLIST.org
(Support: mailto:support@arslist.org)