As long as you don't try to modify data, a view just behaves like a base table. So, like in base tables, you can't extract, filter, sort, group by, etc. based on non-existing columns.
SQLite views are read-only, but modifying data through a view can be done with "instead of" triggers. J-L Hainaut >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 >_______________________________________________ >sqlite-users mailing list >sqlite-users at mailinglists.sqlite.org >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
