SQL help

[sameer11]

After looking at the question people ask...My Q looks really silly and stupid of me asking..but I have to ask

Using oracle databse 9i

I need to use 2 view as tables and pull the following

view1 fields:

date,id,descr,type,src,category,cat descr, amt

view2 fields:
date,id,descr,type,src,category,cat descr, amt

Id, date are fields to join the views

Basically I will need to write other view using the above 2 view to create a report as include a status column which tells.

data missing from view1
data missing from view2
Matched (if view1.amt = view2.amt)
Unmatched (if view1.amt <> view2.amt)

Please advice how I can do this in a best simple way keep performance in mind.

Any help is greatly appericated


Thanks,
Sameer

 

[SantaMufasa]

Sameer,

Following is a very fast performing view that gives you your first two categories of data (and we can add categories 3 and 4 once I understand better what you want for those categories):

create view Exception_view as
(select 'Data missing from view1', view2.* from view2
minus
select 'Data missing from view1', view1.* from view1)
union
(select 'Data missing from view2', view1.* from view1
minus
select 'Data missing from view2', view2.* from view2);

Now, for your Items 3 and 4: What do you mean by "Matched" and "Unmatched"? Please explain the specific algorithm to use for both Matching and Unmatching.

Let us know your reactions to the Exception_view as it presently runs.


Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[sameer11]

For Matched case:
view1.id = view2.id
and view1.date = view2.date
and (view1.amt = view2.amt)

For Unmatched case:
view1.id = view2.id
and view1.date = view2.date
and (view1.amt <> view2.amt)

Currently Iam doing a this and wanted to check if there any other better way to do and also be very fast with performance.Iam just include what Iam doing include the really sql would be lot of scrolling down.

Iam doing

select * from V1,(select 'missing in v2' from
outer join of vewi1 and view2 - equi joing of view1 and view2
union all
select 'Matched' from
(select id, date,sum(amt) from v1 group by id,date)A1
(select id, date,sum(amt) from v2 group by id,date)A2
where a1.id = a2.id and a1.date=a2.date and a1.amt = a2.amt)
union all
select 'Unmatched' from
(select id, date,sum(amt) from v1 group by id,date)A1
(select id, date,sum(amt) from v2 group by id,date)A2
where a1.id = a2.id and a1.date=a2.date and a1.amt <> a2.amt)) TestV2
where v1.id = TestV2.id and v1.date - testv2.id
union all

repeat the whole thing from above ther other way
to get the missing in V1, matched and unmatched
Hope you understand what Iam saying here, I didn't know a better way to explain than this.. I try my best

Thanks,
Sameer

 

[SantaMufasa]

Sameer,

I have some questions for you:

1) How often must you run this report?
2) How long does the query take to execute?
3) What are your expecations for how long the query should take?

Consideration 1: If the report run infrequently, you shouldn't worry too much about how long the query takes.

Consideration 2: If the report runs frequently and the time that it takes to run the report cause a revenue loss or an unmanagable expense increase, then you shouldn't worry too much about that situation either. (For example, how much improved speed must the query have to compensate for the salary time that you have spent already in trying to speed up the query?)

More important than speed is that you are receiving correct results...No use in have a fast query that gives you bad results, right?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[sameer11]

Dave,

Didn't know what to say.I sure wouldn't have worried for any of the consideration you have mention if I had, not had a family and myself to feed and keep saving from been kicked off the work.

So, what you mean to say is that it looks good, If I didn't undersatnd you wrong.

Well, As far as your questions
1. This report run every day(atleast 3 times) could be more.
2. I have every less data in Dev and runs for about 2 mins and sure it will take longer after the report hits the Prod with huge data.
3. As little time as I have do it, as per user under a min ( iam sure it wouldn't)

Thanks you again,
Sameer

 

[sameer11]

Dave,

I really appericate your favor, I tried searching online but dind't find one. I need to write upa doc for unit testing plan. I have never done this before. I understand How I do this search company doc but use.

Thanks for all you help,
Sameer

 

[SantaMufasa]

So, Sameer, do you need my/our help? If so, what help, specifically do you need? What are your time constraints for a resolution to your outstanding needs? (It's currently close of business on Friday.)



Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]