SELECT statement

[Former Member]

Hello,

I want to select some data from CRM tables. My problem is the datas are distributed over 7 different tables which are linked together.

How can I implement such a select statement which select dates from seven tables?

I tried it whith inner joins but at the sixth join I get the error message that the class CL_O2_DUMMY_CLASS doesn´t contains a interface "g". "g" is the alias for the last the table. I named my tables in the joins with letters from "a" to "g".

Regards

Philipp

[Former Member]

Hi,

May be views wud be better option for ur problem.

<REMOVED BY MODERATOR>

Edited by: Alvaro Tejada Galindo on Apr 30, 2008 5:16 PM

[Former Member]

Hi,

could you explain me how can I implement this or give me an example?

Regards

Philipp

[Former Member]

You can use for all entries instead:

FOR ALL ENTRIES WHERE

Syntax

... FOR ALL ENTRIES IN itab WHERE ... col operator itab-comp ...

Effect

If the addition FOR ALL ENTRIES is specified before the language element WHERE, then the components comp of the internal table itab can be used as operands when comparing with relational operators.

The internal table itab must have a structured line type and the component comp must be compatible with the column col.

The logical expression sql_cond of the WHERE condition can comprise various logical expressions by using AND and OR. However, if FOR ALL ENTRIES is specified, there must be at least one Comparison with a column of the internal table itab, which can be specified either statistically or dynamically (Release 6.40 and higher). In a statement with a SELECTstatement with FOR ALL ENTRIES, the addition ORDER BY can only be used with the addition PRIMARY KEY.

The whole logical expression sql_cond is evaluated for each individual line of the internal table itab. The resulting set of the SELECT statement is the union of the resulting sets from the individual evaluations. Duplicate lines are automatically removed from the resulting set. If the internal table itab is empty, the whole WHERE statement is ignored and all lines in the database are put in the resulting set.

Notes

In Release 6.10 and higher, the same internal table can be specified after FOR ALL ENTRIES and after INTO.

The addition FOR ALL ENTRIES is only possible before WHERE conditions of the SELECT statement.

If the additions PACKAGE SIZE or UP TO n ROWS are specified together with FOR ALL ENTRIES, they are not passed to the database system but are applied instead to the resulting set once all selected rows on the application server have been imported.

With duplicated rows in the resulting set, the addition FOR ALL ENTRIES has the same effect as if addition DISTINCT were specified in the definition of the selection quantity. Unlike DISTINCT, the rows are not deleted from the database system but are deleted on the application server from the resulting set.

Addition FOR ALL ENTRIES is only possible for WHERE conditions of the SELECT statement.

Example

Exporting all flight data for a specified departure city. The relevant airlines and flight numbers are first put in an internal table entry_tab, which is evaluated in the WHERE condition of the subsquent SELECT statement.

PARAMETERS p_city TYPE spfli-cityfrom. 

TYPES: BEGIN OF entry_tab_type, 
         carrid TYPE spfli-carrid, 
         connid TYPE spfli-connid, 
       END OF entry_tab_type. 

DATA: entry_tab   TYPE TABLE OF entry_tab_type, 
      sflight_tab TYPE SORTED TABLE OF sflight 
                       WITH UNIQUE KEY carrid connid fldate. 

SELECT carrid connid 
       FROM spfli 
       INTO CORRESPONDING FIELDS OF TABLE entry_tab 
       WHERE cityfrom = p_city. 

SELECT carrid connid fldate 
       FROM sflight 
       INTO CORRESPONDING FIELDS OF TABLE sflight_tab 
       FOR ALL ENTRIES IN entry_tab 
       WHERE carrid = entry_tab-carrid AND 
             connid = entry_tab-connid. 

[Former Member]

Hi,

You could split your select and write a FOR ALL ENTRIES which actually is a bit Performance friendly if you have the Keys..of all the tables

example

Select *

From VBAK

Into Table T_VBAK

Where VBELN IN S_VBELN.

Select *

From VBAP

Into Table T_VBBAP

For All Entries In T_VBAK

Where VBELN = T_VBAK-VBELN.

santhosh

[Former Member]

Hi Kaluvala,

but with this solution I have the datas in different tables.

Is it possible that I have all the entries in one table?

Regards

Philipp

[christine_evans]

We don't use CRM, but if the tables you are trying to join are normal, transparant database tables then you should be able to use a join. I don't understand how class CL_O2_DUMMY_CLASS relates to what you are doing or why it is giving you an error. Have you tried using the letter h instead of g.....

Some people consider that joining as many as seven tables is a bad thing, but if the joins are properly indexed and the tables not too large then it can be fine.

[ThomasZloch]

post the statement here, so we can have a look.

Greetings

Thomas

[Former Member]

select distinct a_{partner a}name_org1 a_{name_org2 d}discuss

into corresponding fields of table tkun1

from BUT000 as a

inner join CRMD_ORDER_INDEX as b

on a_{partner = b}partner_no and b_{process_type_ix = 'Z011' and b}object_type = 'BUS2000126'

inner join CRMD_ORDERADM_I as c

on b_{header = c}header

inner join CRMD_ACTIVITY_I as d

on c_{guid = d}guid

inner join CRMD_PRODUCT_I as e

on c_{guid = e}guid

inner join COMM_CATEGORY as f

on e_{prod_hierarchy = f}category_id

inner join COMM_CATEGORYT as g

on f_{category_guid = g}gategory_guid.

[Former Member]

hi

see if this works:

select distinct a~partner a~name_org1 a~name_org2 d~discuss
into corresponding fields of table tkun1
from BUT000 as a
inner join CRMD_ORDER_INDEX as b
on a~partner = b~partner_no 
inner join CRMD_ORDERADM_I as c
on b~header = c~header
inner join CRMD_ACTIVITY_I as d
on c~guid = d~guid
inner join CRMD_PRODUCT_I as e
on c~guid = e~guid
inner join COMM_CATEGORY as f
on e~prod_hierarchy = f~category_id
inner join COMM_CATEGORYT as g
on f~category_guid = g~gategory_guid
where b~process_type_ix = 'Z011' and b~object_type = 'BUS2000126'.

regards,

madhumitha

[Former Member]

Hi madhumitha

no I get still the same error.

If I delete the last join than it works fine.

But I need the last table also.

Regards

Philipp

[Former Member]

f_{category_guid = *g}category_guid*

chk the spelling of category!

how abt this one?

DID IT WORK???

[ThomasZloch]

you're the man!

[christine_evans]

The original poster has gone strangely silent. Spelling mistakes in code can be so embarassing.