Details

    • Sub-task
    • Status: Resolved
    • Major
    • Resolution: Cannot Reproduce
    • None
    • None
    • Materialized views
    • None

    Description

       explain create materialized view q16 as select  
         count(distinct cs_order_number) as `order count`
        ,sum(cs_ext_ship_cost) as `total shipping cost`
        ,sum(cs_net_profit) as `total net profit`
      from
         catalog_sales cs1
        ,date_dim
        ,customer_address
        ,call_center
      where
          d_date between '1999-4-01' and 
                 (cast('1999-4-01' as date) + 60 days)
      and cs1.cs_ship_date_sk = d_date_sk
      and cs1.cs_ship_addr_sk = ca_address_sk
      and ca_state = 'IL'
      and cs1.cs_call_center_sk = cc_call_center_sk
      and cc_county in ('Richland County','Bronx County','Maverick County','Mesa County',
                        'Raleigh County'
      )
      and exists (select *
                  from catalog_sales cs2
                  where cs1.cs_order_number = cs2.cs_order_number
                    and cs1.cs_warehouse_sk <> cs2.cs_warehouse_sk)
      and not exists(select *
                     from catalog_returns cr1
                     where cs1.cs_order_number = cr1.cr_order_number)
      

      Error

      Error: Error while compiling statement: FAILED: SemanticException [Error 10249]: Line 24:8 Unsupported SubQuery Expression 'cr_order_number': Only 1 SubQuery expression is supported. (state=42000,code=10249)
      

      Attachments

        Activity

          People

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

            Dates

              Created:
              Updated:
              Resolved: