Solved

How to write SQL in Toad to test the Data

Posted on 2009-03-30
5
1,058 Views
Last Modified: 2013-11-15
I have a Q regarding how to write an SQL in toad to unit test my Data generated by WEBI.
I have two Queries(Query1 and Query2)
I have merge dimensions on the common dimensions.
But i am struggling to write an SQL in toad to test my data.
One Query is for LSS data and other one is for CDW data.Look in the attached doc.
Query 2 is merged with Query 1
Can anyone help me out.Data is coming from One Universe.
Merged-Queries.doc
0
Comment
Question by:bodeveloper17
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 17

Expert Comment

by:MIKE
ID: 24027916
Why not just copy paste from the actually WEBI report?

If your report is built,...just look at the query SQL and copy/paste into Toad........
0
 
LVL 16

Expert Comment

by:wykabryan
ID: 24028142
CRXIUser2005... I think he wants to test the final results, the merged query, which I do not believe Webi provides.

But he is on the write track. You are going to take both queries out of the webi document. The first query, the driver query or query1, will be your base. Set your sql statement up like the following..
select * --whatever columns here
from
query1 a,
(select * from query2 b) b--here is where you enter your second query.
where
a.column = b.column -- in the merge you have to identify like columns in the two queries, do the same here. You may need more than one join. Now pay attention to how you merge the two together. It will make a different in if you need an outer join or not.
0
 

Accepted Solution

by:
bodeveloper17 earned 0 total points
ID: 24029473
Can you explain little bit more
Ok if my Query 1 (For LSS data)
Query 2(CDW data with merged dimension with LSS).This is the driver Query which actually generates the WEBI Report.
Correct me if i am wrong, the way i am thinking to write the SQL based on you told.
Select * columns from Query 1(Column from CDW DATA which webi generates for the report) a (I think you are aliasing Query1 as "a")
(select *from Query 2b(Is the second Query for just LSS data no CDW)
WHERE
Query1.column=Query2.Column
plus all the joins from Merged dimensions.
Thanks

0
 
LVL 17

Expert Comment

by:MIKE
ID: 24029687
Just a comment:

I'm not sure what "recreating" the SQL in Toad will accomplish for you.

If you are simply trying to duplicate the data as it appears in WEBI,..then I'm sure you can do that, but it does NOT validate the data.

You need to find the single tables and work to accurately validate to Source Data.

Hope it makes sense.
0
 
LVL 16

Expert Comment

by:wykabryan
ID: 24029748
Query1.column=Query2.Column <-- this represents all of the joins that you do in the merge dimension. So there may be more than just one join, I only showed one.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
I recently went through setting up a JasperReports Server using the AWS EC2 instance, and this article will cover some basic administration tasks I had to perform.
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…

622 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question