On 2016/05/23 3:02 PM, Balaji Ramanathan wrote:
> 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.
Firstly, you are not spoilt, you are deprived! MSSQL supports VIEWs very
much like most other RDBMS systems. A views is essentially a table but
without persistent data, it gets its data from a query. This means that
on the front-end, it behaves very much like any other table and you
cannot query, filter or sort by columns that are not part of the table.
This is true for all RDBMS systems, SQLite, MSSQL and the like.
What SQLite doesn't have is stored queries, nor does it have stored
procedures, but it does support Triggers and Common table expressions.
To achieve what you would like to achieve, the answer is probably TEMP
tables formed by your complicated queries, but which contain a lot more
columns than you mean to display. You can then select and display only
the needed columns after filtering the TEMP table.
You can achieve this more "live" with using Common Table Expressions,
where you can setup a base complicated query with all needed columns
from the base tables into the CTE, and then re-use that CTE everywhere
with only the final select from it showing whatever you really need.
WITH CTE1(c1, c2, c3 ... cn) AS (
SELECT x,y,z... [very complicatedquery here]
), CTE2 (d1, d2, d3 .... dn) AS (
SELECT x,y,z.... [Another very complicated query here]
)
SELECT c1, d2, [very simple query here]
FROM CTE1, CTE2
WHERE c3 > 10
ORDER BY d3
etc.
But, if you are new to CTE's (MSSQL supports them too), then perhaps a
bit of reading is needed first - we could suggest sources if needed.
There is also nothing wrong with making a view that contain all of the
above c1, c2, through d1, d2... dn and then simply selecting from it the
c1, d2 you want to see and ordering by the other d3, c3 etc. columns.
The advantage views have is that you never need to even see the
complicated bits again. Why you would insist to NOT put any column into
a view is beyond me, you only need to select the ones you want, unless
of course you are really spoilt and want to just do SELECT * FROM myView
WHERE stuff_that_isnt_in_my_view = true, but I'm sure that isn't the
case. ;)
HTH and good luck!
Ryan
