For example say I had the following SQL query:
Code: Select all
SELECT c1.Name
FROM Customers c1
WHERE c1.CustomerID NOT IN (SELECT DISTINCT c2.CustomerID
FROM Customers c2
JOIN Orders o ON c2.CustomerID = o.CustomerID
WHERE c2.Gender = 'Male'
AND o.ProductCode = 'ABC')
I tried using the 'not in_reference_column' operator in IA data rules, and set the reference table up as a virtual table in IA (to perform the subquery). However, you can't seem to create a virtual table that joins multiple tables together. So in the case of having a more complex subquery (which I do) virtual tables don't look like an option.
I guess I could implement a view, but it may not be possible to alter the source database, and its not desirable - it would be better to have all the data quality logic inside IA.
Another option I guess is to perform a left/right anti semi join. Eg.
Code: Select all
SELECT c1.Name
FROM Customers c2
JOIN Orders o ON c2.CustomerID = o.CustomerID AND c2.Gender = 'Male' and o.ProductCode = 'ABC'
RIGHT OUTER JOIN Customers c1 ON c1.CustomerID = c2.CustomerID
WHERE c2.Name IS NULL
But since I would be joining a table to itself I'd need to be able to define a table alias - again something IA doesn't seem to support. I don't think the join condition can be anything more than key1 = key2 either.
Am I having a mental blank here, or is it not possible to implement this kind of complexity in IA data rules?
