topic Re: problem with inner join in Application Development and Automation Discussions

problem with inner join

Former Member — Wed, 14 Apr 2010 13:58:19 GMT

select
        mara~matnr
        mara~mtart
        mara~matkl
        mara~bmatn
        mara~bismt
        mara~meins
        mara~mfrpn
        mara~mfrnr
        makt~maktx
        lfa1~lifnr
        lfa1~name1
   into table lt_output
        from mara
   inner join makt on mara~matnr = makt~matnr
   inner join lfa1 on mara~mfrnr = lfa1~lifnr
   where
        mara~matnr in s_matnr and
        mtart in s_mtart and
        matkl in s_matkl and
        bmatn in s_bmatn and
        bismt in s_bismt and
        meins in s_meins and
        mfrpn in s_mfrpn and
        mfrnr in s_mfrnr.

when I inner join these three tables am getting 34 records in the output table but when I enter material number matnr and check it, no records was showing up....I think inner join was failed can any one help me

Edited by: Thomas Zloch on Apr 14, 2010 4:21 PM - tags added

Re: problem with inner join

Former Member — Wed, 14 Apr 2010 17:10:52 GMT

Did you intend to omit the table names in this part of WHERE?

mtart in s_mtart and
        matkl in s_matkl and
        bmatn in s_bmatn and
        bismt in s_bismt and
        meins in s_meins and
        mfrpn in s_mfrpn and
        mfrnr in s_mfrnr.

I've never tried without supplying the mara~, etc.

Trying deconstructing...see if you get values in SE11 from MARA only , with the where clause (that applies to MARA), then LFA1, etc.

Re: problem with inner join

Former Member — Thu, 15 Apr 2010 01:44:30 GMT


> select
>         mara~matnr
>         mara~mtart
>         mara~matkl
>         mara~bmatn
>         mara~bismt
>         mara~meins
>         mara~mfrpn
>         mara~mfrnr
>         makt~maktx
>         lfa1~lifnr
>         lfa1~name1
>    into table lt_output
>         from mara
>    inner join makt on mara~matnr = makt~matnr
>    inner join lfa1 on mara~mfrnr = lfa1~lifnr
>    where
>         mara~matnr in s_matnr and
>         mtart in s_mtart and
>         matkl in s_matkl and
>         bmatn in s_bmatn and
>         bismt in s_bismt and
>         meins in s_meins and
>         mfrpn in s_mfrpn and
>         mfrnr in s_mfrnr.
> when I inner join these three tables am getting 34 records in the output table but when I enter material number matnr and check it, no records was showing up....I think inner join was failed can any one help me
> 
> Edited by: Thomas Zloch on Apr 14, 2010 4:21 PM -  tags added

Hi,

Please change your WHERE condition like this,(Give the Table Alias along with field name )

where

> mara~matnr in s_matnr and

> mara~mtart in s_mtart and

> mara~matkl in s_matkl and

> mara~bmatn in s_bmatn and

> mara~bismt in s_bismt and

> mara~meins in s_meins and

> mara~mfrpn in s_mfrpn and

> mara~mfrnr in s_mfrn

Re: problem with inner join

Former Member — Thu, 15 Apr 2010 04:46:35 GMT

I've tried with thata too same answer...if I give material no no record is printing

Re: problem with inner join

Former Member — Thu, 15 Apr 2010 05:10:10 GMT

You have to pass the logon Language Key to MAKT~SPRAS = sy-langu you can get correct output.

Regards

Nehruu

Re: problem with inner join

Former Member — Thu, 15 Apr 2010 05:24:48 GMT

I think you are not giving valid material so u r not getting the result.

Try with the Valid material range becaus eu r using all AND condtion .

Fot that particualer material what u have given in where condtions it should also satisfy.

Check wheather in where condtion it is AND or OR.

Ask for the valid Test data to functional consultant.

Regards,

Pravin

Re: problem with inner join

Former Member — Thu, 15 Apr 2010 05:25:08 GMT

Let's first of all get rid of the myth that the table names are required in the join when specifying field names: This is only necessary, when the given field/column appears in more than one of the join's database tables. In certain cases it is still good practice though, because if for some strange reason one of the tables would be changed and now you'd actually have duplicate field names across tables, the SQL statement would no longer be syntactically correct.

Now to your problem, which I'm not sure I understand, so let me rephrase. When you run the join without any restrictions you get 34 records, but when entering a condition on material number you get 0 records. If that's the case then most likely your problem is that you used the wrong reference for defining your select-option s_matnr, which has to be of type matnr (or similar) to ensure that the correct conversion exit matn1 gets applied.

Otherwise if your system is configured to store material numbers with leading zeros, you won't get any match if you search for material number 1, when you actually should have looked for 000000000001. By using the right type you ensure that this conversion happens as it should.

You can also quickly check this in SE16 or SE16N, just make sure to switch off the conversion exits in the settings for the transaction (that way you see the non-numeric data as it is stored on the database rather than how it's displayed for the user).

And as Nehruu mentioned, either select the language or add a condition on language. Otherwise you'll end up with duplicate records if your system stores material descriptions in more than one language.

Re: problem with inner join

Former Member — Thu, 15 Apr 2010 12:20:24 GMT

This is the complete code I've used to print this report....I tried tomake changes suggested by you guys but the same reply