how to write SQL statement for EXEC with multiple select-options as condition

former_member625844 — Thu, 22 Apr 2021 03:48:23 GMT

we have a program with three select-options S1,S2,S3. All of them are not obligatory. Now I want to connect to an oracle DB and fetch data with these select-options as condition.

The code is like:

EXEC SQL.
    select * into :WS from ODB 
    where o1 between s1-low and s1-high
    and o2 between s2-low and s2-high
    and o3 between s3-low and s3-high
ENDEXEC.

My question is if user did not input value for selection options, how to let it work? Suppose S2 is empty, I tried to assign s2-low and s2-high value of '%' but it didn't work. Thx.

Re: how to write SQL statement for EXEC with multiple select-options as condition

matt — Thu, 22 Apr 2021 07:17:21 GMT

Don't use EXEC SQL. Use the CL_SQL_STATEMENT classes
There is a class method that converts select options. Read all about it here: https://blogs.sap.com/2015/03/30/handling-of-select-options-parameters-within-amdp/ (The blog is AMDP, but it still applies).

topic how to write SQL statement for EXEC with multiple select-options as condition in Application Development and Automation Discussions

how to write SQL statement for EXEC with multiple select-options as condition

Re: how to write SQL statement for EXEC with multiple select-options as condition