native sql

[Former Member]

hai ,

i have a aelect query how to convert this open sql into Native Sql. This for Selelcting the list of Sales order for The corresponding Customer Purchase Order and this Po Can be entered in Either Any case, so the requirement is for this case.

SELECT A_{VBELN A}ERDAT A_{KUNNR A}BSTNK FROM VBAK AS A

INNER JOIN VBAP AS B ON

A_{VBELN = B}VBELN INTO TABLE I_SALES WHERE

A~VKORG IN S_VKORG AND

A~AUART IN S_AUART AND

A~ERNAM IN S_ERNAM AND

A~ERDAT IN S_ERDAT AND

A~VBELN IN S_VBELN AND

A~BSTNK IN S_BSTNK AND

A~KUNNR IN S_KUNNR AND

B~MATNR IN S_MATNR

i have converted as written below but it is dumping when i exeute ?? what might be the problem in the code.

exec SQL..

SELECT A_{VBELN A}ERDAT A_{KUNNR A}BSTNK FROM VBAK AS A

INNER JOIN VBAP AS B ON

A_{VBELN = B}VBELN INTO TABLE :I_SALES WHERE

A~VKORG IN :S_VKORG AND

A~AUART IN :S_AUART AND

A~ERNAM IN :S_ERNAM AND

A~ERDAT IN :S_ERDAT AND

A~VBELN IN :S_VBELN AND

A~BSTNK IN :S_BSTNK AND

upper(A~KUNNR) IN :S_KUNNR AND

B~MATNR IN :S_MATNR.

endexec.

cheers

[Former Member]

Hi,

I think there might be some exception which has to be handled. It can be done as follows.

NOTE : When it dumps it asks us to catch the

exception by using the class

'CX_SY_NATIVE_SQL_ERROR' which has been used

below.

Check it up if this helps.

TRY.

EXEC SQL..

SELECT A_{VBELN A}ERDAT A_{KUNNR A}BSTNK FROM VBAK AS A

INNER JOIN VBAP AS B ON

A_{VBELN = B}VBELN INTO TABLE :I_SALES WHERE

A~VKORG IN :S_VKORG AND

A~AUART IN :S_AUART AND

A~ERNAM IN :S_ERNAM AND

A~ERDAT IN :S_ERDAT AND

A~VBELN IN :S_VBELN AND

A~BSTNK IN :S_BSTNK AND

upper(A~KUNNR) IN :S_KUNNR AND

B~MATNR IN :S_MATNR.

ENDEXEC.

catch CX_SY_NATIVE_SQL_ERROR into OREF.

TEXT = OREF->GET_TEXT( ).

ENDTRY.

if not TEXT is initial.

write / TEXT.

endif.

Message was edited by: shakuntala Negi

[LucianoBentiveg]

No, you can´t use select-options in native sql.

[Former Member]

Hi,

You dont need to convert this into native sql.

Use Tool ST05---Transaction

Start your program in debuging mode.

place brake point at begining of the select query.

stop the execution of the program

start another session

enter transaction ST05

select SQL trace

Active trace button

enter for all the popup

Go back to your program

execute the the select query

go back to the ST05 Transaction screen

click Deactivate the Trace

and view the Result

It will show you the select query in the native form

NOTE :- If you use Select-option it will show only option which has value in it . it will drop the select-Option if it is initial or blank.

Feel free to ask any question

Reward point if useful

Regards ,

Manoj B gupta

[Former Member]

hai manoj,

can the select-options be used in Native sql because my report requirement is i will be having the input parameters has select-options .

cheers

[former_member181962]

Hi Rajani,

Actually the select-options in the open sql are converted into set of individual where clauses using parameters.

Native sqls do not support select-options.

Regards,

Ravi

[Former Member]

Hi Rajani,

i dont have any idea regarding that; But what you can do is read the Select-option table it has the following fields

Sign--- I( Inclusive ) E (Exclusive )

Option--- Eq ( Equal to )... only for Value in low field

BT ( between ) Between ( LOW and High )

etc

LOW--- Values of that particular data type

HIGH--- Values of that particular data type

When it I , BT 110 200

you can write select query has

select * from table

where field between low and high.

This how ABAP works

Since Select option have more than one rows you have to prepare select query in a loop.

If you want more details

Use Transaction --- ABAPDOCU enter Select-option ( click on Question mark Button for the search field ).

Have you got your Native SQL using ST05,

Regards

Manoj

[Former Member]

hai all,

how to write the following code in open sql in NAtive SQL

SELECT A_{VBELN A}ERDAT A_{KUNNR A}BSTNK FROM VBAK AS A

INNER JOIN VBAP AS B ON

A_{VBELN = B}VBELN INTO TABLE I_SALES WHERE

A~VKORG IN S_VKORG AND

A~AUART IN S_AUART AND

A~ERNAM IN S_ERNAM AND

A~ERDAT IN S_ERDAT AND

A~VBELN IN S_VBELN AND

A~BSTNK IN S_BSTNK AND

A~KUNNR IN S_KUNNR AND

B~MATNR IN S_MATNR.

cheers