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
