Don't show null

[Ringers]

Hi All,

I have a SQL script that uses select, CTE and a whole bunch of case statements.(see attachment link)

The script is fine and returns everything I need, But it shows null when there is no data. I have datatypes datetime,int and varchar. Is there a way to show a empty or blank field rather than null for all these datatypes? Becuase the application I am uploading to doesn't like null.

Thanks

 

[Ringers]

This is in SQL 2005 SSMS, thanks.

 

[dhulbert]

try isnull(fieldname, '')

to display blank rather thn null.

I love deadlines. I like the whooshing sound they make as they fly by
Douglas Adams
(1952-2001)

 

[TheBugSlayer]

The COALESCE function returns the firt non-null value among its arguments. From BOL

COALESCE(expression1,...n) is equivalent to the following CASE expression:

CASE

WHEN (expression1 IS NOT NULL) THEN expression1

WHEN (expression2 IS NOT NULL) THEN expression2

...

ELSE expressionN

END

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)

 

[Olaf Doschke]

I'd add: Changing NULL to an empty string might be okay for text fields, but a NULL in a numeric field has a differnt meaning than 0. You might want another replacement value than NULL for these cases, it depends very much on the nature of the data, what replacement value would be ok. For dates a replacement might be a date in the past, eg 01/01/1899, or in the future, eg 31/12/9999.

You know the destination system better than us, to decide what would be wrong, it might also be a reason to investigate why NULLs come from the source data.

Bye, Olaf.

 

[Ringers]

Thanks for the reply's all.

Is there a way to change the field in the select statement?

it is the "optional_3l AS [Division Code]" I would really prefer to not show NULLS.

E.G
select case.......
END AS [Supplier Code],Account,Optional_6 AS [Job Number], Optional_8 AS [Cost Type Code],Department,Optional_7l AS [Employee Code],linetext AS [Description], Approver AS [Completed By],
optional_3l AS [Division Code], approvedatel AS [BCC Upload Date], optional_5l AS [Client Code],remarks,amount_2l AS [Total Excl GST], amount3l AS [GST], linetotal AS [Total Incl GST], line_id

 

[Ringers]

I got it working, I had to put the AS outside the ISNULL function.

ISNULL(optional_3l, '') AS [Division Code]

Thanks all for replies.

 

[TheBugSlayer]

Cool. Good point Olaf.

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)