CREATE SQL VIEW

[RGoldthorpe]

Hello

I am creating reports on my datbase but because uses want sooooo many reports I am creating views and forms so that the user selects the information they want the report based on I.E. rep name selected and the haulier check box is ticked would produce a report for all that particular reps hauliers. If just haulier is ticked then it would produce a big list of all the hauliers .Any way this works fine when they are created from just one table but when I want to combine three tables I am having difficulty it creates the view fine but then comes up witha syntax error when you try to close the view after modifing it even though you make no changes Also it only shows two of the tables in the view although all the joins are there under the join tab.

The code is as follows

What am I doing wrong or can this not be done??

OPEN DATABASE "data\data1.dbc"exclusive
CREATE SQL VIEW seasonalview as SELECT *;
FROM organisations LEFT OUTER JOIN contact_Details_table ON org_scontact = ctd_sorg ;
LEFT OUTER JOIN field_plot_seasonal ON ctd_sorg = fps_sorg ;
WHERE ((ALLTRIM(organisations.org_scontact) == ?organisation) OR (EMPTY(?organisation)=.T.));
AND ((organisations.org_srep == ?rep OR (EMPTY(?rep)=.T.)));
AND ((Contact_details_Table.ctd_saddress4 == ?regions OR (EMPTY(?regions)=.T.)));
AND ((field_plot_seasonal.fps_svariety == ?variety OR (EMPTY(?variety)=.T.)));
AND ((field_plot_seasonal.fps_season == ?season OR (EMPTY(?season)=.T.)))

Thanks in advance

Rach

 

[DEDMOD]

Are you actually creating this view in code or did you let the view designer create it? A lot of people here have pointed out that the view designer can't handle complicated joins and that they need to create a simple one and then copy the code the view designer gives and modify it to add additional tables. I admit I haven't actually put this to the test, but it could be the source of your problems.

But I also note that you're not using the table names or an alias when you do the joins so if there are the same fields in more than one of the tables, there will be confusion. This could be why your third table doesn't show, for instance.

Dave Dardinger

 

[RGoldthorpe]

I am writing code and then running the code to create my veiw also there are no two field names the same throughtout the whole database but have put table names in before them my third table still doesn't show up but it has stopped the error as I close it.

Having said that table does not show up it is in the missing tables fields in the list of fields if you put that view in the dataenviroment does that mean it has worked???

Thanks for your help Dave

Luv

Rach

 

[DEDMOD]

Well, in my opinion outer joins are weird beasts and while I think I can visualize an outer join between two tables, I'm not at all sure what happens when you have two outer joins in the same select.

If I understand what you're saying correctly, it does indeed sound like you have your problem under control for the moment. Unfortunately, if there's something you're not sure how is working it has the nasty tendency to leap up and bite you in the butt when you turn your back on it. So I wouldn't get too cocky yet.

Dave Dardinger

 

[RGoldthorpe]

Cockey me!!!!!

No don't worry I won't have been bitten by fox pro too many times.

The reports seems to be working OK so I have sent it to my testing man to torture it and see if it does what it is supposed to.

Thanks for all your help

Rach