Trouble with Local View

[xBaseDude]

Established local Database. Added Table to database. Trying to add a Local View of data on one field only. Using "View Wizard". When I add a "sort on" I get zero data in the view. SQL query seems correct. I'd prefer to write my own if possible, but am unclear on how to add the view to the dbc without the wizard.

Ideally the SQL Statement would be...

SELECT cColor ;
FROM mydbf ;
WHERE !EMPTY(cColor);
ORDER BY cColor ;
GROUP BY cColor ;
INTO VIEW vColor

Any ideas as to why I would be getting zero results in the view, data does exist, and it appears to work when I don't include the ORDER BY clause.

TIA - Wayne

 

[ramani]

Sample code to create a view manualy..

OPEN DATABASE myDBC
SET DATABASE TO myDBC

CREATE SQL VIEW vColor AS ;
SELECT cColor ;
FROM mydbf ;
WHERE !EMPTY(cColor);
ORDER BY cColor ;
GROUP BY cColor

DBSetProp("vColor","View","SendUpdates",.F.)
DBSetProp("vColor","View","BatchUpdateCount",1)
DBSetProp("vColor","View","CompareMemo",.T.)
DBSetProp("vColor","View","FetchAsNeeded",.F.)
DBSetProp("vColor","View","FetchMemo",.F.)
DBSetProp("vColor","View","FetchSize",100)
DBSetProp("vColor","View","MaxRecords",-1)
DBSetProp("vColor","View","Prepared",.F.)
DBSetProp("vColor","View","UpdateType",1)
DBSetProp("vColor","View","UseMemoSize",255)
DBSetProp("vColor","View","Tables","myDBC!myDBF")
DBSetProp("vColor","View","WhereType",1)

DBSetProp("vColor.cColor","Field","Caption","Color Code")
DBSetProp("vColor.cColor","Field","DataType","C(12)")
DBSetProp("vColor.cColor","Field","UpdateName","myDBC!myDBF.cColor")
DBSetProp("vColor.cColor","Field","KeyField",.F.)
DBSetProp("vColor.cColor","Field","Updatable",.F.)
...
...
etc ..



YOu are using a Group by class.. but you are not doing anything as to SUM(someFiled) etc.. and so dont know what you do the group for.

SO you can get the results..

SELECT DISTINCT cColor ;
FROM mydbf ;
WHERE !EMPTY(cColor);
ORDER BY cColor



____________________________________________
ramani - (Subramanian.G)

http://winnersoft.coolfreepages.com/

 

[xBaseDude]

Thank-You Ramani!

I just couldn't get it through the wizard. Your solution worked fine. Funny thing about the GROUP BY...always worked in a regular select, used the distinct command instead, and it worked like a charm!

God Bless you and your family!

Regards - Wayne