Using Dynamic SQL to populate ALV

[Former Member]

Hi,

Does anyone know if it is possible to use the Runtime Type Services to obtain the structure definition for the results a dynamic select statement?

I am looking to create a simple web dynpro app that that will populate an ALV grid with the results of a SQL Select statement that has been manually typed in by a user. Is there an easy way to do this without having to pull apart the relevant details from (column_syntax), (from_syntax) etc.. in order to create a dynamic type that the ALV Data Node will be mapped to?

Kind Regards

Simon

[Clemenss]

Hi Simon,

RTTS will not help you.

You need to analyze the select string and create a SELECT (lt_columns) FROM (lv_table) INTO TABLE <dyntable> WHERE (lv_where). If you want to allow joins, subqueries, aggregates, for all entries and stuff like that it gets hard...

The only thing RTTS can help you is to create the result TABLE <dyntable>.

Regards

Clemens

[Former Member]

Hi Clemens,

Thanks for your reply.

It is the result table that I am actually interested in. I know how to build the actual SELECT statement from user input but I'm struggling to capture the results into a table without having to define the result table structure first (i.e. in order to implement the 'INTO CORRESPONDING FIELDS OF TABLE' clause). If I could somehow capture the results into a generically defined table and then use CL_ABAP_TABLEDESCR to get the actual line structure, I could then build the Context Node that will be mapped to the ALV Data interface node.

What I am trying to avoid, if at all possible, is having to deconstruct the actual SQL statement in order to pull out the parts that I would have to use to get RTTS to manually create a custom descriptionfor the results table.

Is this possible?

I have also come across classes in the SDB_ADBC package which look that they could possibly do the job. However, my understanding is that these use Native SQL rather then Open SQL.

[MarcinPciak]

If I understand you correclty at SELECT statement you already want to use specific table which you will fetch data to. Then logic would be as follows:

1. use RTTS to create table type (i.e. by adding component one by one -> structure ->table type) -> if you need some reference take a look at [Do you really know everything about typing? - part 2|http://www.sdn.sap.com/irj/scn/weblogs?blog=/pub/wlg/20887] [original link is broken];

2. create reference typed with this RTTS type and dereference it to empty table <table>

3. use SELECT ... INTO CORRESPONDING FIELDS OF <table>

4. use RTTS structure which you used to create type to type your WD CONTEXT -> this can be done dynamically [Dynamic Context Manipulation|http://help.sap.com/saphelp_nw70/helpdata/en/af/cb744176cb127de10000000a155106/frameset.htm]. Take note that for such dynamic CONTEXT performance will suffer

5. Create ALV based on context

If I missunderstood your query please rephrase your question

Regards

Marcin

[Former Member]

Hi,

I do not know what the structure of the result table will be until runtime as it will depend upon the SQL Query that has been input by the user. It is possible that the SELECT statement could use joins and aliases so it is not as straight forward as using a single dictionary table name to pull back structure details from the RTTS (I would have to analyse the clause to work out which tables are being referenced by what aliases etc...which is not desirable).

What I was hoping was that it was possible to execute the SELECT statement and capture the results table in a form where I can pass it to RTTS in order to obtain the line structure (without having to pre-define the exact line type of the table beforehand).

Kind Regards

Simon

[MarcinPciak]

What I was hoping was that it was possible to execute the SELECT statement and capture the results table in a form where I can pass it to RTTS in order to obtain the line structure (without having to pre-define the exact line type of the table beforehand).

Then I don't think this is possible as SELECT excepts already defined table so it could perform correct data trasnport b/w DB and application. So it is a matter of memory which needs to be allocated first for this table in order it can be fed with corresponding data from DB. That's why the structure of table should be know at runtime at latest. W/o this system wouldn't know if application has reserved enough space for data transport.

For me solution comes in acceptance of what user can input as parameters. If it is whole SQL statement then he doesn't leave you a choice - you have to analize it. If you parse his query for parameters like SOURCE table, INNER table, SOURCE fields etc. you gain possibility of using these metadata for creating necessary data objects during runtime, so that SQL statement (constrcuted based on user input) can be executed successfully.

Regards

Marcin

[Clemenss]

Hi Simon,

what you want is not covered by concepts of ABA programming language: The RESULT of the selection must be moved to an ABAP data object. All moves include an implicit type conversion. This means if your target area is not defined the DB interface does not know how the conversion should be applied. And, think about that, if an internal table is returned where all table lines are just combined into one big field, how to determine the line length? The DB interface usually knows both: What is coming from database and where to put the data in ABAP. If the two sides are not compatible, it is detected at compile time for static data definition or it causes a dump at run time.

I think you should put aside your idea.

Regards

Clemens

[Former Member]

I'll have a re-think.

Thank you both for your responses.