Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

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

Status
Not open for further replies.

devnaren

Programmer
Aug 23, 2007
49
US
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
 
Essentially write 2 temp sets in TOAD and then perform a full outer join on the fields that represent the common dimensions.

Example:

Code:
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

 
Thanks. This example will give me an idea of how to validate my queries.
 
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
 
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

 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top