General information
Category:
Databases,Tables, Views, Indexing and SQL syntax
The view designer is pretty limited with complex views. If I remember correctly, there is a utility in the file section that will help you building complex views
HTH.
>I've a question that puzzled me,
>
>Let's assume 3 tables as such:
>
>1-Emps:
> EMP_ID NAME GROUP CAT
>-------------------------------------------------
> 1 emp 1 g1 c2
> 2 emp 2 g2 c4
> 3 emp3 c3
> 4 emp 4 g7 c1
>
>2-Groups:
> GROUP_ID GROUP_NAME
>-----------------------
> g1 group 1
> g2 group 2
>
>3-Cats
> CAT_ID CAT_DIS
>-----------------------------------
> c1 cat 1
> c2 cat 2
> c4 cat 4
>
>To display each employ data (group & category) we can use the following SELECT:
>
>SELECT emps.emp_id, emps.group, groups.group_name, emps.cat, cats.cat_dis;
> FROM emps;
> LEFT OUTER JOIN Group ON Emps.group = Group.group_id;
> LEFT OUTER JOIN Cats ON Emps.cat = Cats.cat_id
>& the result is:
>
> EMP_ID GROUP GROUP_NAME CAT CAT_DIS
>-----------------------------------------------
> 1 g1 group 1 c2 cat 2
> 2 g2 group 2 c4 cat 4
> 3 .NULL. c3 .NULL.
> 4 g7 .NULL. c1 cat 1
>
>Everything is OK so far!
>
>Now we'll make a view from this:
>
>CLOSE ALL DATA
>OPEN DATA testdata EXCLUSIVE
>CREATE SQL VIEW AS testview;
>SELECT emps.emp_id, emps.group, groups.group_name, emps.cat, cats.cat_dis;
> FROM emps;
> LEFT OUTER JOIN Group ON Emps.group = Group.group_id;
> LEFT OUTER JOIN Cats ON Emps.cat = Cats.cat_id
>
>now try the view:
>
>CLOSE ALL DATA && just in case :-)
>OPEN DATA testdata
>USE testview
>BROWSE
>
> EMP_ID GROUP GROUP_NAME CAT CAT_DIS
>-----------------------------------------------
> 1 g1 group 1 c2 cat 2
> 2 g2 group 2 c4 cat 4
> 3 .NULL. c3 .NULL.
> 4 g7 .NULL. c1 cat 1
>same data ... no problem.
>
>Now try to open the view in the view designer and:
>- table CATS is not there while the JOIN exist!
>- try to RUN the view works fine.
>- try to save the view and you'll get the error:
> "SQL: Column 'CAT' is not found." and can't save the view!!!!
>
>What is wrong? Is it a VFP6.0 bug or what?
Previous
Reply
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only