Stored procedure that does count of two tables?

[jisoo23]

Hello all,

I'm a newbie to this software so please bear with me. I'm trying to come up with the total number of records from each of two different tables (i.e. need to find total from one table, and the total from the other table). Is there any way to do this with a stored procedure? If so, how? The Informatica manual doesn't seem to have enough info on this regard.

Thanks,
Jisoo23

 

[jisoo23]

Ok I've ditched the stored procedure idea (can't seem to find any info on this around the web). I've decided to go with a join and then an aggregate transformation. I'm stuck on one part though, I'm trying to rig up a boolean expression in the aggregate transformation where the variable would take in two other variables (within the same transformation) divide one by the other and then compare to a constant number (for example, 2). If the conditional is not equal to 2, the workflow fails. Else it goes on. I inserted a new variable port in the aggregate transformation and called it "TOTAL_RESULT". In the expression editor, I've put in the following:

IIF((TOTAL_1/TOTAL_2 != 2)1,[0])

I don't think this expression is correct as it does not validate. Can someone give me an idea what I'm doing wrong?

Thanks,
Jisoo23

 

[blom0344]

IIF((TOTAL_1/TOTAL_2 != 2)1,[0])

The square brackets are not allowed in any case....

My personal solution (but then I am my own DBA) is to store the rowcount in database views and then use lookups to fetch these counts within the real mapping.

This allows a very clean solution, cause database views are inherently in sync with the data they are based on.

I am very interested in the story behind this. Why do a check whether one table stores exactly twice as many rows as another?

Ties Blom
Information analyst
tbl@shimano-eu.com