Solved

How to write SQL in Toad to test the Data

Posted on 2009-03-30
5
1,043 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
  • 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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
Hello, In my precious Article  (http://www.experts-exchange.com/Database/Reporting/A_15280-Create-Project-in-Microstrategy-Part-I.html)we saw the Configuration part for Microstrategy which included Metadata Creation and DataSource Preparation as …
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

758 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now