how to go through table cells

[Former Member]

Hi experts,

In a given table in hana, is it possible to go though cells. My current progress is hampered by the need to specify column names. For example,

SELECT <column name> FROM <table name>;

DECLARE i INT;

DECLARE j INT;

SELECT COUNT(*) INTO j FROM <table name>;

FOR i in 1..j DO

     if <table name>. <column name>[i] > 0.05

     then .........

END FOR;

But I want to go through all the cells regardless of the columns. Is it possible in hana with static sql?

Thanks

[michael_eaton3 · Accepted solution]

Hello

What is your requirement?  I'm sure there will be a more elegant solution than iterating over all rows and columns.

Michael

[Former Member · Accepted solution]

The context is

a) Table is given, Table names are available.

b) Hoping to go through cells regardless of columns, but

c) can refer to column name and row index if a specific value in a cell is found

Thanks

[michael_eaton3 · Accepted solution]

This doesn't appear to be a realistic requirement. 

Use of imperative constructs should be carefully considered with HANA (and any other RDBMS!).

As I suggested in your other similar post, this is possible with dynamic SQL.  Why don't you want to use it?  If its performance, then know that iterating over rows and columns will be the slowest way to do most operations.

Michael

[Former Member · Accepted solution]

Dynamic SQL is completely new to me. I will have to go through tutorial before I am able to perform it in dynamic SQL. I am happy to have your confirmation that with static SQL it is almost infeasible to loop through cells.

Is it possible for you to show me a link to a similar post solving this problem with dynamic SQL?

Thanks

[michael_eaton3 · Accepted solution]

As I suggested in your other similar post, there are SCN posts with examples, and the HANA documentation covers this SAP HANA Platform (Core) – SAP Help Portal Page.  In the top right of this page you can use the search - hana dynamic sql should do it.

I tried to understand your requirement because I doubt dynamic SQL or iterating over the rows and columns is required.  As you're new to HANA, I wouldn't recommend starting with these approaches, they are generally used as a last resort or when building applications on HANA.

Michael

[Former Member · Accepted solution]

You are professional! Yes, I am making application out of SAP HANA. I tried to avoid dynamic SQL, cursor, CE. But I found when making application, they are inevitablly involved

[michael_eaton3 · Accepted solution]

I wouldn't say that their use is inevitable. 

When writing applications to consume HANA data, push as much logic into the database as possible, and then consume the smallest result set in the consumption layer - and here is where you might find programming features to iterate over rows and columns.

Michael

[gopinath_kolli]

Yes, it is possible with Static SQL

use this Query:

Select

Case

    when clolumn1 > 0.05

          then ...

     else

end as column1,

Case

    when clolumn2 > 0.05

          then ...

     else

end as column2,

.....

....

....

<like wise for all columns>

From Table.

Happy HANA

Gopinath.