Home » Other » Client Tools » Need help Urgent.....
Need help Urgent..... [message #38512] Wed, 24 April 2002 08:59 Go to next message
shreya
Messages: 15
Registered: March 2001
Junior Member
Hi,

I need to write a pl/sql procedure for the following
requirements. Please suggest me how can i write this
using pl/sql if possible send me a code snippet.
I will be very thakful to u.
Here is spec.

[[Problem]]
In current SS testing, ECT provide the exact comparison. It's fine with short regression test. But for the long regression test, it is impractical to expect QA to look into every difference. Therefore, QA only check a few records and leave most (probably 99%) of difference alone. This could miss some differences which caused by bugs.

[[Proposal]]
It will compare the result from both schema in a FUZZY method. For example, you can say give me the difference only when two numbers are 1% (or 5%) different. In this case, QA is able to narrow down hundreds of thousand of differences to only a handful.

-------------------------------------------------------- <2> example
[[Example]]
Compare the balance of invoices between 2 schema.

[[Input]]
Table invoice. The column to compare is 'amount'.

[[output]]
A diff table which contains all customer/invoice with difference more than specified tolerance (for example 5%).

-------------------------------------------------------- <3> usage
One possible to way to use this tool is drilling down methodology.
For example, I find out all customer/invoice with balances more than 5% difference. Then I drill down to the next level to find out which item(s) cause the difference (previous balance, monthly charge, or total usage charge). If I find out the difference is from 'total usage charge', then I further drill down to find out those CDR which causes the difference.
This tool should provide ability for each level of comparison. And QA can specify the degree of tolerance in each level.

Thanks in advance,

Smith.
Re: Need help Urgent..... [message #38513 is a reply to message #38512] Wed, 24 April 2002 10:11 Go to previous messageGo to next message
oraboy
Messages: 97
Registered: October 2001
Member
I am not sure whether I have understood ur need correctly..
anyways I am going ahead with what I know..

Say there are two schemas A and B
and the table name is Invoice and the column name to compare is amount and the tolerance level is 5

From A
-------
SQL> Select a.Invoice_id,a.amount from a.Invoice A,
b.invoice B
where a.invoice_id=b.invoice_id and
abs((a.amount-b.amount)/a.amount)*100 > 5

By the way, I dont understand why do you need PLSQL for this!

Let me know if I have misunderstood ur requirement

Regards
Oraboy
Re: Need help Urgent..... [message #38525 is a reply to message #38512] Wed, 24 April 2002 12:46 Go to previous messageGo to next message
oraboy
Messages: 97
Registered: October 2001
Member
Hi
I think its very much doable.

Can u mail me with your table structure(s) and hierarchies so that I can have an idea on how you wanna drill down

Oraboy
Re: Need help Urgent..... [message #38544 is a reply to message #38525] Thu, 25 April 2002 14:27 Go to previous messageGo to next message
shreya
Messages: 15
Registered: March 2001
Junior Member
Hi Oraboy,

Here is my table structure.
Name Null? Type
------------------------------- -------- ----
USER_ID NOT NULL VARCHAR2(128)
ACCOUNT_CYCLE_ID NOT NULL VARCHAR2(32)
USAGE_ACCUM_VALUE NUMBER(10,2)
USAGE_ACCUM_TIME NUMBER
MODIFY_TIME DATE
CYCLE_START_DATE DATE
CYCLE_END_DATE DATE
USAGE_ACCUM_C1_TYPE VARCHAR2(20)
TRANS_NUM NUMBER
USAGE_ACCUM_C1 NUMBER(10,2)
TRANS_VALUE NUMBER(10,2)
USAGE_ACCUM_C2_TYPE VARCHAR2(20)
USAGE_ACCUM_C2 NUMBER(10,2)

CREATE TABLE my_table as
SELECT a.*
FROM schema1.user_account_cycle@x1 a,
schema2.user_account_cycle@x2 b
WHERE
a.user_id = b.user_id
and a.usage_accum_value != b.usage_accum_value
and ABS(a.usage_accum_value-b.usage_accum_value) / DECODE(a.usage_accum_value,0,1,a.usage_accum_value) >= &tolerance

I find out all users with user_accum_value more than 5% difference. Then I drill down to the next level to find out which item(s) cause the difference (usage_accum_time,trans_value). If I find out the difference is from 'usage_Accum_time' then I further drill down to find out those trans_value which causes the difference.

I think if u read it properly u can understand easily.
If u could send me the solution that would be great.

Thanks,
Smith.
Now i have to again drill down to USAGE_ACCUM_TIME
Re: Need help Urgent..... [message #38622 is a reply to message #38525] Wed, 01 May 2002 06:47 Go to previous message
oraboy
Messages: 97
Registered: October 2001
Member
Hi Smith
Well still I havent come up with any general procedure for your problem. But just thought atleast I can post this since you seem to looking for an urgent solution.

I think I can hint my my idea..See to that whether it helps u.

As per your post , you did create a table (My_table) having all the rows more than 5% difference in User_accum_value.

Why cant you join that table itself and schema2.user_account_cycle@x2 again to see what are the rows which has more than 5% difference in USAGE ACCUM_TIME

The query will be like this..

a) Create table my_table
SELECT a.*
FROM schema1.user_account_cycle@x1 a,
schema2.user_account_cycle@x2 b
WHERE
a.user_id = b.user_id
and a.usage_accum_value != b.usage_accum_value
and ABS(a.usage_accum_value-b.usage_accum_value) / DECODE(a.usage_accum_value,0,1,a.usage_accum_value) >= &tolerance

b) Select a.rowid,a.primarykeyid from my_table a,schema2.user_account_cycle@x2 b
Where
a.user_id = b.user_id
and a.USAGE ACCUM_TIME != b.USAGE ACCUM_TIME
and ABS(a.USAGE ACCUM_TIME-b.USAGE ACCUM_TIME) /
DECODE(a.USAGE ACCUM_TIME,0,1,a.USAGE ACCUM_TIME) >= &tolerance

c) use the same technique to furthur drill down

Regards
Oraboy
Previous Topic: What does it mean ?
Next Topic: outer join help needed urgent..
Goto Forum:
  


Current Time: Thu Mar 28 10:09:46 CDT 2024