How to validate the result from BO by writing SQL in toad

[devnaren]

Hi All,

Using BOXIR2

I Have a Question regarding how to write an SQL in toad to unit test my Data generated by WEBI.

I have two Queries(Query1 and Query2) and I used merge dimensions on the common dimensions. But i want to know how to write a SQL in Toad to validate my results.
Data is coming from One Universe

 

[blom0344]

Essentially write 2 temp sets in TOAD and then perform a full outer join on the fields that represent the common dimensions.

Example:

SELECT COALESCE(TEMP.A,TEMP2.B),TEMP.SUMX,TEMP2.SUMY
FROM
(SELECT A AS A, SUM(X) AS SUMX FROM TABLE1
WHERE ...............
group by A)TEMP
FULL OUTER JOIN
(SELECT B AS B, SUM(Y) AS SUMY FROM TABLE2
WHERE ...............
group by B)TEMP2
ON TEMP.A = TEMP2.B

Ties Blom

 

[devnaren]

Thanks. This example will give me an idea of how to validate my queries.

 

[skrandel]

That's overkill.

You don't have to validate the merging of the dimensions. You really should just be validating that each query returns the result you want.

However..... It's just SQL. How is the code you're writing going to differ from what WebI generates?

Steve Krandel
Intuit

 

[blom0344]

Steve, I am offering him a solution. Whether he should use it for testing or other purposes is up to him. If the TOAD output is in line with the output of WEBI so much the better, I'd say..

Ties Blom

 

[skrandel]

Ties,
I was saying that your solution is correct, but not needed.

This is one of those situations where the request is not reasonable. You don't validate that reports have the right content. You validate that a query generated by BO is the right query. But, running queries in Toad isn't going to prove that.

SQL is SQL. If BO runs it, then it will produce the same results that Toad will.

Not a criticism of you. It's really directed at a poorly defined requirement.

Steve Krandel
Intuit