Dynamic SQL in SQR with example:

Assume a scenario; SQR generates employee details report based on either Company or pay Group.

 So SQR code would be.

If $Run_Option = ‘P’

Begin-Select

A.EMPLID (+1, 1)

A.NAME (, +5)

 FROM XYZ A

WHERE A.PAYGROUP IN (‘PG’)

End-Select

Else

Begin-Select

B.EMPLID (+1, 1)

B.NAME (, +5)

 FROM XYZ B

WHERE B.COMPANY IN (‘SAP’)

End-Select

End-If

Above code can be tuned by Create dynamic SQL.

If $Run_Option = ‘P’

   LET $WHERE = 'WHERE A.PAYGROUP IN (' || '''' || 'PG' || '''' || ')'

END-IF

IF $Run_Option = ‘C’

   LET $WHERE = 'WHERE A.COMPANY IN (' || '''' || 'SAP' || '''' || ')'

End-If

Begin-Select

EMPLID (+1, 1)

NAME (, +5)

 FROM XYZ A

[$where]

End-Select

If run option is not P and C then there won’t be any where condition. SQR will not give any error. It runs without the Where condition, so all the rows from XYZ table will be Selected.