DSXchange

Hi,

I have a query with joins on 4 tables and a couple of filter conditions. The query returns 5 columns as output and has a count(distinct(colA)) as one of the output columns. I am trying to implement this query using Data Rules functionality in IA and I was able to replicate the joins and filter conditions but not count(distinct(colA)) .
When I try to Generate Rule using a Data Rule definition, I can only use count function on the columns that are defined in the Rule definition but I cannot use the count function on any of the columns from the data source list. If I have to define count(colA) function in the rule definition, the count function must be associated with one of the conditions like > or < or >= or <=.
Can someone suggest if there is a better way of doing this? Datastage is not a option here. This should be done using Data Rules functionality only. That is the requirement.
Thanks in advance.

Can you do something with the OCCURS operator (for example value OCCURS < 4)?

Hi Ray, Thanks for your reply.
I couldn't really test the count() function so far as I see that the joins are not working. I have table A with 5 million recs and table B with 2000 recs. There is a key column in both the tables and using a join I have to test if every record in table B has a matching key in Table A. I created a rule definition and generated a data rule. When I run the rule I should get 2000 recs as output but I see the output as 5 million recs. All the data values in table B is a subset of data values in table A.

I am using the following condition in the rule definition.
TableB_columnname in_reference_column TableA_columnname.

Can someone provide some help on this? Thanks in advance.