topic Re: Using select statement with Inner Join in Application Development and Automation Discussions

Using select statement with Inner Join

Former Member — Wed, 17 Jul 2013 04:03:55 GMT

Hi,

Please find my below peice of code,

DATA: BEGIN OF wa,

smtp_addr TYPE adr6-smtp_addr,

bname TYPE usr21-bname,

END OF wa.

DATA: ittt like SORTED TABLE OF wa WITH UNIQUE KEY bname.

SELECT * from zbc_user_lic INTO TABLE gi_zbc FOR ALL ENTRIES IN gi_funmod

WHERE bname EQ gi_funmod-bname AND sid = 'BF3' AND internet NE ' '.

SELECT c~SMTP_ADDR p~bname

INTO TABLE ittt

FROM adr6 AS c INNER JOIN usr21 AS p

ON p~ADDRNUMBER = c~ADDRNUMBER

AND p~PERSNUMBER = c~PERSNUMBER FOR ALL ENTRIES IN gi_zbc WHERE p~bname EQ gi_zbc-bname.

Is the above inner join statement correct? Since i debugger i dont find the values at gi_zbc-bname.

Please advice.

Regards,

Hajeera.

Re: Using select statement with Inner Join

FredericGirod — Wed, 17 Jul 2013 06:03:18 GMT

Hi Hajeera,

first ! protect your for all entries, if the internal table is empty you will make a full scan in the tables ..

so .. are you sure you have something in your internal table ? correspond to the database table ?

regards

Fred

Re: Using select statement with Inner Join

Former Member — Wed, 17 Jul 2013 06:55:16 GMT

Hi,

Yes i am very sure that i have values in the internal table gi_zbc.

Regards,

Hajeera.

Re: Using select statement with Inner Join

Former Member — Wed, 17 Jul 2013 11:10:02 GMT

Hi,

Try to debug your code by checking where the condition is getting failed. And compare the data of bname in the internal table with the database table.

Re: Using select statement with Inner Join

Former Member — Wed, 17 Jul 2013 11:34:51 GMT

Hi Hajeera,

Above Join works well if the internal table gi_zbc is filled with bname. you can try the following:

1. Use if not gi_zbc[] is initial before join to check if internal table is filled

2. if it is filled with bname, check if corresponding records available in database tables (usr21 and adr6) for bname values.

Thanks,

Shanmugapriya M

Re: Using select statement with Inner Join

Former Member — Wed, 17 Jul 2013 12:39:36 GMT

Hey,

Just check if there is a record in ADR6 table, where the User Name record from the Z table has a Address Number and Person Number record in ADR6 table.

The inner join otherwise is correct.

Your logic is similar to the one below:

IF gt_vbeln[] IS NOT INITIAL.

SELECT b~vbeln b~posnr a~knumv b~matnr b~matkl

INTO TABLE gt_invoice

FROM vbrk AS a INNER JOIN vbrp AS b

ON b~vbeln = a~vbeln

FOR ALL ENTRIES IN gt_vbeln

WHERE a~vbeln = gt_vbeln-vbeln.

ENDIF.

Thanks,

Adithi

Re: Using select statement with Inner Join

prkash_s — Wed, 17 Jul 2013 12:41:11 GMT

hi ,

Please check whether all the internal table are filled .

You should Check that with following Condition.

example :-

if not gi_funmod [] is initial .

SELECT * from zbc_user_lic INTO TABLE gi_zbc FOR ALL ENTRIES IN gi_funmod

WHERE bname EQ gi_funmod-bname AND sid = 'BF3' AND internet NE ' '.

if not gi_zbc [] is initial.

SELECT c~SMTP_ADDR p~bname

INTO TABLE ittt

FROM adr6 AS c INNER JOIN usr21 AS p

ON p~ADDRNUMBER = c~ADDRNUMBER

AND p~PERSNUMBER = c~PERSNUMBER FOR ALL ENTRIES IN gi_zbc WHERE p~bname EQ gi_zbc-bname.

endif.

endif

Re: Using select statement with Inner Join

VXLozano — Wed, 17 Jul 2013 13:31:47 GMT

Let's suppose the first SELECT returns NO rows. Then, as has been stated, the second one will read the whole table ignoring the WHERE clause.

Ok, we have that, we don't need to ask the OP to check it AGAIN.

The problem is not related with the internal table, because if it was empty, the second SELECT should return rows.

Then, the correct answer is Aditi B's one: check if there are rows in both tables that are susceptible to be found with the JOIN condition.

From here, the SQL seems pretty fine. If you use SE16 to check the contents of both tables and find a pair of rows that fit your criteria, return here and we will talk about data type internal conversions, the importance of leading zeroes, and so.

Until then, please, let's try to prevent point-hunting here.