topic Re: Select Tables in Application Development and Automation Discussions

Select Tables

Former Member — Thu, 13 Dec 2007 19:41:36 GMT

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.

Re: Select Tables

Former Member — Thu, 13 Dec 2007 19:45:02 GMT

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.

Re: Select Tables

Former Member — Thu, 13 Dec 2007 19:48:56 GMT

You could use a sub-query.

Rob

Re: Select Tables

naimesh_patel — Thu, 13 Dec 2007 19:49:15 GMT

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

Re: Select Tables

Former Member — Thu, 13 Dec 2007 19:50:19 GMT

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

Re: Select Tables

Former Member — Thu, 13 Dec 2007 20:09:17 GMT

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

 IN R_COD_TIPO.

Hope it helps.

Regards,

Gilberto Li

Re: Select Tables

naimesh_patel — Thu, 13 Dec 2007 20:15:02 GMT

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

Re: Select Tables

Former Member — Fri, 14 Dec 2007 13:10:02 GMT

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.

Re: Select Tables

Former Member — Fri, 14 Dec 2007 13:21:38 GMT

U can use Joins

Re: Select Tables

Former Member — Fri, 14 Dec 2007 13:28:52 GMT

No, I do anything that Gilberto posted

regards

Re: Select Tables

Former Member — Wed, 26 Dec 2007 12:49:02 GMT

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.