How to replace null field values with an empty string?

Question

I need to replace the null values in the Estimate Number field with a blank string. I have tried the code below and the values still appear as null. Any thoughts? thanks

Pro Number      Estimate Number

10271943        NULL
10271944        NULL
10271945        NULL
10271946        NULL
10271948        94606


SELECT a.AAAREFNUMVALUE AS "Pro Number",
    (SELECT TOP 1 isnull(a2.AAAREFNUMVALUE,'') 
    FROM dbo.AAATOREFNUMS a2
    WHERE a2.AAATRANSPORTTABLE  = a.AAATRANSPORTTABLE  AND 
    a2.AAAREFNUMTYPE = 4 
    ORDER BY a2.AAAREFNUMVALUE
    ) AS "Estimate Number"

FROM dbo.AAATOREFNUMS a 
INNER JOIN dbo.AAATODATES d ON a.AAATRANSPORTTABLE = d.AAATRANSPORTTABLE

WHERE a.AAAREFNUMTYPE = 1 AND d.AAADATETYPE = 1  
GROUP BY a.AAAREFNUMVALUE,a.AAATRANSPORTTABLE,a.AAAREFNUMTYPE;

Gordon Linoff · Accepted Answer · 2019-08-05 16:53:37Z

2

The problem occurs when the subquery returns no values. The isnull() needs to go outside the subquery:

ISNULL( (SELECT TOP 1 a2.AAAREFNUMVALUE
         FROM dbo.AAATOREFNUMS a2
         WHERE a2.AAATRANSPORTTABLE  = a.AAATRANSPORTTABLE  AND 
               a2.AAAREFNUMTYPE = 4 
         ORDER BY a2.AAAREFNUMVALUE
        ), ''
      ) AS "Estimate Number"

Note that this is a situation where ISNULL() is preferred over COALESCE(), because the SQL Server implementation of COALESCE() evaluates the first argument twice when it is not NULL.

However, you might find the query easier to express and faster to run if you use window functions instead:

SELECT DISTINCT a.AAAREFNUMVALUE AS "Pro Number",
       COALESCE(a.AAAREFNUMVALUE_4, '') as "Estimate Number"
FROM (SELECT a.*,
             MAX(CASE WHEN a.AAAREFNUMTYPE = 4 THEN a.AAAREFNUMVALUE END) OVER (PARTITION BY a.AAATRANSPORTTABLE) as AAAREFNUMVALUE_4
      FROM dbo.AAATOREFNUMS a
     ) a INNER JOIN
     dbo.AAATODATES d
     ON a.AAATRANSPORTTABLE = d.AAATRANSPORTTABLE
WHERE a.AAAREFNUMTYPE = 1 AND d.AAADATETYPE = 1  ;

answered Aug 5, 2019 at 16:53

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user11821392 Over a year ago

Thanks, Gordon. You're always quick to help.

Daniel Doblas · Accepted Answer · 2019-08-05 16:57:11Z

1

Try case, just make the adjusts:

CASE 
(SELECT TOP 1 a2.AAAREFNUMVALUE
    FROM dbo.AAATOREFNUMS a2
    WHERE a2.AAATRANSPORTTABLE  = a.AAATRANSPORTTABLE  AND 
    a2.AAAREFNUMTYPE = 4 
    ORDER BY a2.AAAREFNUMVALUE) as Estimate Number
  WHEN NULL THEN ''
  ELSE 
    a2.AAAREFNUMVALUE
END as "Estimate Number"

answered Aug 5, 2019 at 16:57

Daniel Doblas

154 bronze badges

Comments

DarkRob · Accepted Answer · 2019-08-05 17:08:41Z

0

You may try this.

SELECT a.AAAREFNUMVALUE AS "Pro Number",
    ISNULL((SELECT TOP 1 isnull(a2.AAAREFNUMVALUE,'') 
    FROM dbo.AAATOREFNUMS a2
    WHERE a2.AAATRANSPORTTABLE  = a.AAATRANSPORTTABLE  AND 
    a2.AAAREFNUMTYPE = 4 
    ORDER BY a2.AAAREFNUMVALUE
    ),'') AS "Estimate Number"

FROM dbo.AAATOREFNUMS a 
INNER JOIN dbo.AAATODATES d ON a.AAATRANSPORTTABLE = d.AAATRANSPORTTABLE

WHERE a.AAAREFNUMTYPE = 1 AND d.AAADATETYPE = 1  
GROUP BY a.AAAREFNUMVALUE,a.AAATRANSPORTTABLE,a.AAAREFNUMTYPE;

answered Aug 5, 2019 at 17:08

DarkRob

3,8231 gold badge12 silver badges28 bronze badges

Collectives™ on Stack Overflow

How to replace null field values with an empty string?

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related