Function Module: how to setting table import parameter

[Former Member]

hi, i'm developing a function module. i create a TABLES where it will allow multiple variables IMPORT from user input to this function module. the tables i set as OPTIONAL and decribed as below.

PSPID	PS_PSPID	CHAR	24
POSID	PS_POSID	CHAR	24
AUFNR	AUFNR	CHAR	12
PSY	J_STATUS	CHAR	5
PUS	J_STATUS	CHAR	5

in my source code, i done a select statement to process the IMPORT input . the problems i face,for the PSY and PUS input, if the one or both input is BLANK, NO RESULT will display. What i'm try to do , if input one or both is BLANK, it wil neglect the input and return as ALL RESULT (return result oir all status for input pspis / posid / aufnr) . attached the select statement. thank you

itparm[] = objparm[].

 select
    a~pspnr a~stspr a~objnr a~pspid
    b~psphi b~objnr as wobjnr b~posid
    c~aufnr c~objnr as nobjnr c~pspel
    d~objnr as jeobjnr d~inact d~stat
    e~objnr as jcobjnr e~udate e~usnam e~utime e~stat as jcstat e~inact as jcinact
    f~istat as syistat
    f~txt04 as sytxt04
    f~spras
    g~estat as usestat
    g~txt04 as ustxt04
    into corresponding fields of table itobj
    from proj as a
    inner join prps as b on a~pspnr = b~psphi
    inner join aufk as c on b~pspnr = c~pspel
    inner join jest as d on c~objnr = d~objnr
    inner join jcds as e on d~objnr = e~objnr
                         and d~stat = e~stat
    inner join tj02t as f on e~stat = f~istat
    inner join tj30t as g on a~stspr = g~stsma
    for all entries in itparm
    where ( a~pspid = itparm-pspid
    or b~posid = itparm-posid 
    or c~aufnr = itparm-aufnr )
   and ( f~istat = itparm-PSY and g~estat = itparm-PUS ).

[Jelena_Perfiljeva]

Well, the problem is here, obviously:

and ( f~istat = itparm-PSY and g~estat = itparm-PUS )

I guess you're confusing this with SELECT WHERE ... IN ... , which works, for example, with selection options and a blank value is treated like "get all". It's not the case here. I guess the easiest solution would be to have a dynamic WHERE condition, e.g.:

IF itparm-psy IS not INITIAL.
  concatenate where_condition 'AND F~ISTAT = ITPARM~PSY' into where_condition.
ENDIF.

SELECT ... WHERE (where_condition).

Edited by: Jelena Perfiljeva on Aug 17, 2009 5:35 PM

[Former Member]

You cannot do anything if you are doing a FOR ALL ENTRIES. Your options are to remove those two from yur WHERE clause and delete the records later in a loop that do not satify the conditions for them or loop at your table and do a select for each record(inefficient).

[Former Member]

hi, i tried this code and it work!, but i'm not sure is it a good practice. since i can't practiced the ALL ENTERIES i modify the code using lopp statement this is the code.

loop at itparm into waparm.
whercond = '( a~pspid = waparm-pspid or b~posid = waparm-posid or c~aufnr = waparm-aufnr )'.
 if waparm-psy is not initial and waparm-pus is not initial.
  concatenate whercond 'and ( f~istat = waparm-psy and g~estat = waparm-pus )' into whercond separated by space.
 elseif waparm-psy is not initial and waparm-pus is initial.
   concatenate whercond 'and f~istat = waparm-psy' into whercond separated by space.
 elseif waparm-psy is initial and waparm-pus is not initial.
   concatenate whercond 'and g~estat = waparm-pus' into whercond separated by space.
 endif.


 select
    a~pspnr a~stspr a~objnr a~pspid
    b~psphi b~objnr as wobjnr b~posid
    c~aufnr c~objnr as nobjnr c~pspel
    d~objnr as jeobjnr d~inact d~stat
    e~objnr as jcobjnr e~udate e~usnam e~utime e~stat as jcstat e~inact as jcinact
    f~istat as syistat
    f~txt04 as sytxt04
    f~spras
    g~estat as usestat
    g~txt04 as ustxt04
    into corresponding fields of table itobj
    from proj as a
    inner join prps as b on a~pspnr = b~psphi
    inner join aufk as c on b~pspnr = c~pspel
    inner join jest as d on c~objnr = d~objnr
    inner join jcds as e on d~objnr = e~objnr
                         and d~stat = e~stat
    inner join tj02t as f on e~stat = f~istat " system status
    inner join tj30t as g on a~stspr = g~stsma "user status
    where (whercond).

    loop at itobj into waobj.
       move-corresponding waobj to waans.
       append waans to itans.
    endloop.

clear itobj[].
endloop.

[Jelena_Perfiljeva]

SELECT in a LOOP is a very bad practice. I thing you can use FOR ALL ENTRIES, but you'll need to review your SELECT statement. Frankly, right now I just can't understand what you're trying to achieve. Perhaps separate SELECT statements would actually work better than this monster.

P.S. You don't have to use aliases, you can use the table names, e.g. proj_{pspnr instead of a}pspnr. This makes the code easier to read for yourself and maintain for others.

[Former Member]

As I already mentioned, it certainly is not efficient. I also noticed that you are doing a INTO CORRESPONDING FIELDS OF TABLE. This will erase all the previous contents of the internal table, so you introduced another loop (you are already in a loop) to move the contents of this internal table into another internal table (again using MOVE-CORRESPONDING). If you know that the fields of your internal table and the database table match (name and order in which they appear), then it is efficient to use INTO TABLE instead of INTO CORRESPONDING FIELDS OF TABLE. This is correct for the second loop also if you know your second internal table(itans) is of the same structure as the first one(itobj). In fact with internal tables you can also use "APPEND LINES OF itobj TO itans" if you know they are of same structure(sometimes it pays to keep them same).

You can also look at the APPENDING CORRESPONDING FIELDS OF TABLE for your SELECT.

In short, there is a lot of improvement you can do with your logic.

[Former Member]

Thanks for your quick reply. actually the data will be somehing like this. since i developed a function module, i keep the import parameter in a table.

PSPID	PS_PSPID	CHAR	24
POSID	PS_POSID	CHAR	24
AUFNR	AUFNR	    CHAR	12
PSY  	J_STATUS	CHAR	5
PUS 	J_STATUS	CHAR	5

the example data will be in OTPARM internal table

PSPID              POSID          AUFNR     PSY     PUS
aa-aa-aa         a1                                  I001
bb-bb-bb                              b1                        E009
cc-cc-cc

i'll store this into an internal table. from the data. it can be seen there a BLANK input. so the blank input should not be included into WHERE connditions. that why in the previous solution, i use SELECT inside LOOP because every line of the IMPORT tables will be generated with different WHERE conditions. i tried to do something like this:

PSPID         POSID     AUFNR     PSY     PUS        cond_syx
aa-aa-aa     a1                           I001                  if proj~pspid = itparm-pspid and prps~posid = itparm~posid and ....
bb-bb-bb                   b1                         E009     if proj~pspid = itparm-pspid and aufk~aufnr = itparm~paufnr and ....
cc-cc-cc                                                             if proj~pspid = itparm-pspid

and implement with FOR ALL ENTERIES, but at the WHERE conditions i failed to put the CONF_SYX. please comment and give opinions. Thanks you very much.

[Jelena_Perfiljeva]

It just seems that this function module is trying to be too flexible... If you expect the table to have just a few entries, it might actually be more efficient to read all the data for the project number and then eliminate unwanted records in a separate loop. You might want to run some tests to find how the code will perform. Use SQL Trace (ST05) or Runtime Analysis.

[Former Member]

You best solution may be to select entries from the database based on just the PSPID, loop at the result internal table and put your if statements there to delet the records you don't need and keep the records you want.