topic Re: Subquery with internal table in Application Development and Automation Discussions

Subquery with internal table

Former Member — Mon, 12 Oct 2009 09:50:36 GMT

Dear Experts,

I need to write a subquery between a database table and an input table i tried to write something like this


Select * from VBRP into table ZBAPI_output_VBRP
    where ERDAT between zBAPI_FROM_DATE and ZBAPI_TO_DATE
  and MATKL IN ( select MATKL from ZBAPI_INPUT_VBRP )
  and PRCTR in ( select PRCTR from zBAPI_INPUT_VBRP where PRCTR is not initial )
  and VKORG_AUFT in ( select VKORG_AUFT from zBAPI_INPUT_VBRP ).

but it is not working ZBAPI_INPUT_VBRP and ZBAPI_output_VBRP are input and output tables can someone guide me what code i should write to make it write and i saw also in the forums that the performance of subqueries are slow so if you can tell me how i can do it in more efficient way?

thanks

Re: Subquery with internal table

Former Member — Mon, 12 Oct 2009 10:03:34 GMT

Hi,

instead of this subquery, its better to define some range variable in selection screen.

populate them as per your requirement.

After that use those ranges into your main query.

it will improve the performance of your query as well as give you the right output.

Example:

Range:

RANGES R_PROFCENT FOR GLPCA-RPRCTR.

Populating Profit Center:

SELECT PRCTR INTO R_PROFCENT-LOW

FROM CEPC

WHERE KOKRS = '1000'.

R_PROFCENT-SIGN = 'I'.

R_PROFCENT-OPTION = 'EQ'.

APPEND R_PROFCENT.

Main Query:

SELECT * FROM GLPCA

WHERE RPRCTR = R_PROFCENT-LOW

AND RACCT IN GLACNO

AND BUDAT BETWEEN STARTDATE AND POSTDATE

AND BLART IN ('DB', 'DR', 'DZ').

Hope you understand. Like this you can declare other ranges:

Regds,

Anil

Re: Subquery with internal table

Former Member — Mon, 12 Oct 2009 10:26:34 GMT

thanks for your immediate reply i tested what you have given but the problem is that i am doing a BAPI and the input should come in a table (in my case zBAPI_INPUT_VBRP) that is why i cannot fill in the range also like this


SELECT PRCTR INTO R_PROFCENT-LOW
FROM zBAPI_INPUT_VBRP
WHERE PRCTR is NOT NULL .

all i want is to get the rows from database table (VBRP) that have the values appeared in the table (zBAPI_INPUT_VBRP)

as i mentioned in the subquery earlier

Re: Subquery with internal table

Former Member — Mon, 12 Oct 2009 10:46:56 GMT

Hi ,

Are you saying that zbpi_input_vbrp is an internal table ? If so, then you can use for all entries as follows :



Select * from VBRP into table ZBAPI_output_VBRP
for all entries in zbapi_input_vbrp
    where ERDAT between zBAPI_FROM_DATE and ZBAPI_TO_DATE
  and MATKL  = ZBAPI_INPUT_VBRP-maktl
  and PRCTR =  zBAPI_INPUT_VBRP-prctr
  and VKORG_AUFT = zBAPI_INPUT_VBRP -VKORG_AUFT.

Hope this helps.

Br,

Advait

Re: Subquery with internal table

Former Member — Mon, 12 Oct 2009 10:59:00 GMT

Dear Advait,

i tried for all entries before but the problem in all entries that it compare the values of row by row. and this is not what i wanted i want the query to get the rows from the database table that has values appear even once in any of this field

example:


input table
PRCTR         VKORG         MATKL
1000                         
                     COND          
                                         ALE_BAT
2000
                      EGHE

then the query should get any record that has PRCTR=(1000 or 2000) and VKORG= (COND or EGHE) and MATKL=(ALE_BAT)

I hope that i made myself clear

thanks

Edited by: ebrahime on Oct 12, 2009 12:59 PM

Re: Subquery with internal table

Former Member — Mon, 12 Oct 2009 11:48:40 GMT

Hello i tried this code it is not getting any data also


data: R_PROFCENT type RANGE OF VBRP-PRCTR,
      R_PROFCENT_line LIKE LINE OF R_PROFCENT.


Loop at zBAPI_INPUT_VBRP INTO wa1 WHERE PRCTR is NOT initial.
  R_PROFCENT_line-LOW = wa1-PRCTR.
R_PROFCENT_line-SIGN = 'I'.
R_PROFCENT_line-OPTION = 'EQ'.
APPEND R_PROFCENT_line to R_PROFCENT.
endloop.


SELECT * FROM VBRP
  INTO ZBAPI_OUTPUT_VBRP
WHERE
     ERDAT between zBAPI_FROM_DATE and ZBAPI_TO_DATE
  and Vbrp~PRCTR in R_PROFCENT.

endselect.