Solved

How to write SQL in Toad to test the Data

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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Crystal Reports - Need help with unique page break and total line for last page 3 35
Crystal Report detail 15 58
SSRS troubles 4 84
Montis monitoring 3 20
1. Set up your parameter at the report level as usual, check the box Multi-value, and set the Data Type to String 2. Set the Stored Procedure Parameter to varchar(max)  --<---- This part here is the key to it's success Example:    @cst_key var…
I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

756 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