sql server programming: 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,

Very difficult to tell exactly what you want without DDL and data to
test with, but see if this works...



SELECT a.cdecode as cdecode,
a.cdename as cdename,
a.cdecallid as cdecallid,
a.cderecID as cderecid,
CASE WHEN a.cdecode IS NULL THEN b.cdecode END as bcdecode,
CASE WHEN a.cdename IS NULL THEN b.cdename END as bcdename,
CASE WHEN a.cdecallid IS NULL THEN b.cdecallid END as
bcdecallid,
CASE WHEN a.cderecID IS NULL THEN b.cderecid END as bcderecid
FROM shrcodemaster AS b
FULL JOIN shrcodemaster AS a ON a.cdeRecID = b.cdeRecID
AND a.cdeCallID = 'B'

Brandon
--
"In the beginning the universe was created. This has made a lot of
people very angry, and has been widely regarded as a bad move." -
Douglas Noel Adams (1952-2001)

select a.cdecode as cdecode,
a.cdename as cdename,
a.cdecallid as cdecallid,
a.cderecID as cderecid,
nullif(a.cdecallid,'B')+b.cdecode as bcdecode,
nullif(a.cdecallid,'B')+b.cdename as bcdename,
nullif(a.cdecallid,'B')+b.cdecallid as bcdecallid,
nullif(a.cdecallid,'B')+b.cderecid as bcderecid
from shrcodemaster a
right outer join shrcodemaster b
on a.cdeRecID = b.cdeRecid
and a.cdecallid = 'B'

sorry,try this:

select a.cdecode as cdecode,
a.cdename as cdename,
a.cdecallid as cdecallid,
a.cderecID as cderecid,
COALESCE('',nullif(a.cdecallid,'B'))+b.cdecode as bcdecode,
COALESCE('',nullif(a.cdecallid,'B'))+b.cdename as bcdename,
COALESCE('',nullif(a.cdecallid,'B'))+b.cdecallid as bcdecallid,
COALESCE('',nullif(a.cdecallid,'B'))+b.cderecid as bcderecid
from shrcodemaster a
right outer join shrcodemaster b
on a.cdeRecID = b.cdeRecid
and a.cdecallid = 'B'