Comparing scalar variable with Table variable in SAP HANA SQL

[former_member597564]

Comparing scalar variable with Table variable in SAP HANA SQL

i am working on a requirement where i need to write an IF...condition to compare a scalar variable with Table variable.

Scalar variable ex., 'a' will be user input to particular column 'X' and

it should be compared with data of column 'X' of the Table 'T' used for which i have created Table variable 'b'.

Here 'a' should be compared with 'b'

If 'a' exists in 'b' then i should UPDATE the Table 'T'

else

INSERT operation should be done on Table 'T'

[pfefferf · Accepted solution]

A simple solution can be do to a count on your table. Something like that:

declare lv_count integer;

select count(*) into lv_count from 😛 where x = :a;
  
if :lv_count > 0 then
  -- update table t
else
  -- insert into table t
end if;

In newer versions you can use the SEARCH option for table variables.

position = :b.search(("X"), (:a));

if :position is not null then
  -- update table t
else
  -- insert into table t
end if;