Hi All,
I have a scnerio where I have to validate the conditions.
For eg :
I have two table master and child.
Master Table:
CleintId Name City Country Citizen
101 A Chennai India
Child Table:
CleintId Status RuleExpression
101 Y Name='A' and City='Chennai' and country='India'
101 N Name='B' and City='Chennai' and country='India'
101 Y Name='A' or City='Bglr' or country='India'
So I have to check each rule expression and I have to take the Status Colum.
In real time there are around 150 rules to be validated for each clientid.
Please Advise on this.
Kumar66
Validate Rule
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 437
- Joined: Fri Oct 15, 2004 6:13 am
- Location: Pune, India
So for a single row there would be 150 rules and it needs to be matched with all of them or the first match should be considered?
Assuming, the first match is considered - Is the rule only on two columns City and Country, or would it be on more columns as well.
If just two columns, then a lookup/join on 3 cols ClientID, City and Country would do the job. You would have to parse the String in the RuleSet to convert them to City and Country columns.
If the rule can contain more than two columns and different permutation and combination then would be a little complex. You would have send each row for rule comparison and you might end up runnig 150 validations for each ClientID.
Assuming, the first match is considered - Is the rule only on two columns City and Country, or would it be on more columns as well.
If just two columns, then a lookup/join on 3 cols ClientID, City and Country would do the job. You would have to parse the String in the RuleSet to convert them to City and Country columns.
If the rule can contain more than two columns and different permutation and combination then would be a little complex. You would have send each row for rule comparison and you might end up runnig 150 validations for each ClientID.
Regards,
S. Kirtikumar.
S. Kirtikumar.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Output of the join will be up to 150 rows for each left input row. So you call the routine that many times from a Transformer stage, each time returning 0 (success) or 1 (failure). Sum these; if the sum is non-zero at least one rule was violated.
Note that this is a difficult task to achieve with a rules table such as you have constructed - you need to write your own parser (and possibly lexer as well) into the routine, and make sure that it can handle all possible rule formulations.
Note that this is a difficult task to achieve with a rules table such as you have constructed - you need to write your own parser (and possibly lexer as well) into the routine, and make sure that it can handle all possible rule formulations.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Can this be done with just a SQL, provided you are able to redesign the child table that has the rule expressions ? (also not sure if you have any OR or ! conditions.. but if all are AND expressions the table can be redesigned or staged to look like this for easy parsing and transformation
Example:
Table X:
ClientId Name City Country Citizen
101 A Chennai India
Table Y:
ClientId Status Name City Country
101 Y A Chennai India
101 N B Chennai India
101 Y A Bglr India
select A.clientId,
(select Y.Status where X.clientID = Y.clientId
and A.Name = Y.Name
and A.city = Y.city
and A.country = Y.Country) status
from table X
Don't forget to put appropriate NVL values for status columns to avoid NULLS
Example:
Table X:
ClientId Name City Country Citizen
101 A Chennai India
Table Y:
ClientId Status Name City Country
101 Y A Chennai India
101 N B Chennai India
101 Y A Bglr India
select A.clientId,
(select Y.Status where X.clientID = Y.clientId
and A.Name = Y.Name
and A.city = Y.city
and A.country = Y.Country) status
from table X
Don't forget to put appropriate NVL values for status columns to avoid NULLS