GitHub user dosubot[bot] added a comment to the discussion: Unable to Parse SQL
When Using Jinja Templates (Superset 6.0.0.0 + Oracle 19)
<!-- Answer -->
The "Unable to parse SQL" error with Jinja templates in Superset 6.0.0.0 and
Oracle is usually caused by the template generating invalid SQL, such as an
empty `IN` clause when `filter_values` returns no values. To fix this, assign
the filter values to a variable and only include the `IN` clause if the
variable is non-empty:
```sql
{% set doc_ids = filter_values('DocumentId') %}
{% if doc_ids %}
AND "DocumentId" IN {{ doc_ids | where_in }}
{% endif %}
```
This prevents invalid SQL like `AND "DocumentId" IN ()` when no filter values
are present [discussion](https://github.com/apache/superset/discussions/35171).
For Oracle, be aware that certain macros (like `{{dataset()}}`) previously
generated SQL with the `AS` keyword for aliases, which Oracle does not allow in
subqueries. Recent Superset updates have fixed this, so make sure you're not
using old patterns that trigger Oracle syntax errors [pull
request](https://github.com/apache/superset/pull/34613).
If Jinja blocks (`{% ... %}`) are not being processed correctly, installing
`sqloxide` can help Superset parse SQL queries containing Jinja templates
[issue](https://github.com/apache/superset/issues/32342).
Superset now surfaces template processing errors with detailed messages, so if
you see a parsing error, check if the rendered SQL is valid for Oracle and that
your template logic avoids empty or invalid clauses [pull
request](https://github.com/apache/superset/pull/36422).
If you still have issues, try running a minimal query with a hardcoded value
for `filter_values` to confirm the template logic works, and check for any
Oracle-specific syntax requirements in your SQL.
<!-- Dosu Comment Footer -->
*To reply, just mention [@dosu](https://go.dosu.dev/dosubot).*
---
<sup>How did I do?
[Good](https://app.dosu.dev/response-feedback/0153ae8e-76c4-4a6f-a077-069fa801998d?feedback_type=great_response)
|
[Irrelevant](https://app.dosu.dev/response-feedback/0153ae8e-76c4-4a6f-a077-069fa801998d?feedback_type=irrelevant_answer)
|
[Incorrect](https://app.dosu.dev/response-feedback/0153ae8e-76c4-4a6f-a077-069fa801998d?feedback_type=incorrect_sources)
|
[Verbose](https://app.dosu.dev/response-feedback/0153ae8e-76c4-4a6f-a077-069fa801998d?feedback_type=too_verbose)
|
[Hallucination](https://app.dosu.dev/response-feedback/0153ae8e-76c4-4a6f-a077-069fa801998d?feedback_type=hallucination)
| [Report
🐛](https://app.dosu.dev/response-feedback/0153ae8e-76c4-4a6f-a077-069fa801998d?feedback_type=bug_report)
|
[Other](https://app.dosu.dev/response-feedback/0153ae8e-76c4-4a6f-a077-069fa801998d?feedback_type=other)</sup>
[](https://app.dosu.dev/a28d3c7e-a9d3-459e-9fb6-3a6f9ff4f357/ask?utm_source=github) [](https://cursor.com/link/prompt?text=The%20%22Unable%20to%20parse%20SQL%22%20error%20with%20Jinja%20templates%20in%20Superset%206.0.0.0%20and%20Oracle%20is%20usually%20caused%20by%20the%20template%20generating%20invalid%20SQL%2C%20such%20as%20an%20empty%20%60IN%60%20clause%20when%20%60filter_values%60%20returns%20no%20values.%20To%20fix%20this%2C%20assign%20the%20filter%20values%20to%20a%20variable%20and%20only%20include%20the%20%60IN%60%20clause%20if%20the%20variable%20is%20non-empty%3A%0A%0A%60%60%60sql%0A%7B%25%20set%20doc_ids%20%3D%20filter_values%28%27DocumentId%27%29%20%25%7D%0A%7B%25%20if%20doc_ids%20%25%7D%0A%20%20AND%20%22DocumentId%22%20IN%20%7B%7B%20doc_ids%20%7C%20where_in%20%7D%7D%0A%7B%25%20endif%20%25%7D%0A%60%60%60%0AThis%20prevents%20invalid%20SQL%20like%20%60AND%20%22Do
cumentId%22%20IN%20%28%29%60%20when%20no%20filter%20values%20are%20present%C2%A0%5Bdiscussion%5D%28https%3A//github.com/apache/superset/discussions/35171%29.%0A%0AFor%20Oracle%2C%20be%20aware%20that%20certain%20macros%20%28like%20%60%7B%7Bdataset%28%29%7D%7D%60%29%20previously%20generated%20SQL%20with%20the%20%60AS%60%20keyword%20for%20aliases%2C%20which%20Oracle%20does%20not%20allow%20in%20subqueries.%20Recent%20Superset%20updates%20have%20fixed%20this%2C%20so%20make%20sure%20you%27re%20not%20using%20old%20patterns%20that%20trigger%20Oracle%20syntax%20errors%C2%A0%5Bpull%20request%5D%28https%3A//github.com/apache/superset/pull/34613%29.%0A%0AIf%20Jinja%20blocks%20%28%60%7B%25%20...%20%25%7D%60%29%20are%20not%20being%20processed%20correctly%2C%20installing%20%60sqloxide%60%20can%20help%20Superset%20parse%20SQL%20queries%20containing%20Jinja%20templates%C2%A0%5Bissue%5D%28https%3A//github.com/apache/superset/issues/32342%29.%0A%0ASuperset%20now%20surfaces%20template%20processing%20er
rors%20with%20detailed%20messages%2C%20so%20if%20you%20see%20a%20parsing%20error%2C%20check%20if%20the%20rendered%20SQL%20is%20valid%20for%20Oracle%20and%20that%20your%20template%20logic%20avoids%20empty%20or%20invalid%20clauses%C2%A0%5Bpull%20request%5D%28https%3A//github.com/apache/superset/pull/36422%29.%0A%0AIf%20you%20still%20have%20issues%2C%20try%20running%20a%20minimal%20query%20with%20a%20hardcoded%20value%20for%20%60filter_values%60%20to%20confirm%20the%20template%20logic%20works%2C%20and%20check%20for%20any%20Oracle-specific%20syntax%20requirements%20in%20your%20SQL.) [](https://go.dosu.dev/discord-bot) [](https://twitter.com/intent/tweet?text=%40dosu_ai%20helped%20me%20solve%20this%20issue!&url=https%3A//github.com/apache/superset/discussions/37489)
GitHub link:
https://github.com/apache/superset/discussions/37489#discussioncomment-15617203
----
This is an automatically sent email for [email protected].
To unsubscribe, please send an email to:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
