On 2016/05/23 7:52 PM, Balaji Ramanathan wrote: > Thank you very much for all your comments. > > I thought about including all the columns in my view and then selecting > just what I need, but that is almost as painful as repeating the view's > query in adding the filters I want. Modifying both the select clause and > the WHERE clause of the query is twice the work. You see, I use the view > to quickly review the contents of multiple tables and whether they make > sense, and adding these extraneous, unformatted columns just makes the work > harder, that is why I did not just throw every column into the view. > > I am familiar with CTE's, but I am not sure how they would help in this > situation. I guess I could throw everything into my view and use that as a > CTE in a select, but all I have is that one complicated view, so creating a > CTE out of it seems like wasted effort. > > I am intrigued by Dominique's suggestion of virtual tables and their hidden > column feature. Enabling hidden columns in views would be the best of both > worlds - allow me to display exactly what I want while allowing me to > filter and sort on other columns. Why don't normal tables and views have > hidden columns? That would be an excellent enhancement to SQLite, I think. > > In the meantime, copying the view's definition as a query and adding the > filtering and sorting clauses to the query gets me there. It is a little > bit of work, but keeps my formatting so that I can scan each row quickly > and verify the data (which is the primary aim of my view).
You know you could add many views right? You could have one view with all columns, and another with only those columns that interest you for different purposes. You could even make views that use other views that already exist as a source. The way you describe "almost as painful as..." etc. sounds to me like there is something small missing, perhaps something you have yet to grok, because nobody usually finds the thing you describe painful, so I'm sure there is some little thing which, when realised, will make all this extremely much easier for you. I just wish I knew what it was. Perhaps you could be specific with your schemata and show us what you have, and what you want to see (and how) and which filtering / ordering you typically would want to do - we could then give you a quick script of how to achieve that and hopefully in there somewhere a light will go on. Even maybe post the database file somewhere, if it isn't full of nuclear launch codes or such. Btw: Access is a user-friendly data tool more than an actual database. You are now using a full-fledged RDBMS which is way more powerful and conversely, very much less user-friendly (or as I like to put it: More accurate and less short-cutty), so it would probably seem a bit more painful at first. It's a lot like going from driving a nice car with parking-assist to piloting a jet fighter - it's not as easy of course, but once you get the hang of it, it's spectacular.
