How to handle null report parameters inside a SQL Statement

[former_member1317840]

Hi All,

I am struggling to find an easier way to find how i can implement this kind of query in ABAP

select *

from zemployees

where employeeid = ISNULL(p_employeeid,employeeid)

and cityid = ISNULL(p_cityid,cityid)

p_employeeid and p_cityid are all user entered parameters.

if the user did not enter a value to p_employeeid then the query is expected to return all employee records regardless of the id

if the user did not enter a value to p_cityid then the query is expected to return all employee records regardless of the city

I know i can do write individual queries for dealing with each condition but, is there an easier way to do this in abap?

I'd appreciate any input on this

[teshanappadoo]

You can use ranges for your where clause. If the ranges is filled with your input it will return data corresponding to your input and if it is empty it will just bring all data from the DB.

SELECT *
  FROM zemployees
 WHERE employeeid IN r_empid[]
   AND cityid IN r_cityid[]

[michael_piesche]

The RANGE-datatype would be perfect for your case, just like Teshan already mentioned.

If a RANGE-datatype is used in a SELECT statement in the WHERE-clause, there are certain rules to pay attention to: One of them is, if the range table is empty, this specific where-restriction is not evaluated.

There are several ways to declare RANGE-datatypes.

EXAMPLE 1:

This shows you how to do it as a selection-screen parameter. If you only want to allow single values, you can add the options 'NO-EXTENSION NO INTERVALS':

TABLES zemployees.
SELECT-OPTIONS r_empid  FOR zemployees-employeeid. "  NO-EXTENSION NO INTERVALS.
SELECT-OPTIONS r_cityid FOR zemployees-cityid.     "  NO-EXTENSION NO INTERVALS.

SELECT *
  FROM zemployees
 WHERE employeeid IN r_empid[]
   AND cityid     IN r_cityid[]

EXAMPLE 2:

The following would work for you if you have a newer SAP ABAP stack and you dont want to change anything about the selection screen:

DATA r_empid  LIKE RANGE OF p_employeeid.
DATA r_cityid LIKE RANGE OF p_cityid.

IF p_employeeid IS NOT INITIAL.
  r_empid[] = value #( ( sign = 'I' option = 'EQ' low = p_employeeid ) ).
ENDIF.
IF p_cityid IS NOT INITIAL.
  r_cityid[] = value #( ( sign = 'I' option = 'EQ' low = p_cityid ) ).
ENDIF.

SELECT *
  FROM zemployees
 WHERE employeeid IN r_empid[]
   AND cityid     IN r_cityid[]

EXAMPLE 3:

For older versions, you would have to use this instead:

DATA r_empid  LIKE RANGE OF p_employeeid.
DATA w_empid  LIKE LINE OF r_empid.
DATA r_cityid LIKE RANGE OF p_cityid.
DATA w_cityid LIKE LINE OF r_cityid.
IF p_employeeid IS NOT INITIAL.
  w_empid-sign   = 'I'.
  w_empid-option = 'EQ'.
  w_empid-low = p_employeeid.
  APPEND w_empid TO r_empid.
ENDIF.
IF p_cityid IS NOT INITIAL.
  w_cityid-sign   = 'I'.
  w_cityid-option = 'EQ'.
  w_cityid-low = p_cityid.
  APPEND w_cityid TO r_cityid.
ENDIF.

SELECT *
  FROM zemployees
 WHERE employeeid IN r_empid[]
   AND cityid     IN r_cityid[]

Let me know if this works for you, or if you need further help.

[michael_piesche]

strugglingabaper06, please follow up on your open question.

• comment answers or your question if there are still open issues.
• otherwise mark an answer as accepted if it helped you solve your problem
• or post an answer of yourself and accept it if you found another useful solution yourself
• or redirect your question to another question that is related and was useful to solve your problem
• in the end, close your question