CODEWITHSUNDEEP
sqlhana
hakeydotcom
hakeydotcom

Reputation: 41

SQL script: new column 1 if value exist in other column else 0

I have first table T1 in SAP HANA DB like this:

T1
----------------------------
| key | value | date       |
----------------------------
| 1   |  A    | 01-02-2018 |
| 2   |  B    | 01-03-2017 |
| 3   |  C    | 01-02-2017 |
| 4   |  D    | 01-02-2018 |
----------------------------

And the other table T2:

T2
---------------------
| value | date       |
----------------------
|  B    | 01-02-2017 |
|  B    | 01-05-2017 |
|  D    | 01-02-2019 |
----------------------

I need to add to T1 new column as indicator with value '1' if T1.value exists in T2.value and T2.date < T1.date for this value, else '0'. I can only use select query or SQL script for this, with no insert or update statements.

Expected result:

----------------------------------------
| key | value | date        | indicator |
----------------------------------------
| 1   |  A    | 01-02-2018  | 0         |
| 2   |  B    | 01-03-2017  | 1         |
| 3   |  C    | 01-02-2017  | 0         |
| 4   |  D    | 01-02-2018  | 0         |
-----------------------------------------

Please, help with query or script on HANA SQL.

Upvotes: 0

Views: 1557

Answers (2)

Barbaros &#214;zhan
Barbaros &#214;zhan

Reputation: 65278

You can use a correlated subquery

select T1.*, 
       ( select sign(count(1)) from T2 where T2.date < T1.date and T2.value = T1.value )
       as indicator
  from T1;

The sign() function returns 1 provided there's at least one row satisfying the condition

Demo

Upvotes: 1

forpas
forpas

Reputation: 164099

I need to add to T1 new column as indicator with value '1' if T1.value exists in T2.value and T2.date < T1.date for this value, else '0'.

This is solved with EXISTS:

select 
  t1.*,
  case 
    when exists (
      select 1 from t2 
      where value = t1.value and date < t1.date
    ) then '1'
    else '0'
  end indicator
from t1

Upvotes: 3

Related Questions