View workarounds

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
13 messages Options
Reply | Threaded
Open this post in threaded view
|

View workarounds

Balaji Ramanathan
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: View workarounds

Jean-Luc Hainaut

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
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users 

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: View workarounds

R Smith
In reply to this post by Balaji Ramanathan


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

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: View workarounds

Steve Schow
In reply to this post by Balaji Ramanathan
My suggestion is add the extra columns you need to the view, then when you make a query against that view, only specify the more limited set of output columns you want in the final output

As others have said already, don’t think of a view as a stored query.  Think of it as multiple joined tables into a “virtual” table which you can then do more simple queries against then you would have to do in some monster join.  it basically will let you hide all the complicated stuff you say you have now into the view, then use very simple select statements on that view to produce your final report…which only shows the columns you want.



On May 23, 2016, at 7:02 AM, Balaji Ramanathan <[hidden email]> 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.
>
>

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: View workarounds

Dominique Devienne
On Mon, May 23, 2016 at 4:49 PM, Steve Schow <[hidden email]> wrote:

> My suggestion is add the extra columns you need to the view, then when you
> make a query against that view, only specify the more limited set of output
> columns you want in the final output
>

SQLite almost supports what's needed, but only for VIRTUAL tables. [1]

The HIDDEN trick doesn't work in tables (parses fine, but ignored), and
doesn't work in views (doesn't parse, at least in 3.10.2 when I tried).

If that trick was extended to views, as in

   create view v (col1, col2, col3 HIDDEN) as select ...

then Balaji could have `select * from v` only return col1 and col2, yet
still be able to filter/sort on col3. i.e. the cake and ... --DD

PS: I actually thought HIDDEN was supported for tables, but sadly it isn't
apparently.
PPS: Oracle12c uses INVISIBLE for such columns. See [2].

[1] https://www.sqlite.org/vtab.html#hiddencol
[2]
http://www.oracle.com/technetwork/articles/database/invisible-columns-odb12c-2331522.html
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: View workarounds

Balaji Ramanathan
In reply to this post by Balaji Ramanathan
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).

Balaji Ramanathan
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: View workarounds

R Smith


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.


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: View workarounds

Balaji Ramanathan
In reply to this post by Balaji Ramanathan
Thank you for continuing with this thread, Ryan.  I don't have nuclear
launch codes in my database, but it is over 4MB in size.  But the data in
it is not that important.  Let me post the view I am interested in:

select Trip.TripID as 'Trip Number',

Mode.Mode as 'Mode',

TripOD.TripOD as 'Origin&Destination',

TripDescription.TripDescription as 'Description',

Trip.Distance as 'Distance (KM)',

TripTimings.TripDates as 'Trip Dates',

TripTimings.TripTimings as 'Trip Timings',

TripTimings.TripScheduledDates as 'Scheduled Dates',

TripTimings.TripScheduledTimings as 'Scheduled Timings',

TripTimes.DepartureDelay as 'Departure Delay',

TripTimes.ArrivalDelay as 'Arrival Delay',

TripTimes.TripTime as 'Trip Time (HH:MM)',

TripCost.TotalCostUSD as 'Cost (USD)',

TripCost.OutOfPocketCostUSD as 'Out of Pocket Cost (USD)',

TripCalculatedValues.Speed as 'Speed in KMPH',

TripCalculatedValues.CentsPerKM as 'Cost (c/KM)',

TripCalculatedValues.OutOfPocketCentsPerKM as 'Out of Pocket Cost (c/KM)',

TripCalculatedValues.DollarPerHour as 'Cost ($/Hour)',

TripCalculatedValues.OutOfPocketDollarPerHour as 'Out of Pocket Cost
($/Hour)',

AllTripNotes.AllTripNotes as 'Trip Notes',

P1.PlaceAlternates as 'Origin Alternates',

P2.PlaceAlternates as 'Destination Alternates',

P3.PlaceDetails as 'Origin Details',

P4.PlaceDetails as 'Destination Details',

P5.AllPlaceNotes as 'Origin Notes',

P6.AllPlaceNotes as 'Destination Notes',

AllTripGroups.AllTripGroups as 'Trip Groups',

AllTripGroupTripNotes.AllTripGroupTripNotes as 'Trip Group Notes',

AllVehicleNotes.AllVehicleNotes as 'Vehicle Notes',

AllModeNotes.AllModeNotes as 'Mode Notes'

from Trip

inner join Mode on Trip.ModeNumber = Mode.ModeID

inner join TripCost on Trip.TripID = TripCost.TripID

inner join TripDescription on Trip.TripID = TripDescription.TripID

inner join TripOD on Trip.TripID = TripOD.TripID

inner join TripTimes on Trip.TripID = TripTimes.TripID

inner join TripTimings on Trip.TripID = TripTimings.TripID

inner join TripCalculatedValues on Trip.TripID = TripCalculatedValues.TripID

inner join AllTripNotes on Trip.TripID = AllTripNotes.TripID

inner join AllTripGroups on Trip.TripID = AllTripGroups.TripID

inner join AllTripGroupTripNotes on Trip.TripID =
AllTripGroupTripNotes.TripID

inner join AllVehicleNotes on Trip.VehicleNumber = AllVehicleNotes.VehicleID

inner join AllModeNotes on Trip.ModeNumber = AllModeNotes.ModeID

inner join PlaceAlternateNames P1 on Trip.Origin = P1.PlaceID

inner join PlaceAlternateNames P2 on Trip.Destination = P2.PlaceID

inner join PlaceDetails P3 on Trip.Origin = P3.PlaceID

inner join PlaceDetails P4 on Trip.Destination = P4.PlaceID

inner join AllPlaceNotes P5 on Trip.Origin = P5.PlaceID

inner join AllPlaceNotes P6 on Trip.Destination = P6.PlaceID

As you can see it is a join of very many tables and views.  And I have
given the columns nice names to make it easier to read and understand what
they are rather than guessing from cryptic camelCase names.

Now, this is the text of the TripTimes view that is one of the sources of
the view above:

select TripID,

(cast(strftime('%s',EndDateTime) - strftime('%s',StartDateTime)
+(StartGMTOffset - EndGMTOffset)*3600 as integer)/3600)

|| ':' ||

substr('00'|| (cast(strftime('%s',EndDateTime) -
strftime('%s',StartDateTime) +(StartGMTOffset - EndGMTOffset)*3600 as
integer)%3600/60), -2,2)

as TripTime,

cast(strftime('%s',EndDateTime) - strftime('%s',StartDateTime) as
float)/3600.0 +(StartGMTOffset - EndGMTOffset) as TripTimeRaw,

case when (strftime('%s',StartDateTime) -
strftime('%s',ScheduledStartDateTime) +(ScheduledStartGMTOffset -
StartGMTOffset)*3600) >= 0 then

(cast(strftime('%s',StartDateTime) - strftime('%s',ScheduledStartDateTime)
+(ScheduledStartGMTOffset - StartGMTOffset)*3600 as integer)/3600)

|| ':' ||

substr('00'|| (cast(strftime('%s',StartDateTime) -
strftime('%s',ScheduledStartDateTime) +(ScheduledStartGMTOffset -
StartGMTOffset)*3600 as integer)%3600/60), -2,2) else

'-'||(cast(strftime('%s',ScheduledStartDateTime) -
strftime('%s',StartDateTime) +(StartGMTOffset -
ScheduledStartGMTOffset)*3600 as integer)/3600)

|| ':' ||

substr('00'|| (cast(strftime('%s',ScheduledStartDateTime) -
strftime('%s',StartDateTime) +(StartGMTOffset -
ScheduledStartGMTOffset)*3600 as integer)%3600/60), -2,2) end

as DepartureDelay,

cast(strftime('%s',StartDateTime) - strftime('%s',ScheduledStartDateTime)
as float)/3600.0 +(ScheduledStartGMTOffset - StartGMTOffset) as
DepartureDelayRaw,

case when (strftime('%s',EndDateTime) - strftime('%s',ScheduledEndDateTime)
+(ScheduledEndGMTOffset - EndGMTOffset)*3600) >= 0 then

(cast(strftime('%s',EndDateTime) - strftime('%s',ScheduledEndDateTime)
+(ScheduledEndGMTOffset - EndGMTOffset)*3600 as integer)/3600)

|| ':' ||

substr('00'|| (cast(strftime('%s',EndDateTime) -
strftime('%s',ScheduledEndDateTime) +(ScheduledEndGMTOffset -
EndGMTOffset)*3600 as integer)%3600/60), -2,2) else

'-'||(cast(strftime('%s',ScheduledEndDateTime) - strftime('%s',EndDateTime)
+(EndGMTOffset - ScheduledEndGMTOffset)*3600 as integer)/3600)

|| ':' ||

substr('00'|| (cast(strftime('%s',ScheduledEndDateTime) -
strftime('%s',EndDateTime) +(EndGMTOffset - ScheduledEndGMTOffset)*3600 as
integer)%3600/60), -2,2) end

as ArrivalDelay,

cast(strftime('%s',EndDateTime) - strftime('%s',ScheduledEndDateTime) as
float)/3600.0 +(ScheduledEndGMTOffset - EndGMTOffset) as ArrivalDelayRaw

from Trip

As you can see, I calculate times in HH:MM format for display and I also
calculate the raw number of hours as a floating point number.  I want to be
able to filter my original view based on the value of things like
TripTimeRaw rather than TripTime (which is a string in HH:MM format).  The
problem is that I don't want to expand my display grid any further (it is
already way too wide) by including TripTimeRaw as one of the selected
columns of my main view.  But I do want to be able to filter that view
based on this value.

Now, I can copy the text of the view definition and paste it in as a plain
old query, add any filter conditions I want based on the "raw" columns in
my sub-views, and it works perfectly fine.  I can also add ORDER BY clauses
to this query based on these "raw" columns, and that also works fine.  I
just wish there was an easier way to do this using just the view rather
than copying and pasting massive amounts of text between windows.

Balaji Ramanathan
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: View workarounds

R Smith


On 2016/05/24 2:17 AM, Balaji Ramanathan wrote:

> Thank you for continuing with this thread, Ryan.  I don't have nuclear
> launch codes in my database, but it is over 4MB in size.  But the data in
> it is not that important.  Let me post the view I am interested in:
>
> select Trip.TripID as 'Trip Number',
>
> Mode.Mode as 'Mode',
>
> TripOD.TripOD as 'Origin&Destination',
>
> TripDescription.TripDescription as 'Description',
>
> Trip.Distance as 'Distance (KM)',
>
> TripTimings.TripDates as 'Trip Dates',
>
> TripTimings.TripTimings as 'Trip Timings',
>
> TripTimings.TripScheduledDates as 'Scheduled Dates',
>
> TripTimings.TripScheduledTimings as 'Scheduled Timings',
>
> TripTimes.DepartureDelay as 'Departure Delay',
>
> TripTimes.ArrivalDelay as 'Arrival Delay',
>
> TripTimes.TripTime as 'Trip Time (HH:MM)',
>
> TripCost.TotalCostUSD as 'Cost (USD)',
>
> TripCost.OutOfPocketCostUSD as 'Out of Pocket Cost (USD)',
>
> TripCalculatedValues.Speed as 'Speed in KMPH',
>
> TripCalculatedValues.CentsPerKM as 'Cost (c/KM)',
>
> TripCalculatedValues.OutOfPocketCentsPerKM as 'Out of Pocket Cost (c/KM)',
>
> TripCalculatedValues.DollarPerHour as 'Cost ($/Hour)',
>
> TripCalculatedValues.OutOfPocketDollarPerHour as 'Out of Pocket Cost
> ($/Hour)',
>
> AllTripNotes.AllTripNotes as 'Trip Notes',
>
> P1.PlaceAlternates as 'Origin Alternates',
>
> P2.PlaceAlternates as 'Destination Alternates',
>
> P3.PlaceDetails as 'Origin Details',
>
> P4.PlaceDetails as 'Destination Details',
>
> P5.AllPlaceNotes as 'Origin Notes',
>
> P6.AllPlaceNotes as 'Destination Notes',
>
> AllTripGroups.AllTripGroups as 'Trip Groups',
>
> AllTripGroupTripNotes.AllTripGroupTripNotes as 'Trip Group Notes',
>
> AllVehicleNotes.AllVehicleNotes as 'Vehicle Notes',
>
> AllModeNotes.AllModeNotes as 'Mode Notes'
>
> from Trip
>
> inner join Mode on Trip.ModeNumber = Mode.ModeID
>
> inner join TripCost on Trip.TripID = TripCost.TripID
>
> inner join TripDescription on Trip.TripID = TripDescription.TripID
>
> inner join TripOD on Trip.TripID = TripOD.TripID
>
> inner join TripTimes on Trip.TripID = TripTimes.TripID
>
> inner join TripTimings on Trip.TripID = TripTimings.TripID
>
> inner join TripCalculatedValues on Trip.TripID = TripCalculatedValues.TripID
>
> inner join AllTripNotes on Trip.TripID = AllTripNotes.TripID
>
> inner join AllTripGroups on Trip.TripID = AllTripGroups.TripID
>
> inner join AllTripGroupTripNotes on Trip.TripID =
> AllTripGroupTripNotes.TripID
>
> inner join AllVehicleNotes on Trip.VehicleNumber = AllVehicleNotes.VehicleID
>
> inner join AllModeNotes on Trip.ModeNumber = AllModeNotes.ModeID
>
> inner join PlaceAlternateNames P1 on Trip.Origin = P1.PlaceID
>
> inner join PlaceAlternateNames P2 on Trip.Destination = P2.PlaceID
>
> inner join PlaceDetails P3 on Trip.Origin = P3.PlaceID
>
> inner join PlaceDetails P4 on Trip.Destination = P4.PlaceID
>
> inner join AllPlaceNotes P5 on Trip.Origin = P5.PlaceID
>
> inner join AllPlaceNotes P6 on Trip.Destination = P6.PlaceID
>
> As you can see it is a join of very many tables and views.  And I have
> given the columns nice names to make it easier to read and understand what
> they are rather than guessing from cryptic camelCase names.
>
> Now, this is the text of the TripTimes view that is one of the sources of
> the view above:
>
> select TripID,
>
> (cast(strftime('%s',EndDateTime) - strftime('%s',StartDateTime)
> +(StartGMTOffset - EndGMTOffset)*3600 as integer)/3600)
>
> || ':' ||
>
> substr('00'|| (cast(strftime('%s',EndDateTime) -
> strftime('%s',StartDateTime) +(StartGMTOffset - EndGMTOffset)*3600 as
> integer)%3600/60), -2,2)
>
> as TripTime,
>
> cast(strftime('%s',EndDateTime) - strftime('%s',StartDateTime) as
> float)/3600.0 +(StartGMTOffset - EndGMTOffset) as TripTimeRaw,
>
> case when (strftime('%s',StartDateTime) -
> strftime('%s',ScheduledStartDateTime) +(ScheduledStartGMTOffset -
> StartGMTOffset)*3600) >= 0 then
>
> (cast(strftime('%s',StartDateTime) - strftime('%s',ScheduledStartDateTime)
> +(ScheduledStartGMTOffset - StartGMTOffset)*3600 as integer)/3600)
>
> || ':' ||
>
> substr('00'|| (cast(strftime('%s',StartDateTime) -
> strftime('%s',ScheduledStartDateTime) +(ScheduledStartGMTOffset -
> StartGMTOffset)*3600 as integer)%3600/60), -2,2) else
>
> '-'||(cast(strftime('%s',ScheduledStartDateTime) -
> strftime('%s',StartDateTime) +(StartGMTOffset -
> ScheduledStartGMTOffset)*3600 as integer)/3600)
>
> || ':' ||
>
> substr('00'|| (cast(strftime('%s',ScheduledStartDateTime) -
> strftime('%s',StartDateTime) +(StartGMTOffset -
> ScheduledStartGMTOffset)*3600 as integer)%3600/60), -2,2) end
>
> as DepartureDelay,
>
> cast(strftime('%s',StartDateTime) - strftime('%s',ScheduledStartDateTime)
> as float)/3600.0 +(ScheduledStartGMTOffset - StartGMTOffset) as
> DepartureDelayRaw,
>
> case when (strftime('%s',EndDateTime) - strftime('%s',ScheduledEndDateTime)
> +(ScheduledEndGMTOffset - EndGMTOffset)*3600) >= 0 then
>
> (cast(strftime('%s',EndDateTime) - strftime('%s',ScheduledEndDateTime)
> +(ScheduledEndGMTOffset - EndGMTOffset)*3600 as integer)/3600)
>
> || ':' ||
>
> substr('00'|| (cast(strftime('%s',EndDateTime) -
> strftime('%s',ScheduledEndDateTime) +(ScheduledEndGMTOffset -
> EndGMTOffset)*3600 as integer)%3600/60), -2,2) else
>
> '-'||(cast(strftime('%s',ScheduledEndDateTime) - strftime('%s',EndDateTime)
> +(EndGMTOffset - ScheduledEndGMTOffset)*3600 as integer)/3600)
>
> || ':' ||
>
> substr('00'|| (cast(strftime('%s',ScheduledEndDateTime) -
> strftime('%s',EndDateTime) +(EndGMTOffset - ScheduledEndGMTOffset)*3600 as
> integer)%3600/60), -2,2) end
>
> as ArrivalDelay,
>
> cast(strftime('%s',EndDateTime) - strftime('%s',ScheduledEndDateTime) as
> float)/3600.0 +(ScheduledEndGMTOffset - EndGMTOffset) as ArrivalDelayRaw
>
> from Trip
>
> As you can see, I calculate times in HH:MM format for display and I also
> calculate the raw number of hours as a floating point number.  I want to be
> able to filter my original view based on the value of things like
> TripTimeRaw rather than TripTime (which is a string in HH:MM format).  The
> problem is that I don't want to expand my display grid any further (it is
> already way too wide) by including TripTimeRaw as one of the selected
> columns of my main view.  But I do want to be able to filter that view
> based on this value.
>
> Now, I can copy the text of the view definition and paste it in as a plain
> old query, add any filter conditions I want based on the "raw" columns in
> my sub-views, and it works perfectly fine.  I can also add ORDER BY clauses
> to this query based on these "raw" columns, and that also works fine.  I
> just wish there was an easier way to do this using just the view rather
> than copying and pasting massive amounts of text between windows.

Ok, that is at least making it clear now. There are some ways to achieve
this, but by far the least effort is to simply make views with more
columns, and then SELECT from those views by stating which columns you
want to select.

So you create the view with all the magic SQL. (Also avoid column names
in the view with spaces and other weirdness, it's possible, but not fun).

SELECT Mode, "Origin&Destination", Description, ... [All fields you DO
want to see]
   FROM my_View
  WHERE ArrivalDelayRaw > 10
  ORDER BY [Any other field which might not be in the SELECT list]

I would also use in the View real simple Field names, you can always
expand them in any query for readability.

SELECT Mode AS 'Amazing! darn mode ^__^', OOPCost  AS 'Out of Pocket
Cost (c/KM)'
   FROM my_View
  WHERE ArrivalDelayRaw > 10

As long as the view does the heavy-lifting in calculating all those
fields, any Query from the view can/should get specific with shown data,
ordering and filtering. This makes views much more powerful and reusable.

Best of luck!
Ryan

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: View workarounds

Balaji Ramanathan
In reply to this post by Balaji Ramanathan
Thanks again, Ryan.  The options right now come down to either expanding
the view with all the raw columns so that I can filter and sort directly
using a select * from view.  Or I can use the view for unfiltered, unsorted
look at my data, and use the query of the view to do filtering and
sorting.  Decisions, decisions . . .

I vote for more extensive support of hidden columns in tables, views, etc.
Is there some site for submitting enhancement requests for SQLite?

Balaji Ramanathan
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: View workarounds

Simon Slavin-3

On 25 May 2016, at 4:42pm, Balaji Ramanathan <[hidden email]> wrote:

> I vote for more extensive support of hidden columns in tables, views, etc.
> Is there some site for submitting enhancement requests for SQLite?

It's here.  You just did it.  Don't hold your breath.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: View workarounds

R Smith
In reply to this post by Balaji Ramanathan


On 2016/05/25 5:42 PM, Balaji Ramanathan wrote:
> Thanks again, Ryan.  The options right now come down to either expanding
> the view with all the raw columns so that I can filter and sort directly
> using a select * from view.  Or I can use the view for unfiltered, unsorted
> look at my data, and use the query of the view to do filtering and
> sorting.  Decisions, decisions . . .
>
> I vote for more extensive support of hidden columns in tables, views, etc.
> Is there some site for submitting enhancement requests for SQLite?

Indeed, this right here is the very place to submit such requests. You
can count it as submitted.

As for hidden columns in Views, I think that request is in already from
before. There are a few considerations when pondering new additions that
the Devs have to balance, they are mainly (but not confined to, and in
no particular order):
Development time,
Request Urgency/Frequency,
Backwards Compatibility,
Code-Size Increase,
and the SQL Standard.

I think your request will fly through the Development time check, it
should not be a great time expense. Problems come in with request
frequency, it's really not a frequently asked thing, nor a very urgent
one, quite a nice-to-have really. It should not break backward
compatibility and while SQLite needs to remain "Lite", I think the code
size will not be greatly affected.
I am unsure what the SQL standard says on Views with hidden columns.

I could be wrong on any of the above points, these are just from my POV
trying to explain what can be expected from the request. The devs will
consider based on the above and then decide to do it and/or to do it
soonest or put in the long queue.

Some days Richard has a sudden bit of inspiration or likes an idea, and
miraculously you will see a new commit on trunk that includes the
functionality the very next day - but mostly it will come in due course.  :)

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: View workarounds

Balaji Ramanathan
This post has NOT been accepted by the mailing list yet.
In reply to this post by Simon Slavin-3
Excellent!  That was easy.  Hopefully the changes required in the code are just that easy and we get a new version of SQLite which supports the feature fully.  But I will continue breathing normally in the meantime.