mercredi 20 mai 2015

Using SQLite WITH clause for ordinary (non-recursive) CTE

It seems like all the documentation for SQLite's relatively new WITH clause focuses on recursive common table expressions (CTE). However, if I understand correctly, it appears the ordinary CTE would be very useful for factoring a complicated query repeating the same sub-query over and over into a more simple form. However, when I try to do this I get an error.

Here's an example. I'm trying to insert some new records into table tab3, using:

INSERT INTO tab3
(rid,name,value,b1,b2)
VALUES
    ((SELECT 1+IFNULL(MAX(rid),0) FROM tab3),'name','blux',2,3),

    ((SELECT 1+IFNULL(MAX(rid),0) FROM tab3),'start',4,2,3),

    ((SELECT 1+IFNULL(MAX(rid),0) FROM tab3),'end',6,2,3);

I would like to get of that obnoxious SELECT that appears three times. Reading the syntax diagram for the WITH statement it appears that I could do this as a simple first experiment:

WITH <???> AS (SELECT 1+IFNULL(MAX(rid),0) FROM tab3) SELECT <???>

In this case, I just want to test out the WITH and make sure it returns the correct value.

But I don't understand what is supposed to go in the <???> part.

My initial naive guess (without looking at the definition for cte-table-name) was to try this:

WITH rid1 AS (SELECT 1+IFNULL(MAX(rid),0) FROM tab3) SELECT rid1

This makes perfect sense to me, it says that (SELECT 1+IFNULL(MAX(rid),0) FROM tab3) is assigned to the field rid during the execution of the subsequent SELECT, which in this case just returns it.

But the syntax diagram says it is table-name "(" column-name [, ... ] ")". But how do I determine what I should put for these?

I have tried several variations, looking at the recursive examples for some inspiration, such as:

WITH rid1(rid) AS (SELECT 1+IFNULL(MAX(rid),0) FROM tab3) SELECT rid1

Here I imagine that maybe I am naming the new CTE "rid1" using "rid" from the original table, but of course that doesn't work ("No such column 'rid1'"). So I thought maybe I had to refer to the field of the CTE not the table name itself, so I tried:

WITH rid1(rid) AS (SELECT 1+IFNULL(MAX(rid),0) FROM tab3) SELECT rid

But this doesn't help, now I get "No such column 'rid'".

What am I doing wrong here? Am I misunderstanding what an ordinary CTE can be used for, or have I just not stumbled upon the right usage of the cte-table-name part of the clause?

Aucun commentaire:

Enregistrer un commentaire