topic Open Sql Between in Application Development and Automation Discussions

Open Sql Between

Former Member — Wed, 20 Oct 2010 07:49:51 GMT

I am Having Ztable like this


MatgrpFrom	MatGrpTo	Department
1100  		1199		Process - Common
2000		2099		Mechanical-Old
2200		2299		Mechanical-New

I need to fetch a department field by passing matgrp Eg.

MATGRP = 2088 Means -> Mechanical-Old

MATGRP = 1150 Means -> Process - Common


select * from Test 
where 
2088  between  MKTL_F  and  MKTL_T

I tried


  Loop AT it_mara into wa_mara.      
    select Dept from ZMMT_MTGRP INTO lv_dept
      where 
      wa_mara-matkl  between  MKTL_F and MKTL_T .
      it_resb-matkl = wa_mara-matkl.
      it_resb-dept = lv_dept.
      MODIFY it_resb TRANSPORTING matkl dept where matnr = wa_mara-matnr.
    ENDSELECT.  
  ENDLOOP.

it is throwing


wa_mara-matkl is unknown

i want to implement in open SQL plz throw me light..

or please let me know best way to get this record.

Re: Open Sql Between

Former Member — Wed, 20 Oct 2010 08:03:50 GMT

Hi,

First, you fetch the Z Table Entries into an Internal Table.

TYPES: BEGIN OF ty_ztest,
        mat_from TYPE i,
        mat_to TYPE i,
        description TYPE string,
       END OF ty_ztest.

DATA: wa_test TYPE ty_ztest,
      it_test TYPE TABLE OF ty_ztest.

DATA: mat_grp TYPE i.

SELECT * FROM test INto TABLE it_test.

Then Loop at this internal table and pass the Material Group No. in the Where Condition -

LOOP at it_test INTO wa_test WHERE mat_grp BETWEEN wa_test-mat_from AND wa_test-mat_to.
  WRITE / wa_test-description.
ENDLOOP.

Hope this helps.

Regards,

Himanshu

Re: Open Sql Between

RaymondGiuseppi — Wed, 20 Oct 2010 08:26:45 GMT

In an Open SQL logical expression [BETWEEN|http://help.sap.com/abapdocu_70/en/ABENWHERE_LOGEXP_INTERVAL.htm] apply to a column on the left and two data objects for interval limits. so you cannot use this instruction here. use a

LOOP AT it_mara INTO wa_mara.
  SELECT dept FROM zmmt_mtgrp INTO lv_dept
    WHERE mktl_f LE wa_mara-matkl 
      AND mktl_t GE wa_mara-matkl.
    it_resb-matkl = wa_mara-matkl.
    it_resb-dept = lv_dept.
    MODIFY it_resb TRANSPORTING matkl dept WHERE matnr = wa_mara-matnr.
  ENDSELECT.
ENDLOOP.

Also, if you have huge volumn of data, you can first fill a (SORTed) internal table with a SELECT FOR ALL ENTRIES and then use a READ TABLE (BINARY SEARCH) statement.

Regards,

Raymond