>I have a situation in which I need to join 3 tables with an sql statement. I am trying to create a view and although I got it to work from the command line by splitting it into two statements I would like to generate a view that can be populated at runtime.
>
>The object is to display a grid with a description and a check box indicating that the record exists in the data file.
>
>The first table is a contact table with a contact id.
>
>The second table is a reference table with type, code and code description fields.
>
>I want an outer join to create a table with all contacts and all roles.
>
>I then want to add a logical field to indicate that the record (contact id and code description) appear in a data file. (BTW, I broke down and added a logical field here (INFILE) that is always true when the record is written.
>
>I can get it to work if I:
>
>create sql view tmpfile as sele people.contid, genref.codedesc from people, genref where genref.codetype = 'ROL'
>
>then;
>
>create cursor view descvue as sele (iif(rolefile.infile) = .t., .t., .f.)) as infile, tmpfile.contid, tmpfile.codedesc from rolefile, tmpfile where rolefile.contid = tmpfile.contid and rolefile.codedesc = tmpfile.codedesc
>
>BUT, when I try to store this to the database I get an error that says that the file TMPFILE doesn't exist and prompts me for a table.
>
>Any ideas?

To get this view into a DB, you have to CREATE SQL VIEW MyView AS SELECT ...

The following should work:

Create SQL View MyView AS ;
Select people.contid, genref.codedesc, .t. as "INFILE" from ;
People Left Outer Join GenRef ON people.FK = genref.PK ...

What I would do is use the view designer to generate the initial code for you so you can select the type of join you want between the People and GenRef tables. Copy the generated code into a PRG then add the CREATE SQL VIEW part.