Need to get rows with last 10 recent dates from table, even if some of the dates are missing from table.

This query is working fine to get dates but the problem is same query in android room dao throwing compile error at 'SELECT x+1' x cannot be resolved.

@Query(WITH RECURSIVE
 stepDtailsTable(x) AS (
        SELECT 0
        UNION ALL
        SELECT x + 1 FROM stepDtailsTable
        LIMIT(SELECT((julianday('2019-03-20')-julianday('2019-03-01')))+1)
        )
        SELECT date(julianday('2019-03-01'),'+'||x||' days')as date FROM stepDtailsTable")
StepDetailsPojo getLastTenDayData();

1 Answers

0
J.R. On

I have noticed that you are using constants for the julian dates whereas you wanted to query the last 10 recent dates from a table. Here is a query that does that, with filling gaps from the maximum date found in the table to 10 days earlier. I cannot test on your platform so it may not resolve the compiler error you are getting, maybe because RECURSIVE is not supported. Hopefully it gets you a step further.

WITH RECURSIVE
 cte(x) AS (
    SELECT julianday(date(max(thedate))) from TheDataTable  
    UNION ALL
    SELECT x-1 FROM cte
    LIMIT 10
)
SELECT date(x) FROM cte