Rodrigo De León wrote:
> On 9/7/07, Chansup Byun <Chansup.Byun@sun.com> wrote:
>
>> Can someone show me an example SQL statement?
>>
>
> I suppose you could add a constant, non-overlapping number to add to
> the duplicate IDs, say 1000, and then this:
>
> SELECT COALESCE(T1.U_USER, T2.U_USER) AS U_USER
> , COALESCE(CASE
> WHEN EXISTS(SELECT 1
> FROM TABLEB
> WHERE U_ID = T1.U_ID
> AND U_USER <> T1.U_USER)
> THEN T1.U_ID + 1000
> ELSE T1.U_ID
> END
> , T2.U_ID
> ) AS U_ID
> FROM TABLEA T1 FULL JOIN TABLEB T2 ON T1.U_USER = T2.U_USER
>
> will generate a new list of U_USERs and U_IDs.
>
> Good luck.
>
This is cool!
What I wanted is shown below.
One more question: Is there a way to make the T2.U_ID + 1000 number to be incremental from a given number instead of
adding1000?
SELECT COALESCE(T1.U_USER, T2.U_USER) AS U_USER
, COALESCE(CASE
WHEN EXISTS(SELECT 1
FROM TABLEB
WHERE U_ID = T1.U_ID
AND U_USER <> T1.U_USER)
THEN T1.U_ID
ELSE T1.U_ID
END
, T2.U_ID + 1000
) AS U_ID
FROM TABLEA T1 FULL JOIN TABLEB T2 ON T1.U_USER = T2.U_USER
thanks,
- Chansup