>I am getting a "GROUP BY clause is missing or invalid" when I run the query above. Im not sure if I should be using DISTINCT or GROUP BY.
>I want to show unique rows for csono
>
>
>SELECT sosOrd.csono,dcreate,cItemno,cwarehouse ;
>FROM sosord INNER JOIN sostrs ON sosord.cSoNO=sostrs.cSoNo WHERE dcreate>1997/01/01} GROUP BY sosord.csono INTO CURSOR tempcurs
>
>Carmel

You've a typo but even after it's corrected neither distinct nor group by would work (I mean results wouldn't be reliable and right).
Why :
You want unique values for csono, right ?
select Distinct sosOrd.csono,dcreate,cItemno,cwarehouse ...

Would return rows like this as separate :

csono dcreate
1     2/2/1997
1     1/1/1998

select sosOrd.csono,dcreate,cItemno,cwarehouse ... group by csono

Assuming VFP7 and earlier or enginebehavior set to 70 in VFP8 above records would return a single csono but dcreate value wold be the one physically entered last.


If sosOrd.csono,dcreate,cItemno,cwarehouse are already unique per csono (like cust_id,company, contact in customer) then include all of them in group by :

SELECT  sosOrd.csono,dcreate,cItemno,cwarehouse ;
  FROM sosord ;
  INNER JOIN sostrs ON sosord.cSoNO=sostrs.cSoNo ;
  WHERE dcreate> {^1997/01/01} ;
  GROUP BY sosord.csono,dcreate,cItemno,cwarehouse ;
  INTO CURSOR tempcurs

Identical to :

SELECT DISTINCT sosOrd.csono,dcreate,cItemno,cwarehouse ;
  FROM sosord ;
  INNER JOIN sostrs ON sosord.cSoNO=sostrs.cSoNo ;
  WHERE dcreate> {^1997/01/01} ;
  INTO CURSOR tempcurs

Faster with a subquery (even if still needs a distinct clause depending on your data) :

SELECT sosOrd.csono,dcreate,cItemno,cwarehouse ;
  FROM sosord ;
  WHERE dcreate > {^1997/01/01} and ;
        csoNo in (select cSoNo from sostrs) ;
  INTO CURSOR tempcurs

Cetin