Create a Dynamic ORACLE SQL witha WHERE clause

Development (ABAP Development WorkBench, ABAP/4 programming)

Moderators: Snowy, thx4allthefish, YuriT, Gothmog

Post Reply
Jkeri
Posts: 62
Joined: Thu Jul 21, 2011 12:44 pm

Create a Dynamic ORACLE SQL witha WHERE clause

Post by Jkeri » Wed Dec 11, 2013 10:02 am

I have the following SQL statement:
EXEC SQL PERFORMING X80_APPEND_DMS_DATA.
select pu.control_number,
pu.center_code,
pu.donor_number,
pu.item_number_code,
dv.visit_date,
pu.actual_volume,
s.shipment_number,
uc.case_number,
s.bill_of_lading
into :gs_mat_center-control_number,
:gs_mat_center-center_code,
:gs_mat_center-donor_number,
:gs_mat_center-item_number_code,
:gs_mat_center-visit_date,
:gs_mat_center-actual_volume,
* :gs_mat_center-shipment_number,
:gs_mat_center-case_number,
:gs_mat_center-bill_of_lading
from plasma_units PU,
shipping_cases SC,
unit_cases UC,
donor_visits DV ,
shipments S
where
ENDEXEC.


I put in logic to create a dynamic 'Where clause' and teh results are in an internal table,
My question is how do i code the SELECT statement between teh EXEC SQL and ENDEXEC to include the contents of my 'WHERE' clause..

Gothmog
Posts: 1952
Joined: Wed Sep 12, 2007 4:46 am
Location: Probably not home

Re: Create a Dynamic ORACLE SQL witha WHERE clause

Post by Gothmog » Wed Dec 11, 2013 10:26 am

I don't think you can use this condition table dynamically with native SQL.
You could generate a subroutine pool with the complete SQL statement, and call the generated routine.
68 74 74 70 3a 2f 2f 74 69 6e 79 75 72 6c 2e 63 6f 6d 2f 62 64 6f 37 6d 77 67

Rich
Posts: 7116
Joined: Thu Oct 31, 2002 4:47 pm
Location: Liverpool
Contact:

Re: Create a Dynamic ORACLE SQL witha WHERE clause

Post by Rich » Thu Dec 12, 2013 4:15 am

Gothmog wrote:I don't think you can use this condition table dynamically with native SQL.
You could generate a subroutine pool with the complete SQL statement, and call the generated routine.
However, there is no way to dispose of individual subroutine pools so if your code is going to do this more than 37 times (the max number of Subroutine pools per session allowed) then it will fail.
Regards

Rich

Image
Abap KC:http://www.richard-harper.me.uk/Kb
SFMDR:http://www.se37.com

Gothmog
Posts: 1952
Joined: Wed Sep 12, 2007 4:46 am
Location: Probably not home

Re: Create a Dynamic ORACLE SQL with a WHERE clause

Post by Gothmog » Thu Dec 12, 2013 4:29 am

Even if you reuse the same subroutine pool name?
68 74 74 70 3a 2f 2f 74 69 6e 79 75 72 6c 2e 63 6f 6d 2f 62 64 6f 37 6d 77 67

Rich
Posts: 7116
Joined: Thu Oct 31, 2002 4:47 pm
Location: Liverpool
Contact:

Re: Create a Dynamic ORACLE SQL with a WHERE clause

Post by Rich » Mon Dec 16, 2013 4:03 am

Gothmog wrote:Even if you reuse the same subroutine pool name?
I can't remember where I got the value of 37 from, but SAP help states:
A temporary subroutine pool generated for an internal mode cannot be explicitly deleted. It remains available from its generation up to the point where the internal session is terminated.
.

I'm not certain if it is overwritten by a subroutine pool of the same name.

Btw, did you know procedures are now obsolete ?
Since subroutines are now obsolete as a method of program modularization, a temporary subroutine pool created using GENERATE SUBROUTINE POOL should only contain a single initial subroutine that calls a method of a local class and does not contain any other functional coding.
Regards

Rich

Image
Abap KC:http://www.richard-harper.me.uk/Kb
SFMDR:http://www.se37.com

Post Reply