Is it something related to the json file you used? I tried the following query, replacing "business.json' with cp.`employee.json`. It works fine. (no row returned, which is expected).
0: jdbc:drill:zk=local> WITH X1 . . . . . . . . . . . > AS . . . . . . . . . . . > (SELECT city, . . . . . . . . . . . > AVG(review_count) AS city_reviews_avg . . . . . . . . . . . > FROM cp.`employee.json` . . . . . . . . . . . > GROUP BY city), . . . . . . . . . . . > X2 . . . . . . . . . . . > AS . . . . . . . . . . . > (SELECT X1.city, X1.city_reviews_avg, . . . . . . . . . . . > MAX(X1.city_reviews_avg) . . . . . . . . . . . > OVER () AS city_reviews_avg_max . . . . . . . . . . . > FROM X1) . . . . . . . . . . . > SELECT X2.city, X2.city_reviews_avg . . . . . . . . . . . > FROM X2 . . . . . . . . . . . > WHERE X2.city_reviews_avg_max = X2.city_reviews_avg; +-------+-------------------+ | city | city_reviews_avg | +-------+-------------------+ +-------+-------------------+ No rows selected (3.462 seconds) On Wed, Sep 23, 2015 at 4:23 PM, Bob Rumsby <brum...@maprtech.com> wrote: > Based on our docs, multiple table references are valid, and this is > consistent with other systems where WITH is supported in queries. However, > maybe the problem is the reference to X1 in the definition of X2? > > Bob > > On Wed, Sep 23, 2015 at 3:41 PM, Neeraja Rentachintala < > nrentachint...@maprtech.com> wrote: > >> Team >> Is this a valid Drill syntax (i.e 2 tables in with clause). >> >> WITH X1 >> AS >> (SELECT city, >> AVG(review_count) AS city_reviews_avg >> FROM `business.json` >> GROUP BY city), >> X2 >> AS >> (SELECT X1.city, X1.city_reviews_avg, >> MAX(X1.city_reviews_avg) >> OVER () AS city_reviews_avg_max >> FROM X1) >> SELECT X2.city, X2.city_reviews_avg >> FROM X2 >> WHERE X2.city_reviews_avg_max = X2.city_reviews_avg; >> >> I am hitting this error. *Error: SYSTEM ERROR: NoSuchFieldError: constants* >> >> A single table specification in WITH clause works fine. >> >> -Neeraja >>
