topic Re: ST05 - Explain SQL in Application Development and Automation Discussions

ST05 - Explain SQL

Former Member — Thu, 15 Sep 2005 05:39:04 GMT

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

Re: ST05 - Explain SQL

former_member221770 — Thu, 15 Sep 2005 05:44:07 GMT

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.

Re: ST05 - Explain SQL

Former Member — Thu, 15 Sep 2005 07:21:36 GMT

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

Re: ST05 - Explain SQL

former_member221770 — Thu, 15 Sep 2005 07:27:20 GMT

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.

Re: ST05 - Explain SQL

Former Member — Thu, 15 Sep 2005 07:34:16 GMT

try this

select a_{vbeln b}posnr from

vbak as a join vbap as b

on a_{vbeln eq b}vbeln.

regards,

Re: ST05 - Explain SQL

Former Member — Thu, 15 Sep 2005 07:39:07 GMT

Hi,

This doesn't work in ST05.

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

//MA

Re: ST05 - Explain SQL

former_member221770 — Thu, 15 Sep 2005 07:43:05 GMT

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.

Re: ST05 - Explain SQL

former_member221770 — Thu, 15 Sep 2005 07:44:41 GMT

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

Re: ST05 - Explain SQL

former_member221770 — Thu, 15 Sep 2005 07:51:17 GMT

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 VBAK_{0 and VBAP}0).

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

Hope this answers your question.

Cheers,

Pat.

Re: ST05 - Explain SQL

christian_wohlfahrt — Thu, 15 Sep 2005 07:56:02 GMT

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

Re: ST05 - Explain SQL

Former Member — Thu, 15 Sep 2005 08:08:23 GMT

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

Re: ST05 - Explain SQL

christian_wohlfahrt — Thu, 15 Sep 2005 08:27:49 GMT

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