>>Hi everybody,
>>
>>It's always simpler to answer somebody's else questions... :)
>>
>>Ok, there is a problem:
>>I have a table with this structure
>>
>>ccode C2 town C4 UseGrp C3 cYear c4 Period C2 other numeric values
>>
>>
>>I do:
>>
>>select ccode, town, UseGrp, cYear, ;
>> VolSales, MedSale, NumSales ;
>> from InputTable ;
>> where town='ZZZZ' and ;
>> Period='YR' and between(cYear,lcStartYear,lcEndYear) ;
>> into cursor curTemp
>>?_Tally
>>_Tally returns 255 records
>>
>>Now I want to join to Towns table to add county C 15 for this table.
>>I do :
>>
>> select distinct curTemp.ccode, nvl(State,lcState) as State, ;
>> nvl(upper(County),lcCounty) as County, ;
>> nvl(fullname,lcCity) as City, ;
>> curTemp.town, UseGrp, cYear, ;
>> VolSales, MedSale, NumSales ;
>> from curTemp left join Towns ;
>> on curTemp.ccode=Towns.ccode ;
>> order by 1,5,6,7 ;
>> into cursor curWork
>>
>>Now my result returns 5755 records.
>>
>>I just want the same 255 records with one additional field included.
>>Note, BTW, that the InputTable contains ZZ as ccode for state level. Towns table doesn't contain ZZ, so what's why I tried left join.
>>
>>Could you please help me with this SIMPLE problem? I spent already about 1h on it :(
>
>I sure sounds like you've got a bunch of records that match in the towns table and are not duplicates.
>How unique is the ccode field in each table? With only 2 characters, it probably isn't unique enough to distinguish a single record.
Hi Trey,
Both tables have repeated ccode.
IOW, curTemp:
ccode town UseGrp cYear
01 ZZZZ 1FA 1998
01 ZZZZ ALL 1998
01 ZZZZ CND 1998
01 ZZZZ 1FA 1998
01 ZZZZ ALL 1999
01 ZZZZ CND 1999
3 record per each county per each UseGrp
Towns table just has all Towns, e.g.
ccode town
01 AVON
01 BROO
etc.
I can do, what I want, in a scan loop (since I process this table anyway), but it takes time to add County to each record. What's why I thought, it would be easy to do it in SQL.
If it's not broken, fix it until it is.
My Blog