joins

[Former Member]

SELECT b~matnr INTO TABLE i_picps

FROM picps as b

JOIN picps as a

ON b_{pickey = a}pickey

WHERE a~matnr = wa_pmd2-fff_lead

AND b~matnr NE wa_pmd2-fff_lead.

The proposed query will retrieve the non-lead parts from PICPS for the given lead part.

can anyone help me in understanding the above SELECT query.

[Former Member]

SELECT b~matnr INTO TABLE i_picps

FROM picps as b

JOIN picps as a

ON b_{pickey = a}pickey

WHERE a~matnr = wa_pmd2-fff_lead

AND b~matnr NE wa_pmd2-fff_lead.

The proposed query will retrieve the non-lead parts from PICPS for the given lead part.

can anyone help me in understanding the above SELECT query.

[Former Member]

can me tell me area of the fun., bcz this table is not available in our system

[Former Member]

This is joining a table on itself - not a common thing to do, but it is permitted. Since the WHERE can never be true, nothing will be selected.

It seems to be a very awkward attempt to set SY-SUBRC = '4'.

Rob

[Former Member]

Hi Rob,

why do you think that the result set is empty? If you assume that pickey is not the primary key of the table, the result set could be not empty.

If we assume that table picps has the columns <i>client</i>, <i>pk</i>, <i>pickey</i> and <i>matnr</i> and it contains the following rows (primary key: client, pk):

client pk    pickey  matnr
------ ----- ------ ------
001    Key1  pickey1 01234
001    Key2  pickey1 12345
001    Key3  pickey2 67890
001    Key4  pickey2 67890
001    Key5  pickey3 23456

There result set would be for a lead part = 01234:

matnr
------
12345

@Syed:

A self join is not that rare, it could be used if entities which are stored in a table has a relation with other entities of the same type stored in the same table. What Rob assumed is that prickey is the primary key of your table (which may be is true), then the predicates of your query lead to an empty set. This is because the join picks only rows from the table with the same value in the column pickey. If this is the primary key, the joined set consists of rows which have all columns as doubles. That mean a_{matnr is always equal to b}matnr, which means your where clause could not be true.

Best regards

Ralph

[Former Member]

Good point - I am assuming it's the primary key.

Rob

[Former Member]

Thanks Ralph for your explanation

[Former Member]

@Ralph,

good illustration

But is the result really always 01234

I think it is not clear, it can also be 12345

Therefore the fieldlist is a bit weird. It should probably be key1 matnr key2 matr2.

Siegfried

[Former Member]

Hi Siegfried,

the result is '12345' if the content of wa_pmd2-fff_lead = '01234'. May be I was not that clear in the first posting. The result is the result of the above query. To visualize it better you're probably right. For the Query:

SELECT a~prikey, a~matnr, b~prikey, b~matnr
  FROM picps AS b
  JOIN picps AS a
    ON b~pickey = a~pickey
 WHERE a~matnr = wa_pmd2-fff_lead
   AND b~matnr NE wa_pmd2-fff_lead.

The result for wa_pmd2-fff_lead = '01234' would be:

a~prikey a~matnr b~prikey b~matnr
-------- ------- -------- ------- 
pickey1  01234   pickey1  12345

Therefore you'll get '12345' as the result of the original query.

Best regards

Ralph