Re: Query Join

From: cc <chris.colclough_at_nospam.jhuapl.edu>
Date: Fri, 1 Aug 2008 13:59:54 -0400
Message-ID: <g6vius$1le$1@aplnetnews.jhuapl.edu>


Tested in
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi

PL/SQL Release 10.2.0.1.0 - Production

CORE 10.2.0.1.0 Production

TNS for Linux: Version 10.2.0.1.0 - Production

NLSRTL Version 10.2.0.1.0 - Production

This solution assumes that X.C2 will *never* have values other than 'P' or 'x' (bad assumption). However....

with in_both as (

select customer_id from x

intersect

select customer_id from y)

,

in_y_only as (

select customer_id from y

minus

select customer_id from x)

select in_both.customer_id,

case x.c2

when 'P' then 'CREATED'

when 'x' then 'EXISTS'

else x.c2

end

from in_both join x on (in_both.customer_id = x.customer_id)

union

select in_y_only.customer_id, 'ERROR'

from in_y_only

will produce the following:

CUSTOMER_ID CASEX.C2WHEN'P'THEN'CREATED'WH

  • ------------------------------

0 ERROR 123 EXISTS 456 EXISTS 789 CREATED hth

<artmerar_at_yahoo.com> wrote in message
news:b0edb153-9b95-4181-a96c-9c45744c9137_at_i76g2000hsf.googlegroups.com...
>
> Hi,
>
> I have 2 tables:
>
> CUSTOMER_ID C1 C2
> 123 ABC x
> 456 DEF x
> 789 GHI P
>
> SQL> select * from y;
>
> CUSTOMER_ID C1
> 123 ABC
> 456 DEF
> 789 GHI
> 0 XXX
>
> I've been using a query like this, which is almost worksing:
> SELECT x.customer_id, x.c1, DECODE(c2, 'P', 'CREATED', NULL,
> 'ERROR', 'EXISTS') result
> FROM x, y
> WHERE x.c1(+) = y.c1;
>
> Basically if the CUSTOMER_ID is in both tables, then DECODE the C2
> column in table X. If the C2 column contains a P, then print
> 'CREATED'. If the record in x does not exist in y, then print
> 'ERROR', otherwise print 'EXISTS'.
>
> So, above, 123 is in both tables and has an 'x' in C2, so it should
> get 'EXISTS. The same holds true for 456. 789 however exists in both
> tables but has a 'P' in C2, so it should get 'CREATED'. And, 0 only
> exists in y, so it should get 'ERROR'.
>
> Does that make sense? Maybe a more analytical function would work?
Received on Fri Aug 01 2008 - 12:59:54 CDT

Original text of this message