I cannot download a table that i queried

Question

A error appears every time I try to download a result table from a sync query. 

The preview table appears to me without any problems, but when i try to download it, this errors appears:

Error: IllegalArgumentException: net.sf.jsqlparser.JSQLParserException JSQLParserException: ParseException: Encountered "(" at line 14, column 27.\r\nWas expecting one of:\r\n "INTO" ...\r\n "FROM" ...\r\n "," ...

Comments

My query is:

SELECT *
FROM (
    SELECT
        s.id  AS splus_id,
        s.ra  AS splus_ra,
        s.dec AS splus_dec,

        v.*,

        q3c_dist(s.ra, s.dec, v.ra, v.dec) * 3600.0 AS sep_arcsec,

        ROW_NUMBER() OVER (
            PARTITION BY s.id
            ORDER BY q3c_dist(s.ra, s.dec, v.ra, v.dec)
        ) AS rn

    FROM mydb://splus_objects AS s
    JOIN vmc_dr7.source AS v
    ON q3c_join(
        s.ra,
        s.dec,
        v.ra,
        v.dec,
        1.0 / 3600.0
    )
) AS x
WHERE rn = 1

Answer 1

Hi, some query functionality is limited in our website-based query tool because it uses an ADQL parser, which does not currently support certain PostgreSQL features such as the ROW_NUMBER() window function.

I would recommend running this query through our Jupyter notebook server instead, where the full PostgreSQL syntax is supported. For example:

from dl import queryClient as qc

q = """
SELECT *
FROM (
    SELECT
        s.id  AS splus_id,
        s.ra  AS splus_ra,
        s.dec AS splus_dec,
        v.*,
        q3c_dist(s.ra, s.dec, v.ra, v.dec) * 3600.0 AS sep_arcsec,
        ROW_NUMBER() OVER (
            PARTITION BY s.id
            ORDER BY q3c_dist(s.ra, s.dec, v.ra, v.dec)
        ) AS rn
    FROM mydb://splus_objects AS s
    JOIN vmc_dr7.source AS v
    ON q3c_join(
        s.ra,
        s.dec,
        v.ra,
        v.dec,
        1.0 / 3600.0
    )
) AS x
WHERE rn = 1
"""

# Save output to a CSV file
result = qc.query(sql=q, out='splus_vmc_output.csv')

This approach should run successfully and save the query results directly to a CSV file.