Hi!

I had a projct to convert to SQl Server once with views that query data from both table and view joined, as well as a bunch of queries from the database and a local cursor. It was a lot of work...

>Hi Vlad
>
>Thanks for such an great reply.
>
>It was a real eye opener to find out I should avoid combining table and views to create another view if I am going to upsize to SQL Server.
>This app may be upsized to SQL Server and I thought I was helping this by doing it the way I have:(
>
>I will go and try what you suggest now:)
>
>
>
>>Hi, Mark!
>>
>>Its sad to see such a trouble, because things you're talking about are quite weird. Will try to explain certaing things and tell me if this helped...
>>
>>First of all, I'm also wondered how another view (V_PrevWinItem) can get uncommitted data from the first view (V_Tickitm). From what I know, SELECT-SQL statement used by views cannot query data from buffers. However, for views it might be different, though I just made a simple test in VFP6SP5 and it works as described (no buffered changes appear in the child view). It is possible your view queried only records that are not changed, and no new records buffered in V_Tickitm appeared in the result set of V_PrevWinItem.
>>
>>Following are just a suggestions.
>>Creating view cause crash in case database is corrupted. Try to use VALIDATE command for it and pack it (Cleanup Database command from menu) - after database backup, of course. If you have backup of the working database files (DCT, DCX and DBC), try to copy older files in place of newer.
>>
>>Also, is version of VFP run-time DLLS changed on any client machine?
>>
>>Finally, it is not needed to join 2 result sets in a single view when you update only one of these result sets. You can query each data (from Winitm and from V_tickitm) into 2 separate views, index second view and use SET RELATION command for the first view. then each time record pointer moved in first view, record pointer is moved in the second view accordingly. This way you will be able also to update both record sources, and you will get rid of complex view biult using table+View.
>>
>>BTW, making combination of the table from database and view in another view is bad practice, in particular, when application is moved to another (remote) backend (such as SQL Server), such views become a great problem (because cannot create remote view that use data from server and from local view simultaneously).
>>
>>HTH.
>>
>>>An updateable view in a live application of mine has stopped working and now I cant figure out how it ever worked in the first place:(
>>>
>>>The view (V_PrevWinItem) selects from a table Winitm (winning Items) and another view V_Tickitm
>>>
>>>It is supposed to select out all the items that are in Winitm and also in V_tickitm and put them in V_PrevWinItem
>>>
>>>It uses the view V_PrevWinItem to do some work on these items then possibly adds a few more items and hopefully writes the lot back to Winitm with a Tablupdate().
>>>
>>>My understanding now is that this process should never have worked in the first place because I used a view (V_tickitm) containing uncommitted items in the select. (but it did work)
>>>
>>>Now however the V_PrevWinItem is always empty, which I am told is what I should have expected in the first place.
>>>
>>>To try and make it work I commited the view V_tickitm but it made no difference.
>>>
>>>I would probably be able to fix the routine by copying out the items in V_Tickitm to a temporary table and creating the View based on that table and V_winitm, but the table would not be in the DBC so I could not use it in a view.
>>>
>>>And anyway I am getting Fatal Errors whenever I try to create a View programattically!
>>>
>>>Sigh:(
>>>
>>>Oh well its Friday night and I am fed up. I am going home.
>>>
>>>Night All