Converting OPEN SQL to NATIVE SQL

[Former Member]

Hi All,

How can I convert an OPEN SQL to Native SQL?

Is there any function module?

Do Help.

Regards

Dnyanesh

[Former Member]

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

[christian_wohlfahrt]

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

[Former Member]

Hi All,

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

Is it possible?

Regards

Dnyanesh

[Former Member]

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

Ramesh.

[Former Member]

Y U WANT THIS...

RU GETING DUMP LIKE THAT...

'CX_SY_NATIVE_SQL_ERROR' which has been used

[Former Member]

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

[Former Member]

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

I am working on 4.7

Regards

Dnyanesh

[christian_wohlfahrt]

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

[former_member184495]

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.

[Former Member]

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

[christian_wohlfahrt]

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

[Former Member]

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

Rob

[sridhar_k1]

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