Oh man! It took me more than half an hour to parse your script and its
output. Please write your codes more readable so people don't give up
understanding your problem :-)
Anyway, your desired result set didn't make sense to me, but I just assume
what you have written is really what you need. So this is probably the query
you want:

select a.cdecode,
a.cdename,
a.cdecallid,
a.cderecID,
case when a.cdecallid = 'B' then NULL else b.cdecode end as
bcdecode,
case when a.cdecallid = 'B' then NULL else b.cdename end as
bcdename,
case when a.cdecallid = 'B' then NULL else b.cdecallid end as
bcdecallid,
case when a.cdecallid = 'B' then NULL else b.cderecid end as
bcderecid
from shrcodemaster a right outer join shrcodemaster b on a.cdeRecID =
b.cdeRecid and a.cdecallid = 'B'

I hope it work...

Good luck,
Shervin


"Omavlana" <kiran@boardroomlimited.com> wrote in message
news:b14098ab.0310021857.4de400f5@posting.google.c om...
> Hi, I want to display the results of the following query in my
> program.
>
>
> select a.cdecode as cdecode, a.cdename as cdename, a.cdecallid as
> cdecallid, a.cderecID as cderecid, b.cdecode as bcdecode, b.cdename as
> bcdename, b.cdecallid as bcdecallid, b.cderecid as bcderecid
> from shrcodemaster a
> right outer join shrcodemaster b
> on a.cdeRecID = b.cdeRecid
> and a.cdecallid = 'B'
>
>
> The query is giving me the following results
>
> cdecode cdename cdecallid cderecid bcdecode bcdename bdecallid
> bcderecid
> MYR Malaysian ringgit B 2 MYR Malaysian ringgit B
> 2
> NULL NULL NULL NULL AUD Australian dollar S 3
> USD US dollar B 4 USD US dollar B 4
> CNY Chinese yuan B 5 CNY Chinese yuan B
> 5
> NULL NULL NULL NULL JPY Japanese yen (1) S 6
> IDR Indonesian Rupiah B 7 IDR Indonesian Rupiah B 7
> TWD New Taiwan dollar B 8 TWD New Taiwan dollar B 8
> HKD Hong Kong dollar B 9 HKD Hong Kong dollar B 9
> NULL NULL NULL NULL sgl Singapore S 10
> NULL NULL NULL NULL MY Malaysia S 11
> CN China B 12 CN China B 12
>
> How can I change my query so that for a cderecid, if cdecallid is 'B'
> then it should return NULL values for bcdecode, bcdename, bcdecallid,
> bcderecid.
>
> Just like for a bcdrecid, if bcdecallid is S, it is returning NULL
> values for cdecode,cdename,cdecallid, cderecid.
>
> Pls assist me. Its quite urgent.
>
> I want out put similar to
>
> cdecode cdename cdecallid cderecid bcdecode bcdename bdecallid
> bcderecid
> MYR Malaysian ringgit B 2 NULL NULL NULL
> NULL
> NULL NULL NULL NULL AUD Australian dollar S 3
> USD US dollar B 4 NULL NULL NULL
> NULL
> CNY Chinese yuan B 5 NULL NULL NULL
> NULL
> NULL NULL NULL NULL JPY Japanese yen (1) S 6
> IDR Indonesian Rupiah B 7 NULL NULL NULL
> NULL
> TWD New Taiwan dollar B 8 NULL NULL NULL
> NULL
> HKD Hong Kong dollar B 9 NULL NULL NULL
> NULL
> NULL NULL NULL NULL sgl Singapore S 10
> NULL NULL NULL NULL MY Malaysia S 11
> CN China B 12 NULL NULL NULL NULL
>
> Regards,
> Omavlana