Well here's my latest info...
I've spoken with Barbara Paltiel, and she sugguested I check the code page of the 2 tables and add some fields to my select to collect more info, so I've tried this...
The Code Pages are the same, 1252, as indicated (far) below.
Here's my latest effort to collect info....
SELECT v12Clients.*, ;
LEN(ALLTRIM(vProfiles.Client_no)) as LEN1,;
LEN(ALLTRIM(v12Clients.Client_no)) as LEN2,;
UPPER(ALLTRIM(vProfiles.Client_no)) == UPPER(ALLTRIM(v12Clients.Client_no)) as MyCond1, ;
UPPER(rtrim(lTRIM(vProfiles.Client_no))) == UPPER(rtrim(lTRIM(v12Clients.Client_no))) as MyCond2, ;
vProfiles.Profile_no, ;
vProfiles.Date_Enter, ;
vProfiles.Profauthnm ;
FROM v12Clients, vProfiles ;
INTO TABLE SYS(2023)+"\NeedMini.DBF" ;
ORDER BY v12Clients.Client_no, vProfiles.Date_Enter DESCENDING, vProfiles.Profile_no ;
WHERE UPPER(lTRIM(vProfiles.Client_no)) == UPPER(lTRIM(v12Clients.Client_no))
I get the right records returned....when the Where Clause is
WHERE UPPER(lTRIM(vProfiles.Client_no)) == UPPER(lTRIM(v12Clients.Client_no))
or
WHERE UPPER(vProfiles.Client_no) == UPPER(v12Clients.Client_no)
I'd go with that but I can't trust the data which is why I want to use the ALLTRIM
I appreciate the ideas but at the moment it's still fustrating...
Rick
------------------------------------------------
>Rick,
>
>Try
>
>Where Upper((vProfiles.Client_no) = Upper(v12Clients.Client_no)
>
>You don't need ALLTRIM and '==' because client_no has the same size in both cursors.
>
>BTW what Foxpro version you are running?
>
>>Sergey,
>>I've tried several variations on this theme with nothing out of the expected...
>>It seems only my original Select is goofed...but ???
>>
>>I still can't figure out why the
>>"Where Upper(Alltrim(vProfiles.Client_no))== Upper(Alltrim(v12Clients.Client_no))"
>>
>>returns matches on only 2 (instead of the expected 6) client_no
>>It matches
>>'11-00CVG '
>>and
>>'11-9 '
>>
>>but not the rest
>>
>>How else can I merge these tables?
>>
>>Rick
>>
>>------------------------------------------------------
>>>Rick,
>>>Try to run the same two selects but with extra space at the end of client_no
>>>
>>>SELECT * FROM FROM v12Clients WHERE Client_no = "111-00-1296 "
>>>and
>>>SELECT * FROM FROM vProfiles WHERE Client_no = "111-00-1296 "
>>>
>>>
>>>
>>>>Sergey,
>>>>I just tried your suggestion and it worked fine...I'm still stuck!
>>>>
>>>>Rick
>>>>
>>>>-----------------------------------------------
>>>>
>>>>>Richard,
>>>>>I don't see any problems with your select. I think that problem is with your data.
>>>>>Try to run two separate selects
>>>>>
>>>>>SELECT * FROM FROM v12Clients WHERE Client_no = "111-00-1296"
>>>>>and
>>>>>SELECT * FROM FROM vProfiles WHERE Client_no = "111-00-1296"
>>>>>
>>>>>
>>>>>
>>>>>>
>>>>>>Question...
>>>>>>With these tables (Fox2.6 cursors) and these data, why doesn't this select return any records for Client_no 111-00-1296
>>>>>>
>>>>>> SELECT v12Clients.*, ;
>>>>>> vProfiles.Profile_no, ;
>>>>>> vProfiles.Date_Enter, ;
>>>>>> vProfiles.Profauthnm ;
>>>>>> FROM v12Clients, vProfiles ;
>>>>>> INTO TABLE SYS(2023)+"\NeedMini.DBF" ;
>>>>>> ORDER BY v12Clients.Client_no, vProfiles.Date_Enter DESCENDING, vProfiles.Profile_no ;
>>>>>> WHERE UPPER(ALLTRIM(vProfiles.Client_no)) == UPPER(ALLTRIM(v12Clients.Client_no))
>>>>>>
>>>>>>
>>>>>>
>>>>>>Structure for table: c:\temp\00861482.tmp
>>>>>>Number of data records: 392
>>>>>>Date of last update: 06/29/1900
>>>>>>Code Page: 1252
>>>>>> Field Field Name Type Width Dec Index Collate
>>>>>> 1 CLIENT_NO Character 15
>>>>>> 2 PROFILE_NO Character 11
>>>>>> 3 DATE_ENTER Date 8
>>>>>> 4 PROFAUTHNM Character 30
>>>>>>** Total ** 65
>>>>>>
>>>>>>Structure for table: c:\temp\00861477.tmp
>>>>>>Number of data records: 6
>>>>>>Date of last update: 06/29/1900
>>>>>>Memo file block size: 64
>>>>>>Code Page: 1252
>>>>>> Field Field Name Type Width Dec Index Collate
>>>>>> 1 CLSEQNUM Character 16
>>>>>> 2 CLNUMBER Character 15
>>>>>> 3 CLCMPNY Character 40
>>>>>> 73 CLIENT_NO Character 15
>>>>>> 74 USAGE Numeric 10
>>>>>>** Total ** 871
>>>>>>
>>>>>>
>>>>>>V12Clients.Client_no
>>>>>>'11-00CVG ' (1 entry, Record Number 1)
>>>>>>'11-17-159-1 ' (1 entry, Record Number 2)
>>>>>>'11-9 ' (1 entry, Record Number 3)
>>>>>>'111-00-1285 ' (1 entry, Record Number 4)
>>>>>>'111-00-1296 ' (1 entry, Record Number 5)
>>>>>>'111-00-1302 ' (1 entry, Record Number 6)
>>>>>>
>>>>>>
>>>>>>vProfiles.Client_no
>>>>>>'11-00CVG ' (377 entries, Record Numbers 1-377)
>>>>>>'11-17-159-1 ' (3 entries, Record Numbers 378-380)
>>>>>>'11-9 ' (3 entries, Record Numbers 381-383)
>>>>>>'111-00-1285 ' (2 entries, Record Numbers 384-385)
>>>>>>'111-00-1296 ' (3 entries, Record Numbers 386-388)
>>>>>>'111-00-1302 ' (4 entries, Record Numbers 389-392)
>>>>>>
>>>>>>
