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.