Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: sqlpro
  Where is sqlpro?
 
 New Zealand
 sqlpro
 To: Eric den Doop
  Where is Eric den Doop?
 Houten
 Netherlands
 Eric den Doop
 Tags
Subject: RE: this must be interesting query
Thread ID: 50887 Message ID: 51619 # Views: 1 # Ratings: 0
Version: Visual FoxPro 8 Category: Databases, Tables and SQL Server
Date: Wednesday, September 15, 2004 4:16:08 AM         
   


I added few more records to the cursor like
INSERT INTO test VALUES ("Raj")
INSERT INTO test VALUES ("Raj")

when i run ur query its reporting this record(i.e. "Raj") but it should not.
bcoz 2 records are in same case.
any ideas Eric

> > Hi Friends
> >
> > This must be good one.I have duplicate data like
> >
> > 123x
> > 123X
> > 1234
> > 1234
> >
> > how can i write query which returns records that r same but different case.in the abv example i need 123x
> >
> > I tried
> > select fld1 from mytable group by fld1 having count(fld1)>1
> > but this returns even 1234 record also.
> > any ideas please.
> > Thanks
> > cheers
>
> not fully tested, but I think this should get you in the right direction.
>
> CREATE CURSOR test (cfield C(4))
> INSERT INTO test VALUES ("123x")
> INSERT INTO test VALUES ("123X")
> INSERT INTO test VALUES ("1234")
> INSERT INTO test VALUES ("1234")
> INSERT INTO test VALUES ("1234")
> INSERT INTO test VALUES ("12A4")
> INSERT INTO test VALUES ("12a4")
> INSERT INTO test VALUES ("12a4")
> INSERT INTO test VALUES ("1bA3")
> INSERT INTO test VALUES ("1Ba3")
> INSERT INTO test VALUES ("1BA3")
> SELECT UPPER(cfield) AS u_cfield, count(*) as ncount ;
> FROM test ;
> WHERE NOT LEN(TRANSFORM(VAL(cfield))) = LEN(cfield) ;
> GROUP BY u_cfield ;
> HAVING ncount > 1
> 

> --
> Eric den Doop
> www.foxite.com - The Home Of The Visual FoxPro Experts


cheers

ENTIRE THREAD

this must be interesting query Posted by Rajani @ 9/7/2004 12:46:45 AM
RE: this must be interesting query Posted by Eric den Doop @ 9/7/2004 8:51:49 AM
cheers Posted by Rajani @ 9/15/2004 4:03:44 AM
RE: this must be interesting query Posted by Rajani @ 9/15/2004 4:16:08 AM
RE: this must be interesting query Posted by Joey Bancud @ 9/9/2004 7:43:58 AM
RE: this must be interesting query Posted by Rajani @ 9/15/2004 4:07:24 AM