How to write a query to display multiple tables with multiple condition?

[vinayad]

Here I am merging 2 tables with multiple conditions but the condition is not satisfying. Kindly do needful.

SELECT *
APPENDING corresponding fields of table it_tab
from
marc AS a inner join eina AS b
on a~matnr = b~matnr

where ( werks = p_werks and eina~matnr in s_matnr )
or ( werks = p_werks and eina~infnr in s_infnr )
or ( werks = p_werks and b~lifnr in s_lifnr ) .

[Tomas_Buryanek]

Can you please describe where do you have problem?

And by just looking od SELECT statement - why do you use OR in conditions? Don't you want it with AND?

[vinayad]

Tomas,

The ONLY way I can get an output is when I put in Material, Info Record, and Vendor. The report should give an output for any ONE of those choices.

when I put AND condition only INFNR condition is satisfying.

[raymond_giuseppi]

Read again your where clause, if any one of the 3 select-options is initial then every record from MARC for the selected plant WERKS would be selected...

Can you express your actual requirement/criteria, clearly, in a logical syntax, in plain english.

[vinayad]

Hi Raymond,

Yes you’re right it’s displaying all the records, Here I am merging MARC and EINA table and the condition is if any one (MATNR, LIFNR, INFNR) present in selection screen. It should display the regarding that record.

Thanks for your help in advance,

[raymond_giuseppi]

So your code (rewritten)

SELECT * APPENDING CORRESPONDING FIELDS OF TABLE it_tab
  FROM marc
  JOIN eina
  ON marc~matnr = eina~matnr
  WHERE marc~werks = p_werks
    AND ( eina~matnr IN s_matnr
       OR eina~infnr IN s_infnr
       OR eina~lifnr IN s_lifnr ) .

doesn't provide expected results, is this the case when one of the select-options is initial, in this case either break the selection into multiple select (and remove duplicates)

IF s_matnr[] IS NOT INITIAL.
  SELECT * APPENDING CORRESPONDING FIELDS OF TABLE it_tab
  FROM marc
  JOIN eina
  ON marc~matnr = eina~matnr
  WHERE marc~werks = p_werks
    AND eina~matnr IN s_matnr.
ENDIF.

or add a exclusion criteria record in the empty select-options that is always true (e.g. E/CP/*)

DATA: r_matnr TYPE RANGE OF eina-matnr.
IF s_matnr[] IS INITIAL.
  REFRESH r_matnr.  
  s_matnr-sign = 'E'.
  s_matnr-options = 'CP'.
  s_matnr-low = '*'.
  APPEND s_matnr TO r_matnr.
  CLEAR s_matnr.
ELSE.
  r_matnr = s_matnr[].
ENDIF
" The use the r_range in the select where clause...

But be aware that usually such selection-screen are handled only with AND criteria, so every criteria is used in the where clause. Also most, if not every and all, users became used to this behavour. This is also true for Abaper who would maintain your program in the future.

SELECT * APPENDING CORRESPONDING FIELDS OF TABLE it_tab
  FROM marc
  JOIN eina
  ON marc~matnr = eina~matnr
  WHERE marc~werks = p_werks
    AND eina~matnr IN s_matnr
    AND eina~infnr IN s_infnr
    AND eina~lifnr IN s_lifnr.

[vinayad]

Hi Raymond,

Need to join 2 different tables and in Selection screen I have 3 select options, if user enter value any one or two of these 3 select options it should display according to selection criteria OR if user enters all those 3 field it should work.

Currently I modified the code to as below, but still I am failing to execute LIFNR and INFNR condition(i.e., 2nd and 3rd condition).

IF ( S_MATNR IS NOT INITIAL ) AND ( S_LIFNR IS INITIAL ) AND ( S_INFNR IS INITIAL ) .
SELECT * APPENDING corresponding fields of table IT_TAB
FROM MARC INTO TABLE
WHERE MATNR IN S_MATNR AND WERKS = P_WERKS .

ELSEIF ( S_MATNR IS INITIAL ) AND ( S_LIFNR IS NOT INITIAL ) AND ( S_INFNR IS INITIAL ) .
select * APPENDING corresponding fields of table it_tab
from marc INNER JOIN eina 
on marc~matnr = eina~matnr 
where werks = p_werks AND eina~lifnr = S_lifnr.

ELSEIF ( S_MATNR IS INITIAL ) AND ( S_LIFNR IS INITIAL ) AND ( S_INFNR IS NOT INITIAL ) .
select * APPENDING corresponding fields of table it_tab
from marc INNER JOIN eina 
on marc~matnr = eina~matnr 
where werks = p_werks AND eina~infnr = S_INFNR.

ELSE.
select * APPENDING corresponding fields of table it_tab
from marc INNER JOIN eina 
on marc~matnr = eina~matnr
where marc~werks = p_werks AND eina~infnr = S_infnr AND eina~lifnr = S_lifnr AND marc~matnr in s_matnr.

[raymond_giuseppi]

If you don't use the trick to 'deter' impact of initial select-options (exclude every value E/CP/*) then you should have to explicitly handle EIGHT cases?

1. Build 3 ranges using my s_matnr/r_matnr sample
2. Use those ranges in the select where clause

[loyd_enochs3]

I'm wondering why building a view isn't a better answer? Then the SQL SELECT is very simple.

It seems from the join condition that you are assuming the EINA record will be there.

Unless what you are really looking for is a listing of mismatches (matnr with no infnr, infnr with no lifnr). In that case you want to code an OUTER JOIN.

[DoanManhQuynh]

you should build a dynamic sql, something like:

DATA(condition) = |MARC~WERKS = P_WERKS | &&

 |{ COND #( WHEN S_MATNR IS NOT INITIAL THEN 'AND EINA~MATNR IN S_MATNR' ELSE space ) } | &&

 |{ COND #( WHEN S_LIFNR IS NOT INITIAL THEN 'AND EINA~LIFNR IN S_LIFNR' ELSE space ) } | &&

 |{ COND #( WHEN S_INFNR IS NOT INITIAL THEN 'AND EINA~INFNR IN S_INFNR' ELSE space ) } |.

and please explain more about what the meaning of 'failing to execute LIFNR and INFNR condition', or 'the condition is not satisfying'. did you check the proper records exists or not yet?

[vinayad]

Quynh,

Yes condition was not satisfying, and records are exist.