Couchbase Query Name Alias

Question

Actually I am working with Couchbase N1Ql Query for retrieving data from db which has calculation in it

a sample similar model of my query is below:

SELECT 2+1 AS Greeting from something where Greeting>1;

The Error I'm getting is :

Ambiguous reference to field Greeting.

But if I modify This like this:

SELECT 2+1 AS Greeting from something where 2+1>1

this is working, but this one is not good solution as it involves a lot of computations. Can any one help me out in optimizing this query?

Is the 2+1 is addition of any two columns or direct number values? — DineshDB, Sep 03 '21 at 06:28
it is just number values similar to a calcuation I'm carrying out in my original query , it is not columns it is just numbers @DineshDB — Surya R, Sep 03 '21 at 06:30
Try `select * from (SELECT 2+1 AS Greeting from something) dt where Greeting>1;` — jarlh, Sep 03 '21 at 08:43

vsr · Accepted Answer · 2021-09-03T18:37:01.127

As @DinishDB mentioned projection Alias can't be used in WHERE.

SELECT Greeting
FROM something 
LET Greeting = 2+1
WHERE Greeting > 1

LET variables can be derived from document, can be chained. If let variables used in WHERE it will inline and choose index selection. Also variables evaluated before WHERE FILTER. If not used in WHERE it evaluated post FILTER to save evaluation. In above case it is constant. So happens FILTER is evaluate false, No documents will be fetched.

OR

WITH Greeting AS (2+1)
SELECT Greeting
FROM something 
WHERE Greeting > 1

If constant use WITH clause one time evaluation vs LET every document evaluation.

Couchbase Query Name Alias

1 Answers1