Re: [sqlite] View workarounds

[R Smith]

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

Re: [sqlite] View workarounds

[Simon Slavin]

On 25 May 2016, at 4:42pm, Balaji Ramanathan  
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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] View workarounds

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

Re: [sqlite] 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',

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

[sqlite] 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] View workarounds

[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',

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) - 

[sqlite] View workarounds

[Dominique Devienne]

On Mon, May 23, 2016 at 4:49 PM, Steve Schow  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] View workarounds

[R Smith]

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

[sqlite] View workarounds

[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] View workarounds

[Steve Schow]

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  
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] 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