Nested query gives expected result, but full query returns no results

SQL++
1

I am trying to do a query select count(*) from bucket b where b.variable=“value” and DATE_ADD_STR(b.createdOn,24,‘hour’) >= (select MAX(createdTimestamp) updatedOn from bucket b1 where b.variable2=“value” and b.createdOn > DATE_ADD_STR(b.createdOn,-24,‘hour’))

The inner query:

select MAX(createdTimestamp) updatedOn from bucket b1 where b.variable2=“value” and b.createdOn > DATE_ADD_STR(b.createdOn,-24,‘hour’)

works fine, but when I run the whole thing it gives me no results

2

Also inserting the result of the inner query into the main query works as well

3

Your inner SELECT needs to be a SELECT RAW … to get just the value, or you need to reference the field. (The result from a SELECT is an array of objects.)

So:

select count(*)
from bucket b 
where b.variable=“value” 
and DATE_ADD_STR(b.createdOn,24,‘hour’) >= 
    (select RAW MAX(createdTimestamp)
     from bucket b1
     where b.variable2=“value”
     and b.createdOn > DATE_ADD_STR(b.createdOn,-24,‘hour’)
    )[0]

Or

select count(*)
from bucket b 
where b.variable=“value” 
and DATE_ADD_STR(b.createdOn,24,‘hour’) >= 
    (select MAX(createdTimestamp) updatedOn
     from bucket b1
     where b.variable2=“value”
     and b.createdOn > DATE_ADD_STR(b.createdOn,-24,‘hour’)
    )[0].updatedOn

HTH.

Ref: SELECT Clause | Couchbase Docs

1 Like
4

This worked perfectly thank you!

5

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.