Oracle Set operators (union, minus, etc) can not handle blobs: Oracle Set operator union can not handle blobs. Help rewriting it using OR

Oracle Set operators (union, minus, etc) can not handle blobs

I need to rwrite this query using an OR rather than a union. I have never done that and need help on how to do it. I've been looking on Oracle PL/SQL programming book by Steven Fewerstein but have not found a good example or a good explanation on how to do it.

Your help is greatly appreciated, thanks experts!.

Select Trim(s.last_name)||', '||Trim(s.first_name) As Name, program_cd as Program, photo From sis.student_facts s, sis.rosa r, blkbrd.blkbrd_people b, sis.name n, blkbrd.photo_v v Where r.term = 'xxxx' and r.class IN ('xxxx') and s.person_id = r.person_id and b.match_ssn = trim(n.ssn) and s.person_id = b.person_id and r.person_id= b.person_id and v.person_id = r.person_id and v.person_id = b.person_id union SELECT Trim(n.last_name)||', '||Trim(n.first_name) As Name, ts.billing_program_cd AS Program, photo from blkbrd.blkbrd_people b, sis.name n, sis.SACT S,sis.term t, sis.tsta ts, blkbrd.photo_v v WHERE(b.match_ssn = Trim(n.ssn)) and S.person_id = N.person_id AND S.activity_cd IN (xxxx) and term_begin_dt <= s.activity_end_dt and term_end_dt >= s. activity_begin_dt and t.term ='xxxx' and ts.term = t.term and ts.person_id=n.person_id and v.person_id = n.person_id and v.person_id = s.person_id order by 3;

Instant Access to this Solution

Plus...

30 Day FREE access, no risk, no obligation

Collaborate with the world's top tech experts

Unlimited access to our exclusive solution database

Never be left without tech help again

Trusted by hundreds of thousands everyday for fast, accurate and reliable tech support.

"The time we save is the biggest benefit of Experts Exchange to Warner Bros. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange." Mike Kapnisakis, Warner Bros.

"Our team likes having a resource that is more secure than just using Google and most experts using this service really know their stuff. It's nice to look here first versus using Google." Dayna Sellner, Lockheed Martin

"Anytime that I've been stumped with a problem, 9 out of 10 times Experts Exchange has either the accepted solution or an open discussion of the potential solution to the problem." Kenny Red, eBay Inc.

See what Experts Exchange can do for you.

Got a question?

We've got the answer.

Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.

Screenshot of Experts Exchange Knowledgebase

Need individual assistance?

Our experts are ready to help.

If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.

Want to learn from the best?

Read articles from industry experts.

Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.

Working on a long term project?

Store your work and research.

Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.

Join the Community

Give a Little. Get a Lot.

Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.

You could try something like this:

SELECT   p.person_id, p.NAME, p.program, v.photo
    FROM (SELECT s.person_id,
                 TRIM (s.last_name) || ', ' || TRIM (s.first_name) AS NAME,
                 program_cd AS program
            FROM sis.student_facts s, sis.rosa r, sis.NAME n
           WHERE r.term = 'xxxx'
             AND r.CLASS IN ('xxxx')
             AND s.person_id = r.person_id
             AND b.match_ssn = TRIM (n.ssn)
             AND s.person_id = b.person_id
             AND r.person_id = b.person_id
          UNION
          SELECT s.person_id,
                 TRIM (n.last_name) || ', ' || TRIM (n.first_name) AS NAME,
                 ts.billing_program_cd AS program
            FROM blkbrd.blkbrd_people b,
                 sis.NAME n,
                 sis.sact s,
                 sis.term t,
                 sis.tsta ts
           WHERE (b.match_ssn = TRIM (n.ssn))
             AND s.person_id = n.person_id
             AND s.activity_cd IN (xxxx)
             AND term_begin_dt <= s.activity_end_dt
             AND term_end_dt >= s.activity_begin_dt
             AND t.term = 'xxxx'
             AND ts.term = t.term
             AND ts.person_id = n.person_id) p,
         blkbrd.photo_v v
   WHERE v.person_id = p.person_id
ORDER BY 2;

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:

Select all Open in new window

Question

Oracle Set operators (union, minus, etc) can not handle blobs

Subscribe now for full access to Experts Exchange and get

Instant Access to this Solution

Oracle Set operator union can not handle blobs. Help rewriting it using OR

PL / SQL

Oracle Database

Oracle 10.x

Trusted by hundreds of thousands everyday for fast, accurate and reliable tech support.

See what Experts Exchange can do for you.

Got a question?

We've got the answer.

Need individual assistance?

Our experts are ready to help.

Want to learn from the best?

Read articles from industry experts.

Working on a long term project?

Store your work and research.

What Makes Experts Exchange Unique?

Trusted by the world's most respected brands.

Faithfully serving IT professionals since 1996.

Related Solutions

Free Tech Articles

Cloud Class Webinars

Join the Community

Give a Little. Get a Lot.

Answers

3 Ways to Join

The Experts

The Experts

Testimonials

Testimonials

Testimonials

Business Clients

Business Clients

In the Press

In the Press

In the Press