Add extra data at the end of the FROM, possible?

I was finding the need to write a query like this:

SELECT * FROM table AS t0 AS SYSTEM TIME '-2h' WHERE t0.enabled

The AS SYSTEM TIME addon should be at the end of the FROM, it MUST to be after AS t0 and that’s because I cannot do this:

from(t in fragment("table AS SYSTEM TIME '-2h'"), where: t.enabled)

It’s not giving me a correct SQL statement because of the order of AS directives. Is there any way to add suffixes for specific parts of the queries?

1 Like

What database is this?

It’s CockroachDB

Does something like this work

from(t in fragment("select * from table AS SYSTEM TIME '-2h'"), where: t.enabled)
1 Like

Indeed, the feature is a bit tricky because it should be between from and where, or more specifically, before where. If we are adding joins then it is required to be at the end of everything. That’s because I was asking if there’s a way to add some specific code at that place.

However, your solution is good and even I just realized I could use subqueries to encapsulate that table. Thanks for the replies!

1 Like

Ah I see what you are saying. I don’t think there is any option to do that.