AbapSimpleQueryBuilder

Very simple query builder (“query optimizer”) for ABAP Open SQL.

What it does

Sometimes you need to build a SELECT dynamically based on:

• which fields the caller wants to select,
• which filters are provided,
• and which JOINed tables are actually needed.

If you have a large JOIN across many tables, but some of those tables are only needed in special scenarios, joining them all the time will slow down the query.

This query builder helps by generating the following parts as strings:

• the result field list (SELECT (lv_result)),
• the FROM clause including only the necessary JOINs,
• the WHERE clause.

Important: it only adds “touched” tables (tables that appear in the result list or in the WHERE conditions).

Note: This is a simple utility. It is not perfect, but it is practical.

Example

Goal: select orders from CRMD_ORDERADM_H.

1) Create the query builder

By default, the builder uses Open SQL syntax, so ABAP variables must be prefixed with @.

DATA(lro_qb) = NEW lcl_query_builder(
  table = 'CRMD_ORDERADM_H'
  alias = 'H' ).

2) Add a JOIN

lro_qb->add_join(
  table = 'CRMD_ORDER_INDEX'
  alias = 'IC'
  pred  = 'H'
  on    = 'IC~HEADER EQ H~GUID' ).

3) Add criteria and result fields

lro_qb->add_where( field = 'H~PROCESS_TYPE' op = 'EQ' value = |'PRTY'| ).

lro_qb->add_result( 'H~GUID' ).
lro_qb->add_result( 'H~OBJECT_ID' ).
lro_qb->add_result( 'H~PROCESS_TYPE' ).

4) Optional: add a range table to the WHERE clause

In some scenarios the user filters by partner function and also wants to see it in the result.

Assume the partner range is stored in a global range table GT_COMPT.

To add a range table, pass its name as a string with @ prefix (because it will be concatenated into a dynamic WHERE string).

If you need string constants in dynamic conditions, make sure to include the quotes (as in |'X'|).

IF gt_compt[] IS NOT INITIAL.
  lro_qb->add_where( field = 'IC~PARTNER_NO' value = '@GT_COMPT' ).
  lro_qb->add_where( field = 'IC~PFT_9'      op    = 'EQ' value = |'X'| ).

  lro_qb->add_result( field = 'IC~PARTNER_NO' alias = 'COMPETITOR_NO' ).
ENDIF.

5) Generate the SQL fragments

DATA(lv_result) = lro_qb->get_result( ).
DATA(lv_from)   = lro_qb->get_from( ).
DATA(lv_where)  = lro_qb->get_where( ).

6) Execute the SELECT

SELECT (lv_result)
    INTO CORRESPONDING FIELDS OF @lt_data
  FROM (lv_from)
 WHERE (lv_where).

Generated SQL examples

If gt_compt[] contains data, the following SELECT will be executed:

SELECT H~GUID, H~OBJECT_ID, H~PROCESS_TYPE, IC~PARTNER_NO AS COMPETITOR_NO
  FROM CRMD_ORDERADM_H AS H
  JOIN CRMD_ORDER_INDEX AS IC
    ON IC~HEADER EQ H~GUID
 WHERE H~PROCESS_TYPE EQ 'PRTY'
   AND IC~PARTNER_NO IN @GT_COMPT
   AND IC~PFT_9 EQ 'X'.

Otherwise:

SELECT H~GUID, H~OBJECT_ID, H~PROCESS_TYPE
  FROM CRMD_ORDERADM_H AS H
 WHERE H~PROCESS_TYPE EQ 'PRTY'.