Report - INNER JOIN Problem

[Former Member]

Hi,

In my report List of Block Customer's, INNER JOIN is not working..

Code:----

**************************************************************

SELECT DISTINCT M1_{KUNNR M1}KTOKD M1_{NAME1 M1}CASSD M1_{AUFSD M1}LIFSD M1~FAKSD INTO CORRESPONDING FIELDS OF TABLE ITAB

FROM KNA1 AS M1 INNER JOIN KNVV AS M2 ON M1_{KUNNR = M2}KUNNR

WHERE M1~KTOKD IN ('0001' , 'KTOKD')

AND M1~CASSD EQ 'X'

OR M1~AUFSD NE ''

OR M1~LIFSD NE ''

OR M1~FAKSD NE ''

OR (M2~AUFSD NE ''

OR M2~LIFSD NE ''

OR M2~FAKSD NE ''

OR M2~CASSD EQ 'X'.

**************************************************************

Here, condition on 0001 is not matching..

Plz tell me, what is the problem in it..

Thanks

Edited by: Prince Kumar on Oct 20, 2008 9:37 AM

[Former Member]

create one select-options fileds then you can use in the where condition .

Here you are using only one value at that you can parameter .

create either parameter or select-options for that field .

If it is not possible let me know .

And check the condition at se16 data is avaible or not .

[Former Member]

hi,

please use " FOR ALL ENTRIES " as it is better performance wise also rather then inner joins

and for syntax refer this code:

Exporting all flight data for a specified departure city. The relevant airlines and flight numbers are first put in an internal table entry_tab, which is evaluated in the WHERE condition of the subsquent SELECT statement.

PARAMETERS p_city TYPE spfli-cityfrom. 

TYPES: BEGIN OF entry_tab_type, 
         carrid TYPE spfli-carrid, 
         connid TYPE spfli-connid, 
       END OF entry_tab_type. 

DATA: entry_tab   TYPE TABLE OF entry_tab_type, 
      sflight_tab TYPE SORTED TABLE OF sflight 
                       WITH UNIQUE KEY carrid connid fldate. 

SELECT carrid connid 
       FROM spfli 
       INTO CORRESPONDING FIELDS OF TABLE entry_tab 
       WHERE cityfrom = p_city. 

SELECT carrid connid fldate 
       FROM sflight 
       INTO CORRESPONDING FIELDS OF TABLE sflight_tab 
       FOR ALL ENTRIES IN entry_tab 
       WHERE carrid = entry_tab-carrid AND 
             connid = entry_tab-connid.

thanks and regards

RAHUL

[Former Member]

hi

please check for the sysntax error

in given quiery

Regards

Prashant

[Former Member]

Hi you try this one.

SELECt distinct M1_{KUNNR M1}KTOKD M1~NAME1

M1_{CASSD M1}AUFSD M1~LIFSD

M1~FAKSD INTO CORRESPONDING FIELDS OF TABLE ITAB

FROM ( KNA1 AS M1 INNER JOIN KNVV AS M2 ON M1_{KUNNR = M2}KUNNR )

WHERE M1~KTOKD IN ('0001' , 'KTOKD')

AND ( M1~CASSD EQ 'X'

OR M1~AUFSD NE ''

OR M1~LIFSD NE ''

OR M1~FAKSD NE ''

OR M2~AUFSD NE ''

OR M2~LIFSD NE ''

OR M2~FAKSD NE ''

OR M2~CASSD EQ 'X').

this will work.

[Former Member]

Hi,

Are you sure KTOKD is creating the problem?

KTOKD is the accounting group and it is of 4 char.

So the value yoy are giving is right provided it is there in the table you are using i.e M1.

Please check if it is there in M1. Please revert back.

Thanks

Natasha Garg