XMLWordPrintableJSON

    Details

    • Type: Sub-task
    • Status: Open
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: None
    • Fix Version/s: None
    • Component/s: Materialized views
    • Labels:
      None

      Description

      explain create materialized view qmv39 as 
      with inv as
      (select w_warehouse_name,w_warehouse_sk,i_item_sk,d_moy
             ,stdev,mean, case mean when 0 then null else stdev/mean end cov
       from(select w_warehouse_name,w_warehouse_sk,i_item_sk,d_moy
                  ,stddev_samp(inv_quantity_on_hand) stdev,avg(inv_quantity_on_hand) mean
            from inventory
                ,item
                ,warehouse
                ,date_dim
            where inv_item_sk = i_item_sk
              and inv_warehouse_sk = w_warehouse_sk
              and inv_date_sk = d_date_sk
              and d_year =2000
            group by w_warehouse_name,w_warehouse_sk,i_item_sk,d_moy) foo
       where case mean when 0 then 0 else stdev/mean end > 1)
      select inv1.w_warehouse_sk,inv1.i_item_sk,inv1.d_moy,inv1.mean, inv1.cov
              ,inv2.w_warehouse_sk,inv2.i_item_sk,inv2.d_moy,inv2.mean, inv2.cov
      from inv inv1,inv inv2
      where inv1.i_item_sk = inv2.i_item_sk
        and inv1.w_warehouse_sk =  inv2.w_warehouse_sk
        and inv1.d_moy=2
        and inv2.d_moy=2+1
      
      Error: Error while compiling statement: FAILED: SemanticException org.apache.hadoop.hive.ql.optimizer.calcite.CalciteViewSemanticException: Duplicate column name: w_warehouse_sk (state=42000,code=40000)
      

        Attachments

          Activity

            People

            • Assignee:
              Unassigned
              Reporter:
              rajesh.balamohan Rajesh Balamohan
            • Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

              • Created:
                Updated: