[jira] [Commented] (DRILL-1248) Add support for using aliases in group by

["Chandru (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/DRILL-1248?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14261011#comment-14261011 ] 

Chandru commented on DRILL-1248:
--------------------------------

@Jacques - is there any other way that column aliases can be used in group by ?

SELECT
PACCNT.PARTY_ACCOUNT_ID as PARTY_ACCOUNT_ID
,  PACCNT. PARTY_ACCOUNT_TYPE_CODE  as PARTY_ACCOUNT_TYPE_CODE
,  COALESCE((CASE 
WHEN (STXN.DAYS_SINCE_LAST_VISIT - 84) <= 0 
THEN 'Y' 
ELSE 'N' 
END),'N')  AS ACTIVE_LAST_12_WKS_IND
FROM   paccntF as PACCNT
LEFT OUTER JOIN WT_SHOPPING_TXN AS STXN
ON     PACCNT.PARTY_ACCOUNT_ID = STXN.PARTY_ACCOUNT_ID
GROUP BY  PARTY_ACCOUNT_ID,
PARTY_ACCOUNT_TYPE_CODE,
ACTIVE_LAST_12_WKS_IND;

> Add support for using aliases in group by
> -----------------------------------------
>
>                 Key: DRILL-1248
>                 URL: https://issues.apache.org/jira/browse/DRILL-1248
>             Project: Apache Drill
>          Issue Type: Bug
>          Components: SQL Parser
>            Reporter: Jim Scott
>             Fix For: Future
>
>
> when I select using a function and alias the resultant function value it won't parse properly saying the alias is ambiguous. I know that this is a debatable / questionable topic, but with this engine being so flexible it seems that in order to support all of the formatting, casting, etc.. that will likely occur having the group by support an alias would be a big deal. This in my opinion is nothing like an ordinal group by. 
> This works:
> select extract(year from to_date(crimes.datetime, 'MM/DD/YYYY hh:mm:ss a')) from BLAH group by extract(year from to_date(crimes.datetime, 'MM/DD/YYYY hh:mm:ss a'));
> This doesn't:
> select extract(year from to_date(crimes.datetime, 'MM/DD/YYYY hh:mm:ss a')) as mygroup from BLAH group by mygroup



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)