Self JOIN and IN Query

1

Hi, I used a IN Query, JOIN Query in SQL inside dremio and searched my elastic Index, in the “Final Physical Transformation” I am not getting the extact elasticsearch query to run, May I know how to get that please.
Thanks

2

Hi @prem

Please send us the profile generated for this job

Share A Query Profile

Thanks
@balaji.ramaswamy

3

Hi Balaji,

Thanks for that, attached the profile, below is the SQL

SELECT * FROM “_doc” c
INNER JOIN (
SELECT SUBSTR(order_id,-4) temp1
FROM “_doc”
GROUP BY SUBSTR(order_id,-4)
HAVING COUNT(SUBSTR(order_id,-4)) > 3
) temp2
ON
SUBSTR(c.order_id,-4) = temp2.temp1
ORDER BY SUBSTR(c.order_id,-4)

Regards

Prem

52b4f00a-0b8f-4809-aa41-ac4343ef2b56.zip (12.8 KB)