topic Re: Native SQL in ABAP in Application Development and Automation Discussions

Native SQL in ABAP

Former Member — Thu, 02 Nov 2006 04:59:41 GMT

I need a query or stored procedure to get data from a table name specified by a variable (here 'TableName').

The stored procedure would be called everytime with a parameter that specifies the tablename to be queried.

The following piece of code works in Oracle, but when I use it in ABAP it gives a 'Table Access Error'.

DECLARE @Query varchar(300), @TableName varchar(30)
SET @TableName = 'Tab1'
SET @Query = 'SELECT * FROM ' + @TableName
EXEC (@Query)

Please help me out.

Re: Native SQL in ABAP

Former Member — Thu, 02 Nov 2006 05:22:54 GMT

Hello Nitya,

Try to do it like this.

EXEC SQL.

YOUR CODE HERE

ENDEXEC.

Try to check the documentation for native sql. You may get an idea!!

Hope this helps, dont forget to reward.

Gabriel

Re: Native SQL in ABAP

Former Member — Thu, 02 Nov 2006 05:33:29 GMT

Yes, I know that the procedure is to be written within EXEC and ENDEXEC.

My question was how do I pass a new value everytime to that procedure i.e. a new tablename is passed to it everytime it is executed.

SET @TableName = 'Tab1'

Instead of specifying a tablename directly , I want to pass a dynamic name to the variable.

Re: Native SQL in ABAP

seshatalpasai_madala — Thu, 02 Nov 2006 05:47:40 GMT

Hi,

I am not sure what is your requirment to use Native SQL. This can be done in the open SQL itself that is passing a table name.

DATA: VAR1 type string value 'DEFAULT_TAB'.

SELECT * FROM VAR1 into ITAB.

Regards,

Sesh

Re: Native SQL in ABAP

former_member186741 — Thu, 02 Nov 2006 05:48:04 GMT

You can use:

SET @TableName = table_name

where table_name is a vaiable or parameter in the abap.

Re: Native SQL in ABAP

Former Member — Thu, 02 Nov 2006 05:50:10 GMT

Hello again,

Sorry for the misunderstanding!!

Have you tried to concatenate the table??

I mean to concatenate the values as a string??

I have never done it before, but it could work. Try to write it like a dynamic select:

column_syntax = `car name plate`.

dbtab_syntax = `zcars_names`.

SELECT (column_syntax)

FROM (dbtab_syntax)

INTO CORRESPONDING FIELDS OF TABLE itab.

try to concatenate or assign a value on a string variable.

myvar = 'tab1'

...

SET @TableName = myvar

...

Will it work, i dont know?? Try it!!...

Be careful with the quotation marks...you know how SQL make us crazy with it!!

Gabriel

Message was edited by: Gabriel Fernando Pulido V.

Re: Native SQL in ABAP

Former Member — Thu, 02 Nov 2006 06:06:49 GMT

Ok. My actual requirement is like this. I have a certain number of tables (say 40) in oracle.

I need to write a select query on each table.

Now, I don't want to write 40 select queries.

Instead, I thought of writing a SQL procedure and pass the tablename each time. So that the select query is executed everytime with a new tablename which changes dynamically.

You can assume that the tablenames to be queried are present in an internal table or an oracle table.

I hope this makes my problem statement more clear!

Re: Native SQL in ABAP

Former Member — Thu, 02 Nov 2006 06:12:44 GMT

hI AGAIN!

Try something like this:

data myvar type string.

" ittab-> cant be tehe it with the table names!!

loop at ittab.

myvar = iittab-tabname.

EXEC.

DECLARE @Query varchar(300), @TableName varchar(30)

SET @TableName = myvar

SET @Query = 'SELECT * FROM ' + @TableName

EXEC (@Query)

ENDEXEC.

ENDLOOP.

gabriel

Message was edited by: Gabriel Fernando Pulido V.