CODEWITHSUNDEEP
plsqloracle11g
MidnightThoughtful
MidnightThoughtful

Reputation: 233

Finding records based on derived column values

We have an employee category system that encapsulates values in approximately 25 columns. The columns indicate features available to our employees in one of our core systems.

We want to build a process by which someone can

...and the code will determine if any existing category matches the features each employee in the list currently has with the new feature(s) enabled.

So far, the only approach I can think of is a cursor that loops through the list of employees in the request, and for each one, builds a SELECT string based on their current column values, overriding any features from the change request, and then dynamically running and returning the select from our category table to see if anything matches.

I'm wondering if there is a better approach?

A simplified example of our data:

Categories table:

    Category Feature1 Feature2 Feature3
    Cat1     Yes      No       1
    Cat2     Yes      Yes      1
    Cat3     No       No       2

Employees table:

    EmployeeId Category
    Emp1       Cat1
    Emp2       Cat2

In the above example, if we are requested to see if a Category exists in which Emp1 can keep all of their existing features, but have Feature2 enabled (in this case 'Yes'), then the code needs to identify that Cat2 is a suitable category (if no suitable category exists with existing and new features, then we advise that the requestor needs to be asking for a new category to be created).

Upvotes: 0

Views: 32

Answers (1)

fhossfel
fhossfel

Reputation: 2191

You don't need to build the SQL on demand. You can do this this bind variables and COALESE (or NVL if you prefer that):

SELECT Category
FROM categories c1
JOIN employee e ON 1=1
JOIN categories c2 ON e.category = c2.category
WHERE e.employeeID = ?
  AND c1.feature1 = COALESCE(?, c2.feature1)
  AND c1.feature2 = COALESCE(?, c2.feature2)
  AND c1.feature3 = COALESCE(?, c2.feature3)

Then you use employeeId, new_feature1, new_feature2, new_feature3 etc. as the bind values. If the new value is NULL then the old value will be used.

Upvotes: 2

Related Questions