Re: TPC-DS queries

> Hi,
>
> I think that the sql is not valid. Based on the order by
> documentation, a column label cannot be used in an expression.
>
> from    https://www.postgresql.org/docs/11/queries-order.html
>      > Note that an output column name has to stand alone, that is, it
> cannot be used in an expression.

Thanks. Yes, you are correct. The line should be something like:

   ,case when grouping(i_category)+grouping(i_class) = 0 then i_category end

> Regards
> s.
>
> On 11.03.2019 06:30, Tatsuo Ishii wrote:
>> I played with TPC-DS and found some of them can't be executed because
>> of SQL errors and I am not sure why.
>>
>> For example with query 36:
>>
>> select
>>      sum(ss_net_profit)/sum(ss_ext_sales_price) as gross_margin
>>     ,i_category
>>     ,i_class
>>     ,grouping(i_category)+grouping(i_class) as lochierarchy
>>     ,rank() over (
>>      partition by grouping(i_category)+grouping(i_class),
>>      case when grouping(i_class) = 0 then i_category end
>>      order by sum(ss_net_profit)/sum(ss_ext_sales_price) asc) as
>>      rank_within_parent
>>   from
>>      store_sales
>>     ,date_dim       d1
>>     ,item
>>     ,store
>>   where
>>      d1.d_year = 2000
>>   and d1.d_date_sk = ss_sold_date_sk
>>   and i_item_sk  = ss_item_sk
>>   and s_store_sk  = ss_store_sk
>>   and s_state in ('TN','TN','TN','TN',
>>                   'TN','TN','TN','TN')
>>   group by rollup(i_category,i_class)
>>   order by
>>     lochierarchy desc
>>    ,case when lochierarchy = 0 then i_category end -- line 25 is here.
>>    ,rank_within_parent
>>    limit 100;
>> psql:query_0.sql:1935: ERROR:  column "lochierarchy" does not exist
>> LINE 25:   ,case when lochierarchy = 0 then i_category end
>>
>> I have follwed the instruction here.
>> https://ankane.org/tpc-ds
>>
>> PostgreSQL is master branch HEAD. For me, the SQL above looks to be
>> valid.
>>
>> Best regards,
>> --
>> Tatsuo Ishii
>> SRA OSS, Inc. Japan
>> English: http://www.sraoss.co.jp/index_en.php
>> Japanese:http://www.sraoss.co.jp
>>
>