topic Re: Converting OPEN SQL to NATIVE SQL in Application Development and Automation Discussions

Converting OPEN SQL to NATIVE SQL

Former Member — Wed, 28 Jun 2006 10:56:10 GMT

Hi All,

How can I convert an OPEN SQL to Native SQL?

Is there any function module?

Do Help.

Regards

Dnyanesh

Re: Converting OPEN SQL to NATIVE SQL

Former Member — Wed, 28 Jun 2006 11:01:56 GMT

Hello,

Instead of converting the OPen to Native u can directly write the Native sql

Under <b>EXEC ..... ENDEXEC</b> Statement

If the answer is useful don't forget to reward points.

Regards,

Vasanth

Re: Converting OPEN SQL to NATIVE SQL

christian_wohlfahrt — Wed, 28 Jun 2006 11:06:05 GMT

Hi Dnyanesh!

I don't know a direct tool, but if your external database (that's the only place I would use native SQL) is from same type as SAP is running on, then there is a help:

Make a SQL-trace (ST05) of some select statements. In detail analysis you can see the syntax of the native statements.

Regards,

Christian

Re: Converting OPEN SQL to NATIVE SQL

Former Member — Wed, 28 Jun 2006 11:06:16 GMT

Hi All,

see I want to convert "select options" to native SQL statements.

Is it possible?

Regards

Dnyanesh

Re: Converting OPEN SQL to NATIVE SQL

Former Member — Wed, 28 Jun 2006 11:08:55 GMT

"select options" to native SQL statements -how u change to SQL,, i didn't get ..pls explain clearly..

Ramesh.

Re: Converting OPEN SQL to NATIVE SQL

Former Member — Wed, 28 Jun 2006 11:09:29 GMT

Y U WANT THIS...

RU GETING DUMP LIKE THAT...

'CX_SY_NATIVE_SQL_ERROR' which has been used

Re: Converting OPEN SQL to NATIVE SQL

Former Member — Wed, 28 Jun 2006 11:16:17 GMT

actually I am using a dynamic where clause.

where is following statement is not supported

move 'mantr in s_matnr' to v_string.

select matnr

into table i_matnr

from mara

where (v_string).

above statement is not working

the dump says incomplete in form "in(V1,,,,Vn)"

Do help

Regards

Dnyanesh

Re: Converting OPEN SQL to NATIVE SQL

Former Member — Wed, 28 Jun 2006 11:18:39 GMT

"in" statement is not supported in dynamic where clause.

I am working on 4.7

Regards

Dnyanesh

Re: Converting OPEN SQL to NATIVE SQL

christian_wohlfahrt — Wed, 28 Jun 2006 11:37:39 GMT

Hi Dnyanesh!

So you are not looking for native SQL...

In newer release it would be much easier:

starting with 610 you can use variables

starting with 640 you can use select-options.

So in your case you need to convert every line of s_matnr into a plain string.

With a lot of case and concatenate you can do this - might be easier to code some separate select statements instead.

Maybe you get an idea of the complexity when looking into the coding of SE16... complete dynamic handling of the selects.

Regards,

Christian

Re: Converting OPEN SQL to NATIVE SQL

former_member184495 — Wed, 28 Jun 2006 11:41:25 GMT

hi,

why do you need to convert 'select-options' to NSql,

you can code NSql in your ABAP Program, within EXEC...ENDEXEC with OPEN CURSOR, FETCH CURSOR, CLOSE CURSOR.

hope its clear,

else please explain your requirement in detail,

cheers,

Aditya.

Re: Converting OPEN SQL to NATIVE SQL

Former Member — Wed, 28 Jun 2006 12:05:48 GMT

Hi all,

Consider following code.

TABLES: lfa1.

DATA : i_lfa1 TYPE STANDARD TABLE OF lfa1.

DATA : v_string TYPE string.

SELECT-OPTIONS : s_lifnr FOR lfa1-lifnr.

v_string = 'lifnr in s_lifnr'.

SELECT * FROM lfa1 INTO CORRESPONDING FIELDS OF TABLE i_lfa1 WHERE (v_string) .

*******************************************

when I execute above code it gives me a dump....

following is the analysis

Error analysis

An exception occurred. This exception will be dealt with in more detail

below. The exception, assigned to the class 'CX_SY_DYNAMIC_OSQL_SYNTAX', was

not caught, which

led to a runtime error. The reason for this exception is:

The current ABAP/4 program attempted to execute an ABAP/4 Open SQL

statement containing a WHERE condition of the form WHERE (itab) or

WHERE ... AND (itab). The part of the WHERE condition specified at

runtime in the internal table itab contains the operator

IN (v1, ...,vn)

in incomplete form.

Now I have to convert select options to normal statements....

is there any function module for this?

Do help.

Regards

Dnyanesh

Re: Converting OPEN SQL to NATIVE SQL

Former Member — Wed, 28 Jun 2006 13:40:55 GMT

Native SQL is messy and slower. I'd work on getting your dynamic WHERE working.

Rob

Re: Converting OPEN SQL to NATIVE SQL

christian_wohlfahrt — Wed, 28 Jun 2006 14:00:28 GMT

Hi,

to repeat the question of Aditya:

why do you think you need a dynamic where-clause?

This code will bring the correct results:


DATA : i_lfa1 TYPE STANDARD TABLE OF lfa1,
       gs_lfa1 type lfa1.

SELECT-OPTIONS : s_lifnr FOR gs_lfa1-lifnr.

SELECT * FROM lfa1 INTO CORRESPONDING FIELDS OF TABLE i_lfa1 WHERE lifnr in s_lifnr.

And it's not import how many select options you define or if a user fill some values or not - that's the easiest way.

Only if you create a dynamic select statement, where you don't know the table before, then you need dynamic where-clause. (Maybe something more, but can't imagine now.)

Conversion would look like:


loop at s_lifnr.
case s_lifnr-sign.
when 'I'.
concatenate 'lifnr' into v_text.
when 'E'.
concatenate 'not lifnr' into v_text.
endcase.
case s_lifnr-option.
when 'EQ'.
concatenate v_text 'EQ' ''' s_lifnr-low ''' into v_text.
when 'BT'.
concatenate v_text 'between' ''' s_lifnr-low ''' 'and'
            ''' s_lifnr-high ''' into v_text.
when ...
endcase.
endloop.

I hope you get the idea. Still here 'lifnr' is hard-coded -> then you can just code the select direct. You would need to be even more dynamic to get a real advantage.

Regards,

Christian

Re: Converting OPEN SQL to NATIVE SQL

sridhar_k1 — Wed, 28 Jun 2006 14:18:08 GMT

I think you've opened another question for the same issue.

Use fm FVD_SELECT_OPTIONS_2_WHERE if available in your system.

Regards

Sridhar