Self Joins

[Omavlana]

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

[Shervin]

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" <ki***@boardroomlimited.com> wrote in message
news:b1**************************@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