Do you know for sure that Power BI generates this kind of SQL? I suspect it doesn't, because it's not standard SQL. And by the way, Oracle doesn't support it.
I have no idea why I'm Tableau is generating this flavor of SQL. Calcite's tool integration is in pretty good shape, and that's because we've stayed close to the standard. There's no question that an extension like this would be nice, especially for end users writing SQL by hand (and contributions are most welcome!), but don't conflate it with tool integration. Julian > On Dec 15, 2016, at 06:21, Gian Merlino <[email protected]> wrote: > > That, I'm not sure how to do. There might be a similar config there for > Power BI. Ultimately it would be nice to have support for this in Calcite, > since it seems to be a pretty common extension to SQL (and makes many > queries easier to write by hand) but I'm not a Calcite expert enough to > know how hard that would be… > > Gian > > On Wed, Dec 14, 2016 at 9:57 PM, Kapil Ghodawat <[email protected]> > wrote: > >> Thanks a lot Gian. I think this would make Tableau work. >> >> But I am worried about connectivity from other SQL clients as i am planning >> in near future connectivity from Microsoft Power BI >> >>> On Wed, Dec 14, 2016 at 9:00 PM, Gian Merlino <[email protected]> wrote: >>> >>> With Tableau you can use a TDC file to tweak the SQL generation. If you >> set >>> CAP_QUERY_GROUP_BY_ALIAS and CAP_QUERY_GROUP_BY_DEGREE to "no" then >> Tableau >>> will avoid using aliases and ordinals for GROUP BY. See >>> http://kb.tableau.com/articles/knowledgebase/ >> customizing-odbc-connections >>> for more info. >>> >>> The relevant Calcite issue is >>> https://issues.apache.org/jira/browse/CALCITE-1306, but if all you care >>> about is getting Tableau to work then the TDC approach should do it. >>> >>> Gian >>> >>> On Wed, Dec 14, 2016 at 4:48 AM, Kapil Ghodawat <[email protected] >>> >>> wrote: >>> >>>> Hi Folks, >>>> >>>> I am using Apache Calcite to add SQL support for my custom data >> source. I >>>> receive SQLs from external SQL clients like Tableau and I have written >> an >>>> ODBC client for connecting to my data source, which basically delegates >>>> these SQLs (generated by Tableau) to my Java program where I use >> Calcite >>> to >>>> execute them. >>>> >>>> I am stuck at a place where I receive SQL which has GROUP BY clause >> that >>>> uses column ordinals instead of columns names and when I try to >> validate >>>> (parsing works fine) such SQLs through Calcite it fails. >>>> >>>> E.g. SELECT prod_name, store_name, sum(sales) from sales_table GROUP BY >>> 1, >>>> 2 >>>> >>>> (The SQL says GROUP BY on prod_name and store_name fields which are >>> column >>>> ordinal 1 and 2 respectively in the SELECT clause) >>>> >>>> >>>> I tried delving into the code of calcite and per my understanding I >>> believe >>>> that usage of column ordinals is supported in ORDER BY clause but not >> in >>>> the GROUP BY. >>>> >>>> Primarily, I want to know Is my understanding about no support of >> column >>>> ordinals in GROUP BY correct? Or is there something I am missing? >>>> >>>> Secondarily, if anyone knows about ODBC, is there a way I can >> tell/force >>>> the Tableau to send me column names instead of column ordinals in the >>> SQL? >>>> >>>> >>>> -- >>>> Best Regards, >>>> Kapil Ghodawat >>>> contact: +91 94254 86638 >>>> >>> >> >> >> >> -- >> Best Regards, >> Kapil Ghodawat >> contact: +91 94254 86638 >>
