Function Module with optional parameter

[Former Member]

Hi Gurus,
I have a requirement where i have to fetch the records from MARC table based on matnr ( importing parameter - Optional ) and werks as table parameter .
When i am writing the select query on MARC , if the material is blank and plant is having data , still the select query is failing.
I want to get even if the material is blank , the select query should get all the materials under that plant.
Kindly help in this regard.
Thanks in Advance.

[IN]

Hello,

I suppose that you are using '=' in your query. Try the query with 'in' clause and you should get the results. Use 'select-options' instead of 'parameters'.

Example:

SELECT-OPTIONS: p_matnr FOR matnr NO INTERVALS.
"some code
select matnr werks
into (l_matnr, l_werks)
from MARC
where matnr in p_matnr
"some code
endselect.
"some code

Regards,

Igor

[former_member182550]

Select-endselect blocks are obsolete

[IN]

Thank you, Richard. 🙂

I would like to add that I misread the title and did not noticed that it was function call.

My apologies, I am not so well in multitasking obviously. 🙂

Regards,

Igor

[horst_keller]

"Select-endselect blocks are obsolete"

I didn't know that ...

Nested SELECT loops are mostly bad. But there can be use cases for processing the result set sequentially in one SELECT loop.

[former_member182550]

Hi Horst,

Sorry for the delay in getting back to you. I hope you and yours had a Merry Christmas and a good New Year!

I may be wrong (or it was specified in a companies programming standards), but as far as I am aware the TABLES statement is obsolete (I know for a fact the TABLES * option is). If then the TABLES statement is obsolete, that infers that a SELECT *...ENDSELECT block is also obsolete although not explicitly stated ?

Regards

Rich

[horst_keller]

Hi Rich,

Best wishes to you too!

Now, back to work ...

You mix up two things.

The TABLES statement and the short form of SELECT without an explicit INTO clause are obsolete:

TABLES dbtab.
SELECT * FROM dbtab ...
  ...
ENDSELECT.

But that does not mean that the SELECT loop itself becomes obsolete. Of course you can write:

DATA wa TYPE dbtab.
SELECT * FROM dbtab ... INTO @wa.
  ...
ENDSELECT.

Or nowadays:

SELECT * FROM dbtab ... INTO @DATA(wa).
  ...
ENDSELECT.

The rule is: Avoid nested SELECT loops.

[Jelena_Perfiljeva]

While not officially obsolete, I believe SELECT... ENDSELECT has been treated as obsolete by many customers. A simple explanation is that if you allow it then for sure people will use it with TABLES, as Rich mentioned. As most ABAPers lack Horst's finesse it's just easier to tell them to forget about it.

I was actually quite surprised it was not technically obsolete. Not that I had any practical need for it in the past 10 years though...

[horst_keller]

"A simple explanation is that if you allow it then for sure people will use it with TABLES,"

Following this line of argument, we must declare LOOP AT itab obsolete, because for sure people will use it with header lines, duh ...

No, SELECT - ENDSELECT is a construct that can be used, if you want to read the lines of a result set sequentially and need it only once. No real need for filling an internal table then.

In fact, even the new WITH comes with an ENDWITH!

The documentation says:

"Whether data should better be read into an internal table or a work area depends on the type of further processing: If data is required only once in a program, it should be imported into a work area, row by row, by a SELECT. Reading data into an internal table requires more memory space (without having a considerably higher reading speed). If, on the other hand, data is required many times in a program, it should be read into an internal table. The disadvantage of the increased memory requirement is more than compensated for here by the advantage of a once-only selection"

(But admittedly, I also use INTO TABLE mostly, because internal table processing gives me more possibilities).

[matt]

On a few occasions recently I've used SELECT... ENDSELECT as the resultant code was more straightforward than using INTO TABLE.

[Jelena_Perfiljeva]

Would this apply to any DB or mostly HANA? The old mantra, as I recall, was to use memory more and avoid hitting up DB too much. Now that DB is in memory what's the new mantra?

(Well, I guess one of those myth debunking blogs is in order.)

[horst_keller]

This applies to any DB, since a SELECT - ENDSELECT is a single database access, as long as no nested SELECTS are contained ...

[former_member182550]

Firstly, you can check if a parameter is provided to a function module by using the 'IS SUPPLIED' clause. For exporting parameters its' IS REQUIRED'.
I would code your select something like:

If it_Werks Is Not Initial.
   If I_Matnr Is Supplied.
      Select [field-list]
        Into Table et_Results
        From Marc
         For All Entries in it_Werks
       Where Matnr = I_Matnr And
             Werks = it_Werks-Werks.
   Else.  
      Select [field-list]
        Into Table et_Results
        From Marc
         For All Entries in it_Werks
       Where Werks = it_Werks-Werks.
   EndIf.
EndIf.

Regards.

Rich

[raghug]

If the material is blank in your query, it is looking for a MARC entry with no material - I don't think that is going to happen. To keep the select statement and therefore its error handling together in a single piece, you could combine the recommendations of Igor and Richard. Use a range for the material instead of the SELECT-OPTIONS. Then use the "IS SUPPLIED" as suggested by Richard to add records to the range.

In this sample code, some of it (non-relevant to the question at hand) is specific to ABAP 7.4 and above, but can easily be replaced by other older ABAP commands.

    DATA:
      it_matnr TYPE RANGE OF matnr,
      it_werks TYPE RANGE OF werks_d,
      lr_matnr LIKE LINE OF it_matnr,
      lr_werks LIKE LINE OF it_werks.


    IF i_matnr IS SUPPLIED.
      it_matnr = VALUE #( ( sign = 'I' option = 'EQ' low = i_matnr ) ).
    ENDIF.


    LOOP AT i_werks_tab INTO DATA(l_werks).
      lr_werks = VALUE #( sign = 'I' option = 'EQ' low = l_werks ).
      APPEND lr_werks TO it_werks.
    ENDLOOP.


    SELECT * FROM marc
      WHERE matnr IN @it_matnr
      AND werks IN @it_werks
      INTO TABLE @DATA(et_results).

[Sandra_Rossi]

sign = 'I' (not +)

[raghug]

Fixed in code sample... thank you!

[Jelena_Perfiljeva]

If you have a material as a single value parameter then it does not seem logical to make it optional and return the plant-ful of materials when it's blank. It is not like parameters work in the reports and it wouldn't be intuitive to someone using such function IMHO. If you are going in this direction then maybe it'd make sense to pass to the function a table that could contain 1 or more material numbers or be blank. This would work like SELECT-OPTIONS and you could even use a range table type for more complex criteria. In this way at least you'll have more robust function.

There are also some memory and security considerations, of course.

Overall this is a rather simple ABAP task, next time you might want to try something yourself first before posting a question on SCN. At least you'd be able to post more specific question based on your experiments.