> 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 >> >
