Re: Query Join
Date: Fri, 1 Aug 2008 13:59:54 -0400
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
select customer_id from y)
in_y_only as (
select customer_id from y
select customer_id from x)
when 'P' then 'CREATED'
when 'x' then 'EXISTS'
from in_both join x on (in_both.customer_id = x.customer_id)
select in_y_only.customer_id, 'ERROR'
will produce the following:
0 ERROR 123 EXISTS 456 EXISTS 789 CREATED hth
<artmerar_at_yahoo.com> wrote in message
> 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