Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

counting orders not in another table 2

Status
Not open for further replies.

26point2

Programmer
Jun 1, 2003
18
US
Hi there,

I have a metric that counts orders in the customer fact table. I'd like to modify the count to exclude order id's that are in the suspended user fact table. I haven't been successful playing with count parameters, and I'm not sure about importing a list of 400K orders to be used as a filter. The SQL I'd like to have is basically:

select count distinct(C.order_id)
from customer_fact C
where... (a bunch of filters on attributes for table C and some other tables)...

and not exists (select 1 from customer_suspend S
where C.order_id=S.order_id
and S.startdate<20030901
and S.enddate>20030901)


When I copy my original metric and change the parameters to count on this second table, I get the error that the facts are not at a level that can be supported, which probably is because the second fact table doesn't have the same attributes as the first one. The first fact table has millions of records, so it wouldn't be easy to create a new fact table. We're on 7.2.1./Oracle. What do you suggest as the best approach?

Much appreciated,
26point2
 
Here is a suggestion unless this is what you meant when you said &quot;importing a list of 400k orders as a filter...&quot; I think what you could try is an ApplyComparison function, like so, ApplyComparison(&quot;#0 Not In (SELECT DISTINCT s.order_id FROM customer_suspend s, customer_fact f WHERE s.order_id = f.order_id AND s.startdate < 20030901 AND s.enddate > 20030901)&quot;,Order@ID). Then place that filter on the count metric you're creating.

If that isn't what you had in mind, instead of creating a new table, you can always try a view, which DBAs like, because it doesn't take up space, and you could conceivably optimize it so that it resolves fast enough. That view could exclude orders based on whatever logic you want to apply.

HTH,
Nate
 
Very nice solution Nate. It avoids using a correlated subquery but still provides the answer required.

Another more generic way to model it is to add &quot;suspended orders&quot; as an attribute. Have it keyed off customer_suspend.order_id Make sure that it does not key off any other table.

Then add a filter defined as order not equal to &quot;suspended orders&quot;

Now you can also create queries to analyze suspended orders...
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top