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 >
