Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Showing results for 
Search instead for 
Did you mean: 

ST05 - Explain SQL

Former Member
0 Kudos

Hi,

I want to try out different kinds of sql-statements inside ST05.

Now:

This doesn't work:

SELECT VBAK~VBELN FROM VBAK

This works:

SELECT VBELN FROM VBAK

I need to get the first option to work as well. Mainly because I want to make some inner join stuff...

Any pointers ?

//Martin

1 ACCEPTED SOLUTION

christian_wohlfahrt
Active Contributor
0 Kudos

Hi Martin!

In ST05-Explain you have to use (something like) native SQL. I copied statement from an explain of a trace:

SELECT  * FROM   "USR21" 
WHERE "MANDT" = :A0 AND "BNAME" = :A1

and replaced the variables with some values (shown in the list below):

SELECT  * FROM   "USR21" WHERE "MANDT" = "200" AND "BNAME" = "MY_USER"

Now I'm also able to make new statements 'from scratch':

SELECT * FROM "VBAK"
WHERE   "MANDT" = '200' AND "VBELN" = '000000616'

Just write your statement in a test report (local) in development system, make a trace, copy this in your production (or test) system and replace the variables accordingly.

I guess, the syntax might be DB dependent, but by this way that's not a problem.

Regards,

Christian

11 REPLIES 11

former_member221770
Contributor
0 Kudos

Martin,

That's right, you only use the '~' when you do joins.

select vbak~vbeln from vbak

is not a join

try


data: begin of tbl_vbak occurs 0,
        vbeln  like vbak-vbeln,
        posnr  like vbap-posnr,
      end of tbl_vbak.

select vbak~vbeln
       vbap~posnr
       into table tbl_vbak
       from vbak
       inner join vbap
       on vbak~vbeln = vbap~vbeln.

Cheers,

Pat.

0 Kudos

Hi,

Let me put it this way:

Could you make a sql join between VBAK and VBAP that works in ST05.

I need to use the EXPLAIN button in ST05 in order to see the index hit etc...

//Martin

Message was edited by: Martin Andersson

0 Kudos

Hi Martin,

I don't think you can "join" VBAK and VBAK. Doesn't make much sense to me. Why would you want to join a database table to itself?

Cheers,

Pat.

0 Kudos

try this

select avbeln bposnr from

vbak as a join vbap as b

on avbeln eq bvbeln.

regards,

PJ

0 Kudos

Hi,

This doesn't work in ST05.

(First of all you need to write sql in UPPER CASE...)

//MA

0 Kudos

Ok Martin,

Try this bit of ABAP:


data: begin of tbl_vbak occurs 0,
        order1  like vbak-vbeln,
        order2  like vbak-vbeln,
      end of tbl_vbak.

select t1~vbeln t2~vbeln
       into table tbl_vbak
       from vbak as t1
       inner join vbak as t2
       on t1~vbeln = t2~vbeln.

Cheers,

Pat.

0 Kudos

oops, ignore my last post - i just saw your edited message....

0 Kudos

Martin,

If you put this sample code in a ABAP program:


data: begin of tbl_vbak occurs 0,
        vbeln  like vbak-vbeln,
        posnr  like vbap-posnr,
      end of tbl_vbak.
 
select vbak~vbeln
       vbap~posnr
       into table tbl_vbak
       from vbak
       inner join vbap
       on vbak~vbeln = vbap~vbeln.

then run ST05 to activate the trace, run your program, deactivate the trace, then view the trace.

Look for the VBAK PREPARE line and click onit and hit Explain.

You will see at the bottom a little tree structure. It will identify the index hit here (in this case VBAK0 and VBAP0).

You will also see the parsed SQL statement on the top half of the screen.

Hope this answers your question.

Cheers,

Pat.

christian_wohlfahrt
Active Contributor
0 Kudos

Hi Martin!

In ST05-Explain you have to use (something like) native SQL. I copied statement from an explain of a trace:

SELECT  * FROM   "USR21" 
WHERE "MANDT" = :A0 AND "BNAME" = :A1

and replaced the variables with some values (shown in the list below):

SELECT  * FROM   "USR21" WHERE "MANDT" = "200" AND "BNAME" = "MY_USER"

Now I'm also able to make new statements 'from scratch':

SELECT * FROM "VBAK"
WHERE   "MANDT" = '200' AND "VBELN" = '000000616'

Just write your statement in a test report (local) in development system, make a trace, copy this in your production (or test) system and replace the variables accordingly.

I guess, the syntax might be DB dependent, but by this way that's not a problem.

Regards,

Christian

0 Kudos

Hi,

We are getting there....

Let me clarify.

Goto ST05.

Click "Enter SQL Statement"

Paste in our sql join statement.

Click Explain.

SELECT VBELN FROM VBAK -- works fine

SELECT X_VBAK~VBELN FROM VBAK AS X_VBAK -- no explain here...

The problem we are having is how do we handle alias in ST05....?

//MA

0 Kudos

Hi,

so a join as example brings this result:

SELECT
  T_00 . "VBELN"
FROM
  "VBAK" T_00 , "VBAP" T_01
WHERE
  ( T_01 . "MANDT" = :A0 AND T_00 . "VBELN" = T_01 . "VBELN" ) AND T_00 . "MANDT" = :A1 AND T_00 .
  "VBELN" > :A2

- and without join no explicit definition is possible (or would make sense).

Regards,

Christian