Using select statement with Inner Join

[Former Member]

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.

[FredericGirod]

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

[Former Member]

Hi,

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

Regards,

Hajeera.

[Former Member]

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.

[Former Member]

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

[Former Member]

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

[prkash_s]

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

[VXLozano]

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.