PLSQL avoid code duplication

Question

I need to select from all users that are not present in a set of other sub queries:

SELECT user_id
FROM (<userSubQuery1>)
WHERE 
    user_id NOT IN (<badUserSubQueryA>) AND
    user_id NOT IN (<badUserSubQueryB>) AND
    user_id NOT IN (<badUserSubQueryC>)

Only I need to do the NOT IN filters in many different queries where the userSubQuery and the badUserSubQueries may be different. E.g.:

SELECT user_id
FROM (<userSubQuery2>)
WHERE 
    user_id NOT IN (<badUserSubQueryB>) AND
    user_id NOT IN (<badUserSubQueryC>) AND
    user_id NOT IN (<badUserSubQueryD>)

All the sub queries, both the ones I'm selecting from and the once used in the NOT IN are complex so I don't want to duplicate the code for the NOT IN sub queries which are often the same (badUserSubQueryB and badUserSubQueryC in my example).

I could achieve this with dynamic sql but I'd rather not if I can avoid it. Is it possible?

vc 74 · Accepted Answer · 2011-09-16 14:37:57Z

2

How about storing all the bad user IDs in an indexed temporary table from which you can filter?

answered Sep 16, 2011 at 14:37

vc 74

38.3k7 gold badges80 silver badges96 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Joe Over a year ago

As it's oracle, you could also use a materialized view for each of the subqueries.

APC Over a year ago

How about not using performance impacting and resource draining implementations to solve what is actually a problem with the development process?

Joel Palmert Over a year ago

I considered having a functions that returns a plsql collection with the union of the result of all the badUserSubQueries. How would that compare performance wise to putting them in a temp table?

vc 74 Over a year ago

@user948705 In this case you could create a pipelined function returning the invalid user ids as if they were coming from a table akadia.com/services/ora_pipe_functions.html

roselan Over a year ago

why not create a "isUserOk(user, type)" function? (where type is "abc" or "bcd"). but then i would use dynamic sql to add the appropriate badUserSubqueries

Dave Costa · Accepted Answer · 2011-09-16 14:47:27Z

2

How about creating a view for each complex subquery that you want to reuse?

answered Sep 16, 2011 at 14:47

Dave Costa

48.3k8 gold badges61 silver badges73 bronze badges

1 Comment

Joel Palmert Over a year ago

Ye if I also put the userSubQuery in a view that would help. But I'd still need to have a separate query (although simpler) for each combination.

Alex Peta · Accepted Answer · 2011-09-22 20:25:27Z

0

I would have a crack at it like this:

Try to think of this in reverse logic like this

SELECT user_id
FROM (<userSubQuery1>)
MINUS
(
SELECT useri_id
  FROM <badUserSubQueryB> 
UNION
SELECT useri_id
  FROM <badUserSubQueryC> 
UNION
SELECT useri_id
  FROM <badUserSubQueryD>
)

Let me know how it goes

Cheers , Alex

answered Sep 22, 2011 at 20:25

Alex Peta

1,4051 gold badge15 silver badges27 bronze badges

Collectives™ on Stack Overflow

PLSQL avoid code duplication

3 Answers 3

5 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related