How can I put a where condition on a SqLite view statement?

Question

Is it possible to rewrite the following view so that the user had the possibility to parameterize the view using WHERE clause (WHERE overlap > @param, which is commented out below)? Is it possible at all? The where clause should affect before the group by clause.

I try to select from a table vl and to match the rows (outer join) against rows from a statement, which counts in another table (vlda).

CREATE VIEW dr AS
SELECT
    vl.unique_object_id AS unique_object_id,
    ifnull(vlda.detectioncount, 0) AS detectioncount,
    vl.count AS labelcount,
    (1.0*ifnull(vlda.detectioncount, 0) / vl.count) AS detectionrate,
    vlda.evalround_id
    FROM viewlabelcount vl
    LEFT OUTER JOIN 
    (
        SELECT 
            count() as detectioncount,
            unique_object_id, 
            evalround_id
            FROM viewlabeldetections_best
            -- WHERE overlap > 0.5 -- is it possible to parametrize?
            GROUP BY unique_object_id
    ) AS vlda
    ON
        vl.unique_object_id = vlda.unique_object_id

score 3 · Accepted Answer · answered Apr 15 '14 at 16:34

3

SQLite does not allow parameters in objects stored in the schema (such as views or triggers).

If you're using SQLite 3.8.3 or later, you could move that definition as a common table expression into your queries.

answered Apr 15 '14 at 16:34

CL.

173,858
17
217
259

Thx. Meanwhile I found also another option to 'emulate' the complex statements: to put the statement as text into a special table, then read the statement from the table and execute it from the application, including necessary statement parameters: http://stackoverflow.com/questions/3402841/does-sqlite-support-store-procedures . – dousin Apr 16 '14 at 12:16

How can I put a where condition on a SqLite view statement?

1 Answers1

Linked