Select Tables

[Former Member]

Hi,

I have a table 'zpf0009' with fields

MANDT

COD_TIPO

COMPANY

LANGUAGE

DESCRIPTION

The table 'zpf0004' with fields

MANDT

COD_DEMANDA

COD_TIPO

COD_COMPLEX

COD_FASE

TIPO_DESENV

How I do to select all the data from the table 'ZPF0009 - COD_TIPO' that the table 'ZPF0004 - COD_TIPO' don´t have.

Best regards.

[Former Member]

I think its better to use the outer join .... with this you will get the entries from the first table even though u don't have the corresponding matching entries in the second table.

[Former Member]

You could use a sub-query.

Rob

[naimesh_patel]

You can try like this:

data: it_09 like zpf0009 occurs 0 with header line,
     it_04 like zpf0004 occurs 0 wiht header line.

RANGES: R_COD_TIPO FOR ZPF0004-COD_TIPO.

select * from zpf0004 into table it_04.

LOOP AT ZPF00004.
  R_COD_TIPO-SIGN = 'E'.
  R_COD_TIPO-OPTIONS = 'EQ'.
  R_COD_TIPO-LOW = ZPF0004-COD_TIPO.
  APPEND R_COD_TIPO.
ENDLOOP.

SELECT * FROM ZPF0009 INTO TABLE IT_09
WHERE COD_TIPO IN R_COD_TIPO.

Regards,

Naimesh Patel

[Former Member]

Hi,

You could do it as Naimesh reply, however just one little fix:

data: it_09 like zpf0009 occurs 0 with header line,
     it_04 like zpf0004 occurs 0 wiht header line.
 
RANGES: R_COD_TIPO FOR ZPF0004-COD_TIPO.
 
select * from zpf0004 into table it_04.
 
LOOP AT ZPF00004.
  R_COD_TIPO-SIGN = 'E'.
  R_COD_TIPO-OPTIONS = 'EQ'.
  R_COD_TIPO-LOW = ZPF0004-COD_TIPO.
  APPEND R_COD_TIPO.
ENDLOOP.
 
SELECT * FROM ZPF0009 INTO TABLE IT_09
WHERE COD_TIPO 

<b>NOT</b>

 IN R_COD_TIPO. 

Hope it helps.

Regards,

Gilberto Li

[naimesh_patel]

I used the

R_COD_TIPO-SIGN = 'E'.  " << which excludes the data

So, if you use the NOT than this will affect as 'I' (Include) which contradicts the requirment.

Hope you got the point

Regards,

Naimesh Patel

[Former Member]

Hi Gilberto,

The program loads the table 'R_COD_TIPO' correctly, but the 'NOT IN' is not working, the loop is always bringing all the values of the table it_zpf0009.

regards.

[Former Member]

You should use LEFT JOIN rather INNER JOIN.

The Left table at ON condition in the select query will retrieve all the values irrespective condition.

Reward if useful.

Pra

[Former Member]

U can use Joins

[Former Member]

No, I do anything that Gilberto posted

regards

[Former Member]

loop at ti_zpf0009.

clear: v_tabix.

clear: sy-tabix.

read table ti_zpf0004

with key cod_tipo = ti_zpf0009-cod_tipo binary search.

if sy-subrc ne 0.

append ti_zpf0009 to ti_zpf0009_recebe.

endif.

endloop.