COALESCE and Replacing Empty Strings

[NWChowd]

Hi Everyone,

I have a need to replace any empty values in a certain fileld with a Null value. I need to do this on the fly in a fairy extensive (for me anyway) SQL Query. here's the portion of the query I a having troubles with:

...
CASE
WHEN a.formcd = 'U'
THEN COALESCE(c.diagcd1, a.diagn1)
ELSE COALESCE(d.diagn1,a.diagn1)
END as "Diag1",
...


the problem is that sometimes the c.diagcd1 value is an empty string ('') and sometimes its a null value.
logicaly, I need to display c.diagcd1 only if the value is non-null and not an empty string, otherwise I need to display a.diagn1.


here's the entire query:

--CLAIMS
SELECT a.claimno,a.[lineno],a.membno,a.grpnum,a.grpnum AS "Employer",SPACE(5) AS "RegionID",a.statcd,a.provno,a.vendor,a.pcpcod,a.chknum,a.authno,
CASE
WHEN dateadd(year, datediff (year, b.bthdat,GETDATE()), b.bthdat) > GETDATE()
THEN datediff (year, b.bthdat,GETDATE()) - 1
ELSE datediff (year, b.bthdat,GETDATE())
END as "Member Age",
b.sexcod,SPACE(5) AS "RefPRovID",a.recdate,a.sysdat,a.pidate,a.svcdat,a.enddat,a.svccod,a.modcod,
CASE
WHEN a.formcd = 'U'
THEN COALESCE(c.diagcd1, a.diagn1)
ELSE COALESCE(d.diagn1,a.diagn1)
END as "Diag1",
CASE
WHEN a.formcd = 'U'
THEN COALESCE(c.diagcd2,a.diagn2)
ELSE COALESCE(d.diagn2,a.diagn2)
END as "Diag2",
CASE
WHEN a.formcd = 'U'
THEN COALESCE(c.diagcd3,a.diagn3)
ELSE COALESCE(d.diagn3,a.diagn3)
END as "Diag3",
CASE
WHEN a.formcd = 'U'
THEN c.diagcd4
ELSE d.diagn4
END as "Diag4",
a.poscod,a.deneop,a.unitct,COALESCE(a.claamt,0) AS "ClaimAmount",
(COALESCE(a.alwamt,0) - COALESCE(a.dscamt,0)) AS "AllowedAmount",COALESCE(copamt,0) AS "CopayAmount",SPACE(5) AS "COB",
COALESCE(a.dscamt,0) AS "DiscountAmount",COALESCE(a.whdamt,0) AS "WithholdAmount",COALESCE(a.to_pay,0) AS "Netpaid",SPACE(5) AS "CapFlag",
a.prvcpy,a.altclm,c.admdat,c.disdat,a.biltyp,SPACE(5) AS "RiskFlag",a.compno AS "CompanyNo",COALESCE(a.preamt,0) as "Prepaid"
FROM claimlin a
LEFT OUTER JOIN demograp b ON a.membno = b.membno
LEFT OUTER JOIN claimext c ON a.claimno = c.claimno
LEFT OUTER JOIN hcfaext d ON a.claimno = d.claimno
WHERE UPPER(a.statcd) = 'P' AND
a.svccod NOT IN ('COINON','COINS','DEDOON','DEDUCT') AND
a.svccod NOT LIKE 'BA%' AND
(COALESCE(a.alwamt,0) - COALESCE(a.dscamt,0)) >= 0 AND
COALESCE(a.claamt,0) >= 0


So how do I do this?? I know it can be done. I think I am just forgetting something obvious.

Thanks,
DMill

======================================
"I wish that I may never think the smiles of the great and powerful a sufficient inducement to turn aside from the straight path of honesty and the convictions of my own mind."
-David Ricardo, classical economist
======================================

 

[ESquared]

CASE
    WHEN a.formcd = 'U'
    THEN    COALESCE(NullIf(c.diagcd1,''), a.diagn1)
    ELSE    COALESCE(d.diagn1,a.diagn1)
END as "Diag1",

-------------------------------------
There are 10 kinds of people in the world: those who know binary, and those who don't.

 

[NWChowd]

perfect! that's exactly what I was looking for. I knew it was straight-forward.

Thanks ESquared
DMill

======================================
"I wish that I may never think the smiles of the great and powerful a sufficient inducement to turn aside from the straight path of honesty and the convictions of my own mind."
-David Ricardo, classical economist
======================================

 

[ESquared]

You could have used more CASE statements but I figured that just modifying what you already had would work, too...

-------------------------------------
There are 10 kinds of people in the world: those who know binary, and those who don't.

 

[NWChowd]

Using NULLIF() was definitely the cleanest, most straight-froward. thanks again.

======================================
"I wish that I may never think the smiles of the great and powerful a sufficient inducement to turn aside from the straight path of honesty and the convictions of my own mind."
-David Ricardo, classical economist
======================================