>>>I am counting transactions posted from outer locations to the main office.
>>>
>>>Each store is suposed to pass in 6 files. I need to find the stores that sent NONE or any # less than 6
>>>
>>>This code does fine:
>>>SELECT ulog.storeno, ulog.postno, ulog.upldate, ;
>>>cnt(ulog.uplproc) as posted , store.phone ;
>>> FROM ulogserv ulog JOIN storelst Store ;
>>> ON ulog.storeno = store.store ;
>>> WHERE upldate between lddate1 and lddate2 ;
>>> group by 1,2,3
>>>
>>>I thought that "having posted <>6 " would do fine but it does not?
>>
>>You are using an INNER JOIN so if a store doesn't post anything, it won't show up in the result set and you won't know they didn't post. Try:
>>SELECT ulog.storeno, ulog.postno, ulog.upldate, ;
>>cnt(ulog.uplproc) as posted , store.phone ;
>> FROM storelst Store LEFTF OUTER JOIN ulogserv ulog ;
>> ON ulog.storeno = store.store ;
>> WHERE upldate between lddate1 and lddate2 ;
<b> or isnull(lddate1) ;</b>
>> group by 1,2,3
>
>Nope. that still misses the no transaction groups?
>
>__Stephen
The WHERE clause is throwing it off because the LEFT OUTER JOIN will produce a null value in the field. I think the
bold statement will take care of it.
Larry Miller
MCSD
LWMiller3@verizon.netAccumulate learning by study, understand what you learn by questioning. -- Mingjiao