I should further add that there is really no reason I see to have permanent views in a database any longer. Full disclosure: our application does have permanent views, mostly because I haven't had the time to go back through the hundreds of files involved and find all of the instances where they are used and add in the code to make the view temporary. There is one permanent view in our database that incorporates a variable, and it has over the years been an occasional source of difficulty. What we do today is maintain a separate set of files to create the structure, create keys, indexes, autonumbers, views and SATTACHed tables. We'll unload the data, run the structure file, load the data, then set about recreating the rest of the components. The file that builds the views types the variable just before creating the view.
Still, I wish I had the time to purge all those permanent views ... Emmitt Dove Manager, Converting Applications Development Evergreen Packaging, Inc. [email protected] (203) 214-5683 m (203) 643-8022 o (203) 643-8086 f [email protected] -----Original Message----- From: [email protected] [mailto:[email protected]] On Behalf Of Tom Frederick Sent: Sunday, April 18, 2010 21:37 To: RBASE-L Mailing List Subject: [RBASE-L] - Variables in views for reports We use views for most reports and it works great. Some of these get very big so I started using variables in the WHERE statement like this : 'WHERE T1.MedCurrentYN = 'Y' AND T1.ZID = (.vZID)' simply to cut down on volume sent over the LAN particularly for some slower computers. This WHERE pulls current medications for one particular person. Works like a charm. Usually get 2-15 rows compared to 300+ without the .vZID, 95% of which is not needed. In database maintenance routines, PACK and RELOADs work without a hitch. When rebuilding the database after an unload, the error message 'unrecognized global variable in expression (2161)' pops up and the view never reloads. If I eliminate .vZID, the rebuild runs just fine with the changed view in place. Put .vZID back in and the error returns. I could use this variable approach in 20-30 views but I really need them to rebuild after an unload. Retyping holds no appeal. As we move to more wireless operations, I will have more staff using laptops away from our offices to get notes and reports done immediately using a satellite connection. These are Medicaid services and you do not get paid for writing reports back in the office. You create them while you are with the person. In our office, I can live with 300 view rows of something because the LAN is pretty quick. In an apartment with a satellite connection, 300 rows of data slows the database to a crawl. I realize this can be done using TEMP tables, but I really like the benefits of using views. I am also trying very hard to stay consistent in how the database works because I am not the only one doing the programming. Based on the error message, is there some way to declare the variable during the unload/rebuild process so view based variables simply work like they do in PACK and RELOAD? Tom Frederick Jacksonville, IL
