topic Re: Explain SQL in SQL trace. in Application Development and Automation Discussions

Explain SQL in SQL trace.

Former Member — Wed, 30 Aug 2006 13:11:08 GMT

Hi All,

I have a doubt with regards to the 'Explain one SQL request option' in ST05 SQL trace transaction.This option displays a screen where an SQL statement could be given and analysed.

This works fine when a staright forward & simple SQL statements like below are given :

SELECT VBELN

FROM VBEP

WHERE VBELN = 0000000001

AND POSNR = 00010

AND MBDAT <= 20060830

AND "J_3AABGRU" = ' '

But, when a bit complex SQL (like the one given below) with JOINs are given, its gives out an error with message "An error in Explain SQL. Change UPDATE/DELETE to SELECT if possible."

SELECT A~VBELN "Sales order document

FROM VAKPA AS A

INNER JOIN VBUK AS B

ON A_{VBELN = B}VBELN

INTO TABLE RT_SO_ALL

FOR ALL ENTRIES IN RT_KNKK

WHERE A~KUNDE = RT_KNKK-KUNNR

AND A~PARVW = C_PAYER

AND A~VKORG = RV_VKORG

AND ( B~GBSTK <> C_COMPLETE

OR ( B~GBSTK = C_COMPLETE AND

B~LFSTK = C_PARTIALLY_PROCESSED ) ).

Would appreciate any help in this regard.

Warm Regards,

Partha.

Re: Explain SQL in SQL trace.

Former Member — Wed, 30 Aug 2006 13:14:06 GMT

Whane you enter your sql manualy for explaining. - the sentence must be written in native sql - not open sql used in abap (It is different).

BR, JAcek

emaple for native with joins.

SELECT

T_00 . "ERNAM" , T_00 . "VBELN" , T_00 . "VRKME" , T_00 . "KWMENG" ,

T_00 . "POSNR" , T_00 . "MATNR" , T_00 . "PSTYV" , T_01 . "GBSTA" ,

T_01 . "ABSTA" , T_02 . "KALAB" , T_02 . "MATNR" , T_02 . "LGORT" ,

T_02 . "ERSDA"

FROM

"VBAP" T_00 , "VBUP" T_01 , "MSKA" T_02

WHERE

( T_01 . "MANDT" = ? AND T_01 . "MANDT" = ? AND T_01 . "VBELN" = T_00 .

"VBELN" AND T_01 . "POSNR" = T_00 . "POSNR" ) AND ( T_02 . "MANDT" = ?

AND T_02 . "VBELN" () = T_00 . "VBELN" AND T_02 . "POSNR" () = T_00 .

"POSNR" ) AND T_00 . "MANDT" = ? AND T_00 . "VBELN" = ?

Message was edited by: Jacek Slowikowski

Re: Explain SQL in SQL trace.

Former Member — Wed, 30 Aug 2006 13:17:12 GMT

Hi Parthasarathy,

Yes the EXPLAIN SQL in ST05 will not work for complex select statements. Also the tool will not work for FOR ALL ENTRIES.

This tool works on database and FOR ALL ENTRIES is not recogonized.

This option is only available when the statement contains a LOCATION comment, for example, when you call EXPLAIN using transaction ST05 ® Trace List.

The location of the statement in the ABAP source code is displayed in a separate window.

Cheers

Message was edited by: Vijayendra Rao

Re: Explain SQL in SQL trace.

Former Member — Wed, 30 Aug 2006 13:20:22 GMT

Hi,

perhaps this is what the

note 557449

describes, when you have an old 4.0 Release?:

copy & paste from note -

Symptom

ST05 is not working for join statements. When you try to analyse the

statement of the listing with 'Explain SQL' , then a pop up is

displayed with the message "An error in EXPLAIN SQL. Change

UPDATE/DELETE to SELECT if possible".

Other terms
ST05, Explain, SQL

Reason and Prerequisites
It is an error in the program. The sql statement constructed for the

explin is cut off. One program is need to be modified.

Solution

These changes are applied for the R3 release 4.0. For higher releases the changes are already modified in the basis support package systems. Please add one line to the form skip_table in the report RSXPLAIN as mentioned below.

Änderung für DB2: Falls keine Tabelle erkannt wird (weil z.B.

Klammern in der FROM-Klausel vorkommen), ist das für DB2 kein

Problem, da die gesamte Anweisung übergeben wird.

Hinweis 193752

if ( tabname = space ) and ( sy-dbsys(3) ne 'DB2' )

and ( sy-dbsys(3) ne 'DB4' ) "d021078

and ( sy-dbsys(3) ne 'DB6' ) "d021078

and ( sy-dbsys(3) ne 'MSS' ) . "Add this line

if tabname = space.

no table name ==> invalid input string

rc = 1.

exit.

endif.

copy & paste from note -

Regards,

Stefan

Re: Explain SQL in SQL trace.

Former Member — Wed, 30 Aug 2006 13:51:50 GMT

Hi Jacek,

Thanks for your reply.

I tried writing a Native SQL(given below) for the below SQL . I still received the same error.

<u>Native SQL</u>

SELECT T_00.VBELN

FROM "VAKPA" T_00 "VBUK" T_01

WHERE( T_00."KUNDE" = 0000000001 AND T_00."PARVW" = RG AND T_00."VKORG" = 1100 AND T_01."GBSTK" <> C)

SELECT A~VBELN

FROM VAKPA AS A

INNER JOIN VBUK AS B

ON A_{VBELN = B}VBELN

INTO TABLE RT_SO_ALL

FOR ALL ENTRIES IN RT_KNKK

WHERE A~KUNDE = RT_KNKK-KUNNR

AND A~PARVW = C_PAYER

AND A~VKORG = RV_VKORG

AND ( B~GBSTK <> C_COMPLETE

OR ( B~GBSTK = C_COMPLETE AND

B~LFSTK = C_PARTIALLY_PROCESSED ) ).

The value of RT_KNKK-KUNNR = '0000000001'

C_PAYER = 'RG'

RV_VKORG = '1100'

C_COMPLETE = 'C'

C_PARTIALLY_PROCESSED = 'B'.

Stefan-Iam using SAP 4.6C. Would this still be a problem?

Thanks & Regards,

Partha.

Re: Explain SQL in SQL trace.

Former Member — Wed, 30 Aug 2006 14:02:48 GMT

Look post below

Message was edited by: Jacek Slowikowski

Re: Explain SQL in SQL trace.

Former Member — Wed, 30 Aug 2006 14:17:42 GMT

that code works fine - i have tried in my system - try in yours then try with your statment:

SELECT

T_00 . "VBELV" , T_00 . "POSNV" , T_00 . "VBTYP_N" , T_00 . "RFMNG" , T_01 . "GBSTA"

FROM

"VBFA" T_00 , "VBUP" T_01

WHERE

( T_01 . "MANDT" = 1 AND T_01 . "VBELN" = T_00 . "VBELN" AND T_01 . "POSNR" = T_00 . "POSNN" )

AND T_00 . "MANDT" = 1 AND T_00 . "VBELV" = 1 AND T_00 . "POSNV" = 1 AND T_00 . "VBTYP_N" = 1

Plese reward helpful answers !!!

Message was edited by: Jacek Slowikowski

Re: Explain SQL in SQL trace.

Former Member — Fri, 01 Sep 2006 06:32:28 GMT

Hi Jacek,

Thanks for your reply.It was really helpful.

Thanks all for your kind replies and suggestions.

Cheers,

Partha.