Using "AS" for subquery in SAP HANA SQL

[naomy_xu]

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

[Former Member · Accepted solution]

Hi Naomy,

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

Best regards,

Wenjun

[Former Member]

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;

[naomy_xu]

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;

[Former Member]

Yes, you got it. You need to add "as c_count", since in your outer select statement you use c_count which does not exist in your original SQL statement.

Best regards,

Wenjun

[naomy_xu]

Thank you very much !   >_<Y