Hi, I have created some views in my database by joining multiple tables to pull out specific columns from these tables without having to remember the exact SQL and joins (easy repeatability). But it looks like I have misunderstood how views work and have run into some limitations when using these views. I was wondering if any of you have any workarounds for these limitations.
1. I can't filter the view on any column that is not explicitly part of the SELECT clause of the view. These are columns that are part of the tables included in the view, but they are not in the SELECT statement, so I am not able say: SELECT * from myView where [column that is not part of the select] = 'myValue'. I am able to copy the SQL of the view and add that WHERE condition to its end, and it filters perfectly fine, but I can't use the view directly, I have to use the SQL of the view 2. Similar, probably related: I can't order the view by any column that is not part of the SELECT clause of the view. Again, this is a column in a table included in the view, but the view itself does not include it in the SELECT, and so I can't sort by it. Is there something similar to a view in SQLite that I should be using instead to get around these? I don't want to keep using the query because it is long and complicated and I am afraid I will introduce errors into it when I try to modify it to add sorting and filtering. And I don't want to include these columns in my view because my view already includes some calculations based on these columns (for example, a cost field is output as a string with a leading $ sign, so I don't want to include the raw numerical column in the select, but I want to be able to filter and sort by that raw numerical value). I have a lot of experience with SQL, and have worked with MS Access extensively, so I am used to saving queries in the database and using them as needed. MS Access does not have views, and saved queries are MS Access' alternative to views. But they behave more like queries than SQLite views: they give me access to all the columns in the tables involved, not just those in the SELECT clause. Maybe I am just spoilt! Thank you in advance for your thoughts on this. Balaji Ramanathan
