Re: Select one field/value from two different tables

On Aug 5, 10:14 am, JBond007 <nafis..._at_yahoo.com> wrote:
> On Aug 4, 7:12 pm, t..._at_panix.com (Dan Blum) wrote:
>
>
>
> > I'm amazed that that actually parses.
>
> .....I got the same response from some other programmers in my
> company,
> .....including a DBA who, unfortunately, doesn't know code.
> .....Unfortunately, what worked in SQLPlus, from my original msg,
> doesn't work in Pro*C,
> .....which complains about the word "oldschool" in the larger query's
> concatentation.
> .....Neither SQLPlus nor Pro*C liked the union in the sub-select or
> the word "coalesce",
> .....in someone else's response.
> .....I also tried if, case, and decode, to no avail (they worked, but
> didn't give me what I wanted.
> .....I can't believe this is as complicated as it seems!
>
>
>
> > In any case, what you want to do is replace the concatentation operator
> > with UNION - or, if the tables are really mutually exclusive, UNION ALL
> > (UNION removes duplicates, which if you don't have any duplicates
> > just wastes time). E.g.,
>
> > (select oldschool from old where pkey = 12345
> >  union
> >  select newschool from new where pkey = 12345)- Hide quoted text -
>
> - Show quoted text -

What version of Oracle? Coalesce is an ANSI standard function available since version 9 and works just fine on our system which for the purpose of my test was 9.2.0.6 on AIX 5.3.

Since you are having trouble putting your full query together try writing the different parts separately then merging them into one SQL statement. Consider inline views, scalar subqueries, union | union all, case | decode depending on your version when you determine how to put the working pieces together into one unified query.

You should be able to select from new union all select from old and join this to whatever other queries you need if your version does not support sclar sub-queries.

HTH -- Mark D Powell -- Received on Wed Aug 06 2008 - 11:04:38 CDT