topic Re: Select Query in FM in Application Development and Automation Discussions

Select Query in FM

SG141 — Thu, 16 Aug 2007 18:02:47 GMT

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.

Re: Select Query in FM

Former Member — Thu, 16 Aug 2007 18:19:14 GMT

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

Re: Select Query in FM

SG141 — Thu, 16 Aug 2007 18:22:45 GMT

Aman,

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

Re: Select Query in FM

Former Member — Thu, 16 Aug 2007 18:24:13 GMT

Hi,

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

Regards,

Mukul

Re: Select Query in FM

Former Member — Thu, 16 Aug 2007 18:25:31 GMT

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.

Re: Select Query in FM

SG141 — Thu, 16 Aug 2007 18:32:42 GMT

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

Re: Select Query in FM

Former Member — Thu, 16 Aug 2007 18:34:26 GMT

Hi Karthik,

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

Amandeep

Re: Select Query in FM

SG141 — Thu, 16 Aug 2007 18:34:34 GMT

Aman your logic is returning a error

Re: Select Query in FM

Former Member — Thu, 16 Aug 2007 18:36:27 GMT

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.

Re: Select Query in FM

SG141 — Thu, 16 Aug 2007 18:41:03 GMT

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.

Re: Select Query in FM

Former Member — Thu, 16 Aug 2007 18:45:30 GMT

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

amandeep

Re: Select Query in FM

Former Member — Thu, 16 Aug 2007 18:45:57 GMT

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

Re: Select Query in FM

Former Member — Thu, 16 Aug 2007 18:46:35 GMT

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

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

Re: Select Query in FM

Former Member — Thu, 16 Aug 2007 18:49:14 GMT

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

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

Re: Select Query in FM

Former Member — Thu, 16 Aug 2007 19:01:15 GMT

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...

Re: Select Query in FM

SG141 — Thu, 16 Aug 2007 19:10:15 GMT

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

Re: Select Query in FM

Former Member — Thu, 16 Aug 2007 19:14:46 GMT

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

Re: Select Query in FM

Former Member — Thu, 16 Aug 2007 19:18:23 GMT

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.