Complex Joins (like)

[Former Member]

So in the old days of SQL server, I could do this :

Select *

from

     Table_A

     inner join

     Table_B

     on

          left(Table_A.Field,2) = Table_B.field

I cannot see a way to do this in HANA Studio (Attribute Views, Calculated Views etc)

Wondered if anyone could confirm this before I go off spending hours/days for an alternate solution !?

My case in point, is that I have 1 table with a list of departments (AA1, AA5, AA7, BB10, , BB3, BB01, CC99..etc)

I am trying to join this field to another table which puts all the AAs and EEs in 1 category and all the BBs and YYs in another.

I imagine I could create a derived column on the main table such as left(field,2) , and then join on that.

But that itself seems contrived.?

But maybe I am thinking too much 'old school' and I need a fresh perspective on how to accomplish this in HANA.

Thanks for any advice....

S

[lbreddemann]

Hi Simon,

you can do the same in SAP HANA - by using SQL directly.

For the graphical modelling tools you will find that not every option SQL provides is available.

This has to do with the fact, that many complex contructs are rather seldom used and also that something like your join condition will decrease the query performance.

If you want something like this to be executed real quickly you may consider using a generated column that is based on the LEFT() expression, just as you figured yourself.

Alternatively, if you really really only want to use the graphical tools, well, you can create a calculated column and use this one to join against.

I'd go with the calculated column, but hey, it's your database

- Lars