Validate Rule

kumar66 · Post by **kumar66** » Fri Dec 23, 2011 12:26 am

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

ray.wurlod · Post by **ray.wurlod** » Fri Dec 23, 2011 2:48 am

Create a routine that can be passed the logic, fields to be tested and, if necessary, metadata.

kumar66 · Post by **kumar66** » Fri Dec 23, 2011 2:55 am

Hi Ray,

Can you please explain more on this.

Thanks & Regards,
Kumar66

Kirtikumar · Post by **Kirtikumar** » Fri Dec 23, 2011 3:56 am

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.

kumar66 · Post by **kumar66** » Fri Dec 23, 2011 4:05 am

Hi Kirtikumar

Thanks for your response.

Yes , for a single row we need to validate all the 150 rules.

I have diffculty in parsing . How to do that after the join.

Please Advise.

Thanks,
Kumar66

ray.wurlod · Post by **ray.wurlod** » Fri Dec 23, 2011 9:59 pm

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.

jreddy · Post by **jreddy** » Tue Dec 27, 2011 7:30 pm

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