Query Join

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:32:52 CDT