09-15-2005 6:39 AM
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
09-15-2005 8:56 AM
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
09-15-2005 6:44 AM
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.
09-15-2005 8:21 AM
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
09-15-2005 8:27 AM
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.
09-15-2005 8:34 AM
try this
select avbeln bposnr from
vbak as a join vbap as b
on avbeln eq bvbeln.
regards,
PJ
09-15-2005 8:39 AM
Hi,
This doesn't work in ST05.
(First of all you need to write sql in UPPER CASE...)
//MA
09-15-2005 8:43 AM
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.
09-15-2005 8:44 AM
oops, ignore my last post - i just saw your edited message....
09-15-2005 8:51 AM
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.
09-15-2005 8:56 AM
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
09-15-2005 9:08 AM
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
09-15-2005 9:27 AM
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