CODEWITHSUNDEEP
sqlsql-serverssms
user11821392
user11821392

Reputation:

How to replace null field values with an empty string?

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;

Upvotes: 1

Views: 358

Answers (3)

DarkRob
DarkRob

Reputation: 3833

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;

Upvotes: 0

Daniel Doblas
Daniel Doblas

Reputation: 15

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"

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269553

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  ;

Upvotes: 2

Related Questions