Tom, Why not use a TEMPORARY VIEW? It is just as effective, and you can assign a value to the variable just before creating the view.
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
