I came across an issue where I can not JOIN databases with some tables being in an attached tables in the SELECT part when creating a View. I keep getting errors about not being able to reference objects in the attached table. I understand this error but believe it is too pessimistic. I would expect SQLite to create the View and when it is executed (opened) if the proper tables were not attached at execution time then it would "throw an exception" at that moment.
Attached tables are useful to create "daily transactional or activity" types of files, we don't want a system to have a single monolithic database file that keeps growing so you have "daily" database files to capture the daily activity of a system - I use the timestamp of the day as the filename. At "system reset time" you go onto the next day, etc. Here is a condensed example: In "System.db3" I have the following Table: SessionStatusTable - SessionStatusKey (Primary Key) - SessionStatus (Text Field) UserTable - UserNumber (PrimaryKey) - UserName (Text Field) MachineTable - MachineNumber (PrimaryKey) - MachineName (Text Field) SessionEndReasonTable - SessionEndReasonKey (PrimaryKey) - SessionEndReason (Text Field) There are configuration types of tables. This is the first table connected to, thus its database name is "Main" as part of the SQLite standard. In a daily transactional or activity file "YYYY-MM-DD.db3" I have the following Table: SessionTable - SessionNumber (Primary Key) - SessionStatusKey (Foreign Key) - UserNumber (Foreign Key) - MachineNumber (Foreign Key) - ApplicationName - BeginTime - LastRequestTime - EndTime - SessionEndReasonKey (Foreign Key) It would be nice to create a View in this Table where the SELECT part of the View can JOIN with the configuration tables in Main so you can view the SessionView with the SessionStatus, UserName, MachineName, and SessionEndReason text fields instead of their primary keys. This would mean you can use any of the SQLite editors, execute the SessionView and it gives a much clearer "view" (pardon the pun) of session information. NOTE: I can create a SELECT query programmatically at runtime that has JOINS with attached database tables and that works great, but it would be nice to allow a View to use the exact same SELECT query but with the advantage of having the view stored with the database file for easy execution by end users or support people. I am right, or am I wrong? Opinions? - Scott McDonald ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
