Skip to Content
avatar image
Former Member

NTILE function in HANA SQL

Hi All,

I need to write an SQL in HANA, which would produce a similar result to an NTILE() in SQL 2008.

as per my knowledge NTILE function does not exist in HANA SQL. Has anybody written an SQL similar to this function?

e.g below the MSSQL 2008 code, which need to be re-written in HANA SQL.

select case when FLD1 in ('AA','XX')

then 'BB'

      else FLD1 end as FLD1,

 

  NTILE(3) Over (PARTITION by case when FLD1 in

   ('AA','XX')  then 'BB'

      else FLD1 end

   Order by FLD_Dis Asc) as Percentile,

     

  FLD_Dis,

  'ALL' as FLD2,

  'ALL' as FLD3,

 

  From

"SCHEMA"."TABLE1"

  where FLD2 >= 10

GROUP BY FLD3, FLD1, FLD2,FLD_Dis

   Order by 1,2,3,4;

Thanks

Ranjit

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Best Answer
    Nov 29, 2012 at 01:40 AM

    Hello Ranjit,

    no idea what your timeframe is, but window functions (like NTILE, SUM, RANK, LAG/LEAD...) are being developed and will find their ways into HANA standard.

    So maybe it pays to wait for this.

    However, I can't tell when these functions will be available.

    Cheers,

    Lars

    ADDITIONAL INFORMATION:

    Just had a look into the current SQL reference manual which is already out for SPS5: http://help.sap.com/hana/html/_esql_functions_window.html

    As you see, window functions will be available on a HANA system near you soon ;-)

    Message was edited by: Lars Breddemann

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member Lucas Oliveira

      Thx Lucas!

      exactly what I was looking for ... event though the reference is a bit too technical for business users. But I'll find my way ...

      Cheers,

      Uwe