Change a selected value to null

Question

Change a selected value to null

sdh96

Mr or Mrs. 500

Points: 596
More actions
December 1, 2014 at 9:21 am

#299007

I am emailing data using this:
DECLARE @xml NVARCHAR(MAX)
DECLARE @body NVARCHAR(MAX)
USE R4W_001
SET @xml = CAST(( SELECT ITEM_ID AS 'td','',"td/@align" = 'right', replace(convert(varchar,convert(Money, AVAILABLE),1),'.00','') AS 'td','',
ITEM_DESC AS 'td', '',"td/@align" = 'right',convert(varchar(10),EXPCT_DELIVERY,101) AS 'td'
FROM inventor
where PROD_CLASS_ID = 'INVENTORY'
ORDER BY ITEM_ID
FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))
SET @body ='<html><body><H3>Inventory Available Information</H3>
<table border = 1>
<tr>
<th> ITEM_ID </th> <th> Available </th> <th> DESC </th><th> EXPCT DELV </th></tr>'
SET @body = @body + @xml +'</table></body></html>'
The result displays:
ITEM_ID Available DESC EXPCT DELV
AC 16,015Apples 01/18/2015
ORG12 2,908 Oranges 12/30/2014
AHA 3,355Apricots 01/01/1753
Our systems stores 01/01/1753 when the date field is blank. In the above TSQL, how can I replace 01/01/1753 with null or blank in teh results?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

sdh96 Mr or Mrs. 500 Points: 596 More actions · Answer 1

I apologize the format of the results displayed all together. But the columns are spaced out in the real results. The columns are item_id, available, Desc, and Expct Delv.

djj SSCoach Points: 18831 More actions · Answer 2

sdh96 (12/1/2014)
I am emailing data using this:
DECLARE @xml NVARCHAR(MAX)
DECLARE @body NVARCHAR(MAX)
USE R4W_001
SET @xml = CAST(( SELECT ITEM_ID AS 'td','',"td/@align" = 'right', replace(convert(varchar,convert(Money, AVAILABLE),1),'.00','') AS 'td','',
ITEM_DESC AS 'td', '',"td/@align" = 'right',convert(varchar(10),EXPCT_DELIVERY,101) AS 'td'
FROM inventor
where PROD_CLASS_ID = 'INVENTORY'
ORDER BY ITEM_ID
FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))
SET @body ='<html><body><H3>Inventory Available Information</H3>
<table border = 1>
<tr>
<th> ITEM_ID </th> <th> Available </th> <th> DESC </th><th> EXPCT DELV </th></tr>'
SET @body = @body + @xml +'</table></body></html>'
The result displays:
ITEM_ID Available DESC EXPCT DELV
AC 16,015Apples 01/18/2015
ORG12 2,908 Oranges 12/30/2014
AHA 3,355Apricots 01/01/1753
Our systems stores 01/01/1753 when the date field is blank. In the above TSQL, how can I replace 01/01/1753 with null or blank in teh results?

Could you not just have a "CASE WHEN Expct_Delv = '01/01/1753' THEN NULL ELSE Expct_Delv END"?

sdh96 Mr or Mrs. 500 Points: 596 More actions · Answer 3

Thanks djj. Would you put the case right before the data field, or could you copy my code and put it exactly where it should go?

sdh96 Mr or Mrs. 500 Points: 596 More actions · Answer 4

sdh96

Mr or Mrs. 500

Points: 596

December 1, 2014 at 9:36 am

#1761778

I got it. Thanks so much djj.

Luis Cazares SSC Guru Points: 183706 More actions · Answer 5

djj (12/1/2014)
Could you not just have a "CASE WHEN Expct_Delv = '01/01/1753' THEN NULL ELSE Expct_Delv END"?

Why not use NULLIF() instead of the explicit CASE?

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2