topic condition as range in Application Development and Automation Discussions

condition as range

Former Member — Thu, 06 Sep 2007 16:41:11 GMT

I need the partner information for parvw as WE & ZM. So if vbeln = 123 has 2 rows in VBAP, one for WE and the other for ZM, both should be included in itab. Currently only WE is been added. How to change this code. Thanks

    SELECT vbpa~vbeln vbpa~parvw vbpa~pernr vbpa~kunnr adrc~country
      FROM vbpa
      INNER JOIN adrc
      ON adrc~addrnumber = vbpa~adrnr
      INTO TABLE i_vbpa
      FOR ALL ENTRIES IN i_vbak
      WHERE vbpa~vbeln = i_vbak-vbeln
      AND   vbpa~kunnr IN s_kunnr
      AND   vbpa~pernr IN s_pernr
      AND   adrc~country IN s_cntry
      AND ( vbpa~parvw = 'WE' OR vbpa~parvw = 'ZM' ).

Re: condition as range

Former Member — Thu, 06 Sep 2007 16:46:56 GMT

Hi,

Your code seems to be ok, if you want to get the details for both WE and ZM.

Or else you can do as below : Declare a range as below and use the code in the initailzation event.

data : gr_parvw for vbpa-parvw..

Initialization.

gr_parvw-option = 'EQ'.

gr_parvw-sign = 'I'.

gr_parvw-low = 'WE'.

gr_parvw-high = 'WE'.

append gr_parvw.

gr_parvw-option = 'EQ'.

gr_parvw-sign = 'I'.

gr_parvw-low = 'ZM'.

gr_parvw-high = 'ZM'.

append gr_parvw.

And use this range in your select statement as below :

SELECT vbpa_{vbeln vbpa}parvw vbpa_{pernr vbpa}kunnr adrc~country

FROM vbpa

INNER JOIN adrc

ON adrc_{addrnumber = vbpa}adrnr

INTO TABLE i_vbpa

FOR ALL ENTRIES IN i_vbak

WHERE vbpa~vbeln = i_vbak-vbeln

AND vbpa~kunnr IN s_kunnr

AND vbpa~pernr IN s_pernr

AND adrc~country IN s_cntry

AND vbpa~parvw in gr_parvw.

Thanks,

Sri.

Re: condition as range

Former Member — Thu, 06 Sep 2007 16:47:47 GMT

I would put this into a range table that excludes both these values. It would be easier than trying to apply NOT logic here.

Since you are testing for equality on the document number, your SELECT will probably be able to use the index, but I'm not sure about that. You might want to keep this in mind during testing.

Rob

Re: condition as range

ferry_lianto — Thu, 06 Sep 2007 16:49:26 GMT

Hi,

Your code looks fine assuming meet the condition check.

Please try this.


RANGES: r_parvw for vbpa-parw.

r_parvw-sign = 'I'.
r_parvw-option = 'EQ'.
r_parvw-low = 'WE'.
append r_parvw.

r_parvw-sign = 'I'.
r_parvw-option = 'EQ'.
r_parvw-low = 'ZM'.
append r_parvw.


SELECT vbpa~vbeln vbpa~parvw vbpa~pernr vbpa~kunnr adrc~country
      FROM vbpa
      INNER JOIN adrc
      ON adrc~addrnumber = vbpa~adrnr
      INTO TABLE i_vbpa
      FOR ALL ENTRIES IN i_vbak
      WHERE vbpa~vbeln = i_vbak-vbeln
      AND   vbpa~kunnr IN s_kunnr
      AND   vbpa~pernr IN s_pernr
      AND   adrc~country IN s_cntry
      AND   vbpa~parvw IN r_parvw.

Regards,

Ferry Lianto

Re: condition as range

Former Member — Thu, 06 Sep 2007 16:49:59 GMT

I tried this which is the same as range tbl. I only get values for WE and not ZM

    SELECT vbpa~vbeln vbpa~parvw vbpa~pernr vbpa~kunnr adrc~country
      FROM vbpa
      INNER JOIN adrc
      ON adrc~addrnumber = vbpa~adrnr
      INTO TABLE i_vbpa
      FOR ALL ENTRIES IN i_vbak
      WHERE vbpa~vbeln = i_vbak-vbeln
      AND   vbpa~kunnr IN s_kunnr
      AND   vbpa~pernr IN s_pernr
      AND   adrc~country IN s_cntry
      AND   vbpa~parvw IN ('WE','ZM').

Re: condition as range

former_member194669 — Thu, 06 Sep 2007 16:50:23 GMT

Hi,

There is conversion exit behind domain PARVW please check the value you are passing in the select statement.

CONVERSION_EXIT_PARVW_INPUT

CONVERSION_EXIT_PARVW_OUTPUT

a®

Re: condition as range

Former Member — Thu, 06 Sep 2007 16:52:18 GMT

Hi,

Try using range as above code by me. I faced this problem once.

thanks,

Sriram Ponna.

Re: condition as range

Former Member — Thu, 06 Sep 2007 17:10:01 GMT

SELECT vbpa~vbeln vbpa~parvw vbpa~pernr vbpa~kunnr adrc~country
      FROM vbpa
      INNER JOIN adrc
      ON adrc~addrnumber = vbpa~adrnr
      INTO TABLE i_vbpa
      FOR ALL ENTRIES IN i_vbak
      WHERE vbpa~vbeln = i_vbak-vbeln
      AND   adrc~country IN s_cntry
      AND   vbpa~parvw IN r_parvw.

The inner join on ADRC is causing me the grief. I am looking for partner function employee responsbile (ZM) and ship to (WE). For ZM it does not have a adrnr. How to resolve this. I tried OUTER JOIN but then I cant include the adrc~country condition

i need to reduce access to VBPA to minimum as it already has over 16 ml records. So I would prefer to execute the query in one SELECT rather than two.

Message was edited by:

Megan Flores

Re: condition as range

former_member194669 — Thu, 06 Sep 2007 17:29:57 GMT

Hi,

Make it this way. due to you don't have proper key to join.


SELECT vbeln parvw pernr kunnr 
      FROM vbpa
      INTO TABLE i_vbpa
      FOR ALL ENTRIES IN i_vbak
      WHERE vbpa~vbeln = i_vbak-vbeln
      AND   vbpa~parvw IN r_parvw.

i_vbpa_1[] = i_vbpa[].
sort i_vbpa_1 by adrnr.
delete adjacent duplicates comparing adrnr.
if not i_vbpa_1[] is initial.
  SELECT addrnumber country
      FROM adrc
      INTO TABLE i_adrc
      FOR ALL ENTRIES IN i_vbpa-1
      WHERE addrnumber eq i_vbpa_1-adrnr
      AND   adrc~country IN s_cntry.
endif.

loop at i_vbpa.
  read i_adrc  ..........
  modify country <<<<<<<
endloop.

a®

Re: condition as range

Former Member — Thu, 06 Sep 2007 18:13:40 GMT

Megan - maybe I'm missing something, but is this what you are looking for:

tables vbpa.

ranges: r_parvw for vbpa-parvw.

MOVE 'EQ' TO r_parvw-option.
MOVE 'E'  TO r_parvw-sign.         "<================== note

MOVE 'WE' TO r_parvw-low.
append r_parvw.

MOVE 'ZM' TO r_parvw-low.
append r_parvw.

SELECT vbpa~vbeln vbpa~parvw vbpa~pernr vbpa~kunnr adrc~country
  FROM vbpa
  INNER JOIN adrc
  ON adrc~addrnumber = vbpa~adrnr
  INTO TABLE i_vbpa
  FOR ALL ENTRIES IN i_vbak
  WHERE vbpa~vbeln = i_vbak-vbeln
  AND   vbpa~kunnr IN s_kunnr
  AND   vbpa~pernr IN s_pernr
  AND   adrc~country IN s_cntry
  AND   vbpa~parvw in r_parv.             "<=========== note

Rob