Complex Data Rules in IA
Posted: Tue May 03, 2011 10:51 pm
I can't figure out how to implement complex logic in data rules. The logic builder and free form editor don't seem to support things like distinct, group by, or sub queries.
For example say I had the following SQL query:
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.
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?
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?