08-30-2006 2:11 PM
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 AVBELN = BVBELN
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.
08-30-2006 2:17 PM
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
VJ
Message was edited by: Vijayendra Rao
08-30-2006 2:14 PM
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
08-30-2006 2:51 PM
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)
<u>SQL in ABAP</u>
SELECT A~VBELN
FROM VAKPA AS A
INNER JOIN VBUK AS B
ON AVBELN = BVBELN
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.
08-30-2006 3:02 PM
08-30-2006 3:17 PM
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
09-01-2006 7:32 AM
Hi Jacek,
Thanks for your reply.It was really helpful.
Thanks all for your kind replies and suggestions.
Cheers,
Partha.
08-30-2006 2:17 PM
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
VJ
Message was edited by: Vijayendra Rao
08-30-2006 2:20 PM
Hi,
perhaps this is what the
note 557449
describes, when you have an old 4.0 Release?:-
-
copy & paste from note -
-
Symptom
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