Querying Superset dataset using jinja

[ampil]

I am trying to run a query for saved dataset using jinja in SQL lab.
I tried:
select * from {{dataset(115)}}

I face the error: oracle error: ORA-00907: missing right parenthesis

The jinja flag is turned on: 'ENABLE_TEMPLATE_PROCESSING': True,
The datasource_id = 115 is related with the dataset on the Datasets tab.

My query is simple:

SELECT "Name", "Id"   
FROM dbo."Clients"
WHERE "Status" = 1

What am I missing? How to run queries like select * from {{dataset(115)}} ?

[ampil]

@dosu, please help.

[dosubot[bot]]

I found a similar unsolved discussion on using SQL templating in datasets, which might be relevant to your issue: How to use sql templating in dataset ^[1].

Regarding your specific issue with the ORA-00907: missing right parenthesis error, it seems like there might be a problem with how the dataset function is being used in your Jinja template. The dataset function is expected to generate a subquery, and the error might be related to how Oracle handles the generated SQL.

Here are a few things to check and try:

1. Verify Dataset Configuration: Ensure that the dataset with ID 115 is correctly configured in Superset, including its columns and any custom SQL expressions.

2. Check SQL Syntax: The error message suggests a syntax issue. Ensure that the SQL generated by the dataset function is valid for Oracle. You might need to manually inspect the generated SQL to identify any syntax issues.

3. Test with a Simple Query: Try running a simpler query without the dataset function to ensure that the connection and basic query execution are working correctly.

4. Review Jinja Context: Make sure that the Jinja context is correctly set up and that any necessary macros or functions are available.

If these steps don't resolve the issue, you might need to manually construct the SQL query to ensure compatibility with Oracle's syntax requirements.

To continue talking to Dosu, mention @dosu.

---

^{Help Dosu learn! Give it feedback: Great Response | Irrelevant Answer | Incorrect Sources | Too Verbose | Hallucination | Bug Report | Other}

[imedlee]

The {{dataset()}} macro in Superset always uses the AS keyword to generate SQL with aliases, such as (SELECT...) AS dataset_1, independent of the database. Oracle does not allow AS to use table aliases in subqueries. Therefore, using the {{dataset()}} macro in oracle will result in the ORA-00907 error. #34613