topic Re: joins in Application Development and Automation Discussions

joins

Former Member — Wed, 12 Dec 2007 14:34:17 GMT

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.

Re: joins

Former Member — Wed, 12 Dec 2007 17:25:12 GMT

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

Re: joins

Former Member — Wed, 12 Dec 2007 18:12:38 GMT

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

Re: joins

Former Member — Wed, 12 Dec 2007 22:46:12 GMT

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 client, pk, pickey and matnr 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

Re: joins

Former Member — Thu, 13 Dec 2007 01:59:47 GMT

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

Rob

Re: joins

Former Member — Thu, 13 Dec 2007 07:45:38 GMT

@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

Re: joins

Former Member — Thu, 13 Dec 2007 10:03:35 GMT

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

Re: joins

Former Member — Fri, 14 Dec 2007 12:17:31 GMT

Thanks Ralph for your explanation