Skip to Content
avatar image
Former Member

Using "AS" for subquery in SAP HANA SQL

Hi,

I'd want to use "AS" to create a temp table for the result of a subquery. But the query can not be executed.How can I fix this error?

Do I need to create a view ?

select

  c_count, count(*) as custdist

from (

  select

  c_custkey,

  count(o_orderkey)

  from

  customer left outer join orders on

  c_custkey = o_custkey

  and o_comment not like '%special%requests%'

  group by

  c_custkey

  )as c_orders (c_custkey,c_count)

group by

  c_count

order by

  custdist desc,

  c_count desc;



Many thanks in advance!

Naomy



Message was edited by: Tom Flanagan

error2.PNG (10.7 kB)
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • Best Answer
    avatar image
    Former Member
    Jan 13, 2015 at 02:28 AM

    Hi Naomy,

    Please remove "(c_custkey,c_count)" after c_orders.

    Best regards,

    Wenjun

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Jan 13, 2015 at 12:51 PM

    Hi Naomy,

    An alternative to using sub-selects is to use the WITH statement. Your SQL could be rewritten as follows:

    WITH c_orders AS
    (
             SELECT   c_custkey,
                      count(o_orderkey) c_count 
             FROM     customer LEFT OUTER JOIN orders
                       ON c_custkey = o_custkey
                       AND o_comment NOT LIKE '%special%requests%'
             GROUP BY c_custkey)


    SELECT   c_count,
             count(*) AS custdist
    FROM     c_orders
    GROUP BY c_count
    ORDER BY custdist DESC,
             c_count DESC;

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Jan 13, 2015 at 02:36 AM

    I still need c_count as an alias for count(o_orderkey).Based on Wenjun's solution,I correct the SQL statement and it work now.

    select

      c_count, count(*) as custdist

    from (

      select

      c_custkey,

      count(o_orderkey) as c_count

      from

      customer left outer join orders on

      c_custkey = o_custkey

      and o_comment not like '%special%requests%'

      group by

      c_custkey

      )as c_orders

    group by

      c_count

    order by

      custdist desc,

      c_count desc;

    Add comment
    10|10000 characters needed characters exceeded