SQL statement in exe vs dev. env.

[Diederik]

In a table a supposedly unique field contains double values.
To determine/get rid of these double entries, I use the following SELECT - SQL statement:

SELECT a.id, a.code as doublecode ;
   FROM basetable a WHERE NOT DELETED() AND ;
     EXISTS (SELECT b.id ;
               FROM basetable b ;
               WHERE b.code = a.code AND b.id != a.id) ;
               ORDER BY doublecode INTO CURSOR double
IF _TALLY > 0
 ...

When I run this in my development environment, it works flawlessly (VFP 6.0, SP4).
However, in the compiled application (either an .app or an .exe), it suddenly doesn't work (the cursor contains just 1 record, and a wrong one at that).

I'm missing something here, but what? Diederik Vermeeren
verm1864@exact.nl

 

[ramani]

SELECT a.id, a.code as doublecode ;
FROM basetable a WHERE NOT DELETED() AND ;
EXISTS (SELECT DISTINCT b.id ;
FROM basetable b ;
WHERE b.code = a.code AND b.id != .id ;
.AND. !DELETED() ) ;
ORDER BY doublecode INTO CURSOR double
ramani
(Subramanian.G)
FoxAcc
ramani_g@yahoo.com

 

[ramani]

SORRY THAT WONT WORK.. IN THE MIDST OF MY CONSTRUCTION, I AM INTO SOMETHING ELSE AND THE POST BUTTON GOT PRESSED. I SHALL GET BACK TO YOU SOON.. SORRY
ramani
(Subramanian.G)
FoxAcc
ramani_g@yahoo.com

 

[foxdev]

How about:

[tt]select ID, count(*) ;
from basetable ;
group by ID having count(*) > 1 [/tt] Robert Bradley

http://www.lowcostprints.com

http://www.foxdev.com/

http://www.agrainofhope.org

 

[ramani]

I am back ... What FoxDev suggested shall work.. little more suggestion ...

select ID, count(*) ;
from basetable WHERE ! DELETED() ;
group by ID having count(*) > 1

*** SINCE IT APPEARS YOU HAVE DELETED RECORDS AS WELL ramani
(Subramanian.G)
FoxAcc
ramani_g@yahoo.com

 

[Diederik]

:~/ Whoops...
Robert, Ramani,

I got carried away with my SQL-statement and missed the actual problem...
By using Roberts far more simple SELECT (adapted for the real thing), which gave me exactly the same problem, I suddenly realised that the problem was NOT the SELECT itself, but had to do with the double entries and string-comparisions.

Short explanation:
ID 1, CODE "abc"
ID x, CODE "ABC"
In a dev. environment, "abc" = "ABC" results in .T.
In a compiled version (either exe or app), "abc" = "ABC" results in .F.

Thanks for getting me on the right track! Diederik Vermeeren
verm1864@exact.nl