hana - SQL Script - manipulate value based on previous non zero value -


i'm using hana sql script , after computation data in view/table has displayed below

col       col b       col c 1000042965  20090830    15.9 1000042965  20090906    0 1000042965  20090913    0 1000042965  20090920    14.2 1000042965  20090927    0 1000042965  20091004    0 1000042965  20091011    14.4 1000042967  20090830    13.2 1000042967  20090906    0 1000042967  20090913    12.9 1000042967  20090920    0 1000042967  20090927    0 1000042967  20091004    11.2 1000042967  20091011    11.2 

and expected output as

col       col b       excepted result col c 1000042965  20090830    15.9 1000042965  20090906    15.9 1000042965  20090913    15.9 1000042965  20090920    14.2 1000042965  20090927    14.2 1000042965  20091004    14.2 1000042965  20091011    14.4 1000042967  20090830    13.2 1000042967  20090906    13.2 1000042967  20090913    12.9 1000042967  20090920    12.9 1000042967  20090927    12.9 1000042967  20091004    11.2 1000042967  20091011    11.2 

i want return previous not null values. tried using windows lag function, desired output not above.

lag(col c) on (partition col a) 

i referred below link , try implement same logic, still couldn't finding previous non-zero value date-ordered list

select distinct col a, col b, case when col c = 0      ifnull(( select col c       table t2       cast(t2.col b date) < t1.col b , t1.col = t2.col , t2.col c > 0       ),0)     else col c end col c table t1 

my guess problem subquery has no order by or limit (i think hana sql uses limit), can return more 1 row.

the query quite hard read. shouldn't have spaces in column names, might work:

select cola, colb,        (case when colc = 0              ifnull((select colc                           table t2                            t1.cola = t2.cola ,                                 t2.colb < t1.colb ,                                 t2.colc > 0                            order t2.colb desc                           limit 1                          ), 0)              else colc         end) colc table t1 

Comments

Popular posts from this blog

Payment information shows nothing in one page checkout page magento -

tcpdump - How to check if server received packet (acknowledged) -