cancel
Showing results for 
Search instead for 
Did you mean: 

Questions in HANA Sql

Former Member
0 Kudos

Hi Team,

I have some questions as under:

     1)how can i use a dynamic tables/fields in the Cursor definition :

               say for eg.., Delcare Cursor Cur_1(dyn_field1 varchar(10), dyn_field2 varchar(10), dyn_table varchar(15)

                               for select <dyn_field1> <dyn_field2>... <dyn_fieldn> from <dyn_table> ? as it is not acceptable in the Cursor definition to use as:

                               for select :dyn_field1 :dyn_field2 from :dyn_table ? as it is not acceptable in the Cursor definition to use a Colon(:) before them..

     2)how to processing the records fetched from a table type:

               say for eg.., if I got a set of records from another procedure that contained a set of records(of a table type)

                                 how to Loop at the records and check for the fields inside them... say I got table that has 5 records and each with 3 fields in it...

                                   after I receive them in the calling procedure, how can Loop at them and check for conditions on the fields... Just like how the

                                   processing will be done in ABAP... Loop at itab into wa_tab.... check the conditions on the fields and Endloop.. ?

     3)how to check for dynamic fields while looping in the Cursor records...

               say for eg.., if I got 10 records from a stored procedure and I am not aware of the names of the fields in the cursor records

                                   if the code is like: I fetched all the fields from a table which has been derived out of another Cursor...

                                   For tab1 as Cursor_1 do

                                             tab1 has a field called Hana_table and this changes for every cursor loop...

                                             I defined a Cursor_2 as Select * from tab1.hana_table...

                                             Now in the Cursor loop of tab2.... For tab2 as Cursor_2 do

                                                  how do I know which fields are there in the Cursor_2 and to process them? 

     4)how to use Local and Global Internal tables in HANA sql and what is its impact on the HANA tables ?

Please provide answers on the above points as they are very much need for my current development.

Thanks in advance for your inputs.

Regards,

Vishnu                   

Accepted Solutions (0)

Answers (1)

Answers (1)

lbreddemann
Active Contributor
0 Kudos

Hi Vishnu,

if your current project really depends on those features, it's looking real bad, because dynamic cursor handling is not supported in SAP HANA SQLScript.

The data structures need to be known at compile time and there's no API for cursor-metadata or something similar available.

That's the answer to your questions 1-3.

To your last question: I assume you are talking of global or local _temporary_ tables, right?

How those work and what they do differently is actually well documented. Just read up on them.

Out of interest: apparently you don't know the above things about SAP HANA. How come your project depends on them? Why don't you tell us more about your development - maybe there are other ways to implement your requirements that don't require dynamic cursors.

- Lars

Former Member
0 Kudos

hi Lars,

thanks for your inputs...

I am very much new to HANA and Sql - just 1.5 months into these areas...

well my requirement is as under:

          I was given a task to check the differences in the loaded tables in HANA...

1)there is a table(say tab_1) which has 3 fields(say field1, field2 and field3) and field1 refers to a HANA table and field2 and field3 refer to the fields in that HANA table...

2)now I need to read all the records of the table tab_1 and for every record of tab_1, I need to take the HANA table(field1) and get the entries against the fields 2 and 3... this referred HANA table can have multiple records....

3)now I have to check other fields say field4 and field5( of the table tab_1) with that of each and every record obtained against the HANA table entries of the Step(2)...

4)if for any one record of Step(2) matches with the field values of field2 and field3(of tab_1), I need to do some INSERTION into a table...

Dynamic things are:
a)the HANA table might change for each record of records of table tab_1

b)the fields in the HANA table get changed.. need compare field2 with first field of HANA table and field3 with the 2nd record of HANA table...

for this requirement I resorted to Cursors as I am not aware of any other Sql result sets that can be useful in my requirement...

I used the following logic:

      For tab1 as cursor_1(fetched fields 1 to 5)

          For tab2 as cursor_2(fetched the fields -> from HANA table of cursor_1-hana_table)

               Now I created a Local temporary table using EXEC statement as under:
              va_sql := 'INSERT INTO #hana_table select "' || tab2.field1 || '" as field1'  || ' ' ||

                '"' || tab2.field2 || '" as field2 ' || 'from ' || :va_hana_table;

              exec (:va_sql);

         the Stored procedure was successful, how to read the values present in #hana_table?

     I am getting an error:
     invalid table name:  Could not find table/view #hana_table in schema SYSTEM

     if I use as under:

     for tab3 as "#hana_table" do

     end for;

     the error is:
     identifier must be declared

I hope the requirement is clear to you... please provide your valuable suggestions...

Regards,

Vishnu

lbreddemann
Active Contributor
0 Kudos

Actually I am not quite sure what your requirement is exactly.

I highly recommend that you start to use proper variable and table names instead of table_1, 2, 3...

That made me read each sentence at least twice, wasting my time by that.

From what I understand this is the situation:

  • you barely now databases, SQL or SAP HANA
  • the solution requires dynamic structure evaluation and some kind of meta programming
  • the application logic is not quite straight and very easy to get wrong

I don't see yet what the overall goal of this exercise is, so advice is difficult to provide.

It _looks_ like some data transformation/loading task and for that I'd usually say: don't reinvent the wheel. Take a ETL tool, e.g. SAP DataServices, and use that to implement the logic.

This would take away the burden of actual programming for a good part of the job and you could focus on the actual logic.

Other than that I can only recommend to get some good material (usually it's still books) on SQL and database programming and design.

- Lars