Select Query in FM

[sastry_gunturi]

I am working on a function module.

The input to the function is more one entry.... (zz1, zz2, zz3)...

Based on the input i have to write a select query

 select f1
           f2
           from DBtable  into table itab2
           for all entries in itab2 where
           f1 eq itab1-f1 and
           f3 ge sy-datum.

here f 1 is zz1,zz2,zz3.

However if the user enters z* in the input then also this select query in the FM should work.How to achieve this..Thank you.

[Former Member]

Use LIKE. as below..

PARAMETERS srch_str(20) TYPE c. 
CONCATENATE '%' srch_str '%' INTO srch_str. 
DATA text_tab TYPE TABLE OF doktl. 
SELECT * 
       FROM doktl 
       INTO TABLE text_tab 
       WHERE doktext LIKE srch_str.

The use of the wildcard characters "_" and "%" corresponds to the standard of SQL. In the rest of ABAP, the wildcard characters "+" and "*" are used in similar logical expressions, in particular when selection tables are used

Cheers

A

[sastry_gunturi]

Aman,

This is logic is in FM not a report. I am not sure if we can use parameters in FM

[Former Member]

you should use like this in the FM. Its simple to copy the exmaple code from report to FM,

select f1
           f2
           from DBtable  into table itab2
           for all entries in itab2 where
           f1 <b>LIKE</b> itab1-f1 and
           f3 ge sy-datum.

A.

[sastry_gunturi]

Aman your logic is returning a error

[Former Member]

i code this in my system and works fine..

DATA: lt TYPE TABLE OF pa0001.
SELECT * FROM pa0001 INTO TABLE lt WHERE pernr  like '%9%'.
break-point.

it returns me all personnel number which has digit 9 in it.

A

[sastry_gunturi]

select f1
           f2
           from DBtable  into table itab2
           for all entries in itab1 where
           f1 <b>LIKE</b> itab1-f1 and
           f3 ge sy-datum.

in the fm i am getting the error

The addition "FOR ALL ENTRIES IN itab" cannot be used in the WHERE condition BETWEEN, LIKE and IN.

[Former Member]

remember. the pattern used in the <b>LIKE</b> statement can only be C type.

amandeep

[Former Member]

if thats the case you will have to loop over indivisual items..

i can't think of any other statement...

[Former Member]

before doing the select. don;t forget to put the statement..

REPLACE ALL OCCURRENCES OF '*' in itab1-f1 WITH '%'.

A

[Former Member]

FUNCTION ZSIGN.
*"----------------------------------------------------------------------
*"*"Local Interface:
*"  IMPORTING
*"     VALUE(PERNR) TYPE  CHAR8 OPTIONAL
*"  TABLES
*"      PA0000 STRUCTURE  PA0000
*"----------------------------------------------------------------------
REPLACE ALL OCCURRENCES OF '*' in pernr WITH '%'.

SELECT * from pa0000 into TABLE pa0000 where pernr like pernr.


ENDFUNCTION.

this works fine.. i am not sure why you are getting erros...

A

[sastry_gunturi]

i am using tables in the fm to accept values from the user

[Former Member]

Hi,

If you are using a table to accept values from the user, then the code given in my previous reply will work. You have to replace

1. Internal table it_itab by your table

2. Work area x_itab by your work area

3. Fields VBELN, ERDAT, ERNAM in CASE...ENDCASE with possible values that you can get in the table.

4. Change conditions accordingly for each field.

Reward points if the answer is helpful.

Regards,

Mukul

[Former Member]

SEE THIS NOW..THIS HAS TABLE

FUNCTION ZSIGN.
*"----------------------------------------------------------------------
*"*"Local Interface:
*"  TABLES
*"      PA0000 STRUCTURE  PA0000
*"      LT TYPE  RSELOPTION
*"----------------------------------------------------------------------

DATA: LV TYPE RSDSSELOPT.

LOOP AT LT INTO LV.
REPLACE ALL OCCURRENCES OF '*' in LV WITH '%'.
SELECT * from pa0000 into TABLE pa0000 where pernr like  LV-LOW.
APPEND PA0000.
ENDLOOP.



ENDFUNCTION.

[Former Member]

Hi,

zz1, zz2, zz3 etc. are individual fields or they belong to a table ???

Regards,

Mukul

[sastry_gunturi]

zz1, zz2,zz3 are values of a single field in Database field

[Former Member]

Hi Karthik,

did u impliment my code. this should be perfectly fine..

Amandeep

[Former Member]

Hi Karthik,

I hope following code will solve your problem. Here, it_itab should be your table declared in the interface of your your function module. Use should pass zz1,zz2 to it_itab. Here, I have taken only three fields and written the code. You will have to tweak it a bit to suit your scenario. So, based on the entries in the it_itab, your fields that need to be selected from the database and condition to be evaluated will be generated dynamically.

TYPES : BEGIN OF x_itab,
          field TYPE string,
        END   OF x_itab.

DATA : it_itab TYPE TABLE OF x_itab,
       it_vbak TYPE TABLE OF vbak,
       x_itab TYPE x_itab,
       v_select TYPE string,
       v_condition TYPE string.

LOOP AT it_itab INTO x_itab.
  IF sy-tabix = 1.
    v_select = x_itab-field.
  ELSE.
    CONCATENATE v_select x_itab-field INTO v_select SEPARATED BY space.
  ENDIF.
  CASE x_itab-field.
    WHEN 'VBELN'.
      IF sy-tabix = 1.
        v_condition = 'VBELN = X_ITAB-FIELD'.
      ELSE.
    CONCATENATE v_condition 'AND VBELN = X_ITAB-FIELD' INTO v_condition
SEPARATED BY space.
      ENDIF.
    WHEN 'ERNAM'.
      IF sy-tabix = 1.
        v_condition = 'ERNAM = X_ITAB-FIELD'.
      ELSE.
    CONCATENATE v_condition 'AND ERNAM = X_ITAB-FIELD' INTO v_condition
SEPARATED BY space.
      ENDIF.
    WHEN 'ERDAT'.
      IF sy-tabix = 1.
        v_condition = 'ERDAT > 20070405'.
      ELSE.
        CONCATENATE v_condition 'AND ERDAT > 20070405' INTO v_condition
   SEPARATED BY space.
      ENDIF.
  ENDCASE.
ENDLOOP.

CONCATENATE v_condition '.' INTO v_condition.

SELECT (v_select)
  FROM vbak
  INTO CORRESPONDING FIELDS OF  TABLE it_vbak
  WHERE (v_condition).

Reward points if the answer is helpful.

Regards,

Mukul