6.5 Beta Common Table Expressions Use - SELECT only?

SQL++
1

Trying out Common Table Expressions feature in CB 6.5 Beta. Are only SELECT statements allowed as the consumer of CTEs? This works… (yay!)

WITH transform AS (
WITH extract AS (
SELECT y keyTransactions
FROM CURL(“http://api.newrelic.com/v2/key_transactions.json",{"header”: “X-Api-Key: b1adeeb1adeeb1a”}) x
UNNEST x.key_transactions as y
)
SELECT e.keyTransactions.id id,
e.keyTransactions.application_summary appSummary
FROM extract e
)
SELECT t.id, t.appSummary FROM transform t

But if the target of the CTEs are an INSERT, error (boo!). Complains about INSERT:

WITH transform AS (
WITH extract AS (
SELECT y keyTransactions
FROM CURL(“http://api.newrelic.com/v2/key_transactions.json",{"header”: “X-Api-Key:b1adeeb1adeeb1a”}) x
UNNEST x.key_transactions as y
)
SELECT e.keyTransactions.id id,
e.keyTransactions.application_summary appSummary
FROM extract e
)
INSERT INTO test (i,a) SELECT t.id i, t.appSummary a FROM transform t
“code”: 3000,
“msg”: “syntax error - at INSERT”,…

Only replaced the ultimate SELECT with an INSERT…SELECT

Is it planned for release? One liner ETL would be cool. (It can be accomplished without the CTEs, but use of CTEs makes it much more readable.
-MK
P.S. is there a (more) proper forum or tag for 6.5 Beta questions?

2

CTE is FOR SELECT only.
Try this

INSERT INTO test (KEY id, VALUE appSummary)
WITH extract AS ( SELECT y keyTransactions
                  FROM CURL("http://api.newrelic.com/v2/key_transactions.json",{"header": "X-Api-Key:b1adeeb1adeeb1a"}) x
                  UNNEST x.key_transactions as y
                )
SELECT t.id, t.appSummary FROM extract AS t;
3

Thanks for the hint. The full N1QL is below! We also missing KEY and VALUE keywords. Doh! BUT Lookeee! ETL in one (albeit long-ish) N1QL statement! (Soon to be the topic of a blog entry).
-MK

INSERT INTO test (KEY i, VALUE a)
WITH transform AS (
WITH extract AS (
SELECT y keyTransactions
FROM CURL(“http://api.newrelic.com/v2/key_transactions.json",{"header”: “X-Api-Key:b1adeeb1adeebla”}) x
UNNEST x.key_transactions as y
)
SELECT to_string(e.keyTransactions.id) id,
e.keyTransactions.application_summary appSummary
FROM extract e
)
SELECT t.id i, t.appSummary a FROM transform as t

1 Like