Skip to Content
-2
Aug 01, 2019 at 12:28 PM

How can I convert this sql query to sap hana

91 Views Last edit Aug 01, 2019 at 12:28 PM 2 rev

  1. selectday(a.Date) as txn_day,
  2. case
  3. when a.Date='2019-01-04' then(select count(t.id)from type_11 t
  4. where (t.assetId="BDMRyZsmDZpgKhdM7fUTknKcUbVVkDpMcqEj31PUzjMy"andMONTHNAME(from_unixtime(t.timestamp/1000))='January' and year(from_unixtime(t.timestamp/1000))=2019) groupby txn_day
  5. ) else 0
  6. endas transaction_volume
  7. from (
  8. select last_day('2019-01-01') - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAYas Date
  9. from (select 0 as a unionallselect 1 unionallselect 2 unionallselect 3 unionallselect 4 unionallselect 5 unionallselect 6 unionallselect 7 unionallselect 8 unionallselect 9) as a
  10. crossjoin (select 0 as a unionallselect 1 unionallselect 2 unionallselect 3 unionallselect 4 unionallselect 5 unionallselect 6 unionallselect 7 unionallselect 8 unionallselect 9) as b
  11. crossjoin (select 0 as a unionallselect 1 unionallselect 2 unionallselect 3 unionallselect 4 unionallselect 5 unionallselect 6 unionallselect 7 unionallselect 8 unionallselect 9) as c
  12. ) a
  13. where a.Date between'2019-01-01' and last_day('2019-01-01') orderby a.Date