Home > other >  Oracle script to create dynamic query based on SQL held in a field
Oracle script to create dynamic query based on SQL held in a field

Time:11-09

Where a user gives a set of inputs from one table, e.g. "request_table" a:

User Input Value Field Name in Database
Product Deposit product_type
Deposit Term (months) 24 term
Deposit Amount 200,000 amount
Customer Type Charity customer_type
Existing Customer Y existing_customer

Would like to use the product selection to pick out SQL scripts embedded in a "pricing_table" b, where the price is made up of components, each of which are affected by one or more of the above inputs:

Product Grid Measures Value1 Value1Min Value1Max Value2 Value2Min Value2Max Price
Deposit Term_Amount a.term>=b.value1min and a.term<b.value2 max and a.amount>=b.value2min and a.amount<b.value2max 0 12 0 100000 1
Deposit Term_Amount a.term>=b.value1min and a.term<b.value2 max and a.amount>=b.value2min and a.amount<b.value2max 12 36 0 100000 2
Deposit Term_Amount a.term>=b.value1min and a.term<b.value2 max and a.amount>=b.value2min and a.amount<b.value2max 36 9999 0 100000 3
Deposit Term_Amount a.term>=b.value1min and a.term<b.value2 max and a.amount>=b.value2min and a.amount<b.value2max 0 12 100000 500000 1.1
Deposit Term_Amount a.term>=b.value1min and a.term<b.value2 max and a.amount>=b.value2min and a.amount<b.value2max 12 36 100000 500000 2.1
Deposit Term_Amount a.term>=b.value1min and a.term<b.value2 max and a.amount>=b.value2min and a.amount<b.value2max 36 9999 100000 500000 3.1
Deposit Term_Amount a.term>=b.value1min and a.term<b.value2 max and a.amount>=b.value2min and a.amount<b.value2max 0 12 500000 99999999 1.2
Deposit Term_Amount a.term>=b.value1min and a.term<b.value2 max and a.amount>=b.value2min and a.amount<b.value2max 12 36 500000 99999999 2.2
Deposit Term_Amount a.term>=b.value1min and a.term<b.value2 max and a.amount>=b.value2min and a.amount<b.value2max 36 9999 500000 99999999 3.2
Deposit Customer_Type a.customer_type=b.value1 Personal 0
Deposit Customer_Type a.customer_type=b.value1 Charity 0.1
Deposit Customer_Type a.customer_type=b.value1 Business -0.1
Deposit Existing_Customer a.existing_customer=b.value1 Y 0.1
Deposit Existing_Customer a.existing_customer=b.value1 N 0

Where the query is: select distinct measures from pricing_table where product=(select product_type from request_table). This gives multiple rows where SQL logic is held. Would like to run this SQL logic in a LOOP, e.g.: select b.* from pricing_table b where :measures This would return all rows where the specific metrics are matched. Doing it this way as the exact columns in the input can grow to hundreds, so don't want a really wide table. Any help appreciated thanks.

I've creating tables but am unsure how to loop the measures, and apply the values from that field in a looped query thanks.

CodePudding user response:

In a PL/SQL pipelined function, you can build the SQL query and open a cursor on it, loop on the results and PIPE the rows.

  • Related