not able to get data from different table

[Former Member]

please can some body tell me how to join data from two or more tables in abap..

i am using a cluster table konv ..it is a cluster table so cann't use the inner join .i want to join konv t685t and vbak .so please tell me how can i do this ?

Edited by: ravipanwar.21 on Oct 25, 2010 2:36 PM

Edited by: ravipanwar.21 on Oct 25, 2010 2:41 PM

[Former Member]

please can some body tell me how to join data from two or more tables in abap..

i am using a cluster table konv ..it is a cluster table so cann't use the inner join .i want to join konv t685t and vbak .so please tell me how can i do this ?

Edited by: ravipanwar.21 on Oct 25, 2010 2:36 PM

Edited by: ravipanwar.21 on Oct 25, 2010 2:41 PM

[Former Member]

Hi,

In this way we can join the two or more tables

PARAMETERS p_cityfr TYPE spfli-cityfrom.

DATA: BEGIN OF wa,

carrid TYPE scarr-carrid,

carrname TYPE scarr-carrname,

connid TYPE spfli-connid,

END OF wa,

itab LIKE SORTED TABLE OF wa

WITH NON-UNIQUE KEY carrid.

SELECT s_{carrid s}carrname p~connid

INTO CORRESPONDING FIELDS OF TABLE itab

FROM scarr AS s

LEFT OUTER JOIN spfli AS p ON s_{carrid = p}carrid

AND p~cityfrom = p_cityfr.

LOOP AT itab INTO wa.

IF wa-connid = '0000'.

WRITE: / wa-carrid, wa-carrname.

ENDIF.

ENDLOOP.

As far as I know cluster tables can be read using IMPORT statement, macros, or specific FMs. You can't extract them directly using simple SELECT, thus joining is not allowed here two.

Nevetherless you are able to do join on internal tables which stores already extracted cluster tables. PROVIDE statement is the one to be used here.

Edited by: Lavanya YH1504 on Oct 25, 2010 2:59 PM

[Former Member]

but main problem is that i cannot use the join on this problem as the table konv is a cluster table.

so please tell me a way to join these three tables konv t685t and vbak by another method..

main fiellds of konv that i am using are

kschl,kstat and knumv

main fields of t685t are

kappl kschl and vtext

main fields of vbak that i am using are -

vbeln vkbur knumv

i want to join these three tables by using the common fields of the above mentioned table ..

so please help me...:(

[Former Member]

Well like you already know you cant use a join with cluster tables.

So you gotta do three seperate selects and join them manually by looping over your base table and reading the info from your other tables from internal tables then.

A join would just have spared you database time.

[Former Member]

not getting result after applying this method also ...so please give me the code after reading my previous post..

[nabheetscn]

Hi Florian,

I was just going throught your reply in which you have mentioned Joins are faster then separate selects. For me it has remained a debatical topic. I also believe in have separate selects rather then a join as join will put more load on data base. Please clarify and correct me if i am wrong.

Nabheet

[ThomasZloch]

 I also believe in have separate selects rather then a join as join will put more load on data base. Please clarify and correct me if i am wrong.

Wrong, please see here:

Thomas

[Former Member]

Hello NabheetMadan,

execution load on database itself is not really what matters. The database is quite fast with those joins.

The thing that has the most intense effect on performance is the pure data transfer from database to application server.

This means restricting the load of data from database to apllication is your upmost goal.

Thats why a join has a better performance than seperate selects.

If you do seperate selects, you will get ALL the data from ALL involved tables, while a join gives you way less data, at least when speaking of inner joins.

The database is very FAST and very powerful, so yeah you can really let the database do a LOT of your work beforehand without lacking performance.

[nabheetscn]

Hi Florian,

Very well said....It clears my doubt but one more question dont you think it depend on the system set up...how many work processes are there...?

Nabheet

[Former Member]

Hmm not sure about what work processes you are talking, if you mean the ones from SAP, then forget it, those are application server processes and have nothing to do with the database. Sure if 100 workd processes send a database query it takes more time than if just one would send a query. But even with 1000 a join would still be faster

If you are talking about database processes you might be right. If you trashed your database with lots of procedures, this really may take effect, but thats why we have a basis team to run those things. They know how to set it up and also what NOT to do.

[nabheetscn]

Thanks a lot..it clarifies my doubt.:):)

Regards

Nabheet

[Former Member]

yeah i agree with your answer that joins are better than the simple select statements..but what about cluster tables where i cannot apply joins then what should i do...i am new to abap so please tell me the procedure or syntax to apply separate select statements to get data from different tab les

[ThomasZloch]

Cluster tables warrant the use of FOR ALL ENTRIES. Once again, please visit the link I supplied above, a lot has already been analysed and discussed in the ABAP performance forum.

Thomas