Returning a NULL value - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Returning a NULL value

Author

Topic

jkessler43
Starting Member

2 Posts

Posted - 2012-01-03 : 16:24:14

Hello all,

I have a SQL Query that I am running that writes a file full of selected variables from different tables. One of those fields being outputted has the potential to be a null value. My problem is when there is no value I would like to write "null" to the file. How do I do this?

SELECT COALESCE((Select d.DATE
From TWO b, ONE c, THREE d
Where 1=1
and c.onething = b.onething
and d.something = b.somethingelse
and d.ID = '213'
), 'NONE') AS DATE

Conversion failed when converting date and/or time from character string.

Ultimately I want to output this to a file. I am just trying to get the output I want first. Any Ideas?

Thanks,

jkessler43
Starting Member

2 Posts

Posted - 2012-01-03 : 16:30:32

I apologize this is the error I am getting:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

X002548
Not Just a Number

15586 Posts

Posted - 2012-01-03 : 16:33:49

do you mean

SELECT COALESCE(d.date, c.date, d.DATE) AS [date]
From TWO b
INNER JOIN ONE c
ON b.onething = c.onething
INNER JOIN THREE d
ON b.something = d.somethingelse
Where d.ID = '213'

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-04 : 06:13:43

quote:
Originally posted by jkessler43

I apologize this is the error I am getting:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

check subquery and see if its returning more than a row. if yes you cant use it inside COALESCE as done now


Select d.DATE
From TWO b, ONE c, THREE d 
Where 1=1 
and c.onething = b.onething
and d.something = b.somethingelse
and d.ID = '213'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-04 : 06:15:01

quote:
Originally posted by X002548

do you mean

SELECT COALESCE(d.date, c.date, d.DATE) AS [date]
From TWO b
INNER JOIN ONE c
ON b.onething = c.onething
INNER JOIN THREE d
ON b.something = d.somethingelse
Where d.ID = '213'

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Hmm...
doesnt make much sense in last part
why put it again to d.Date which is already NULL?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-04 : 06:16:24

Actually i didnt get what OP is trying to do with subquery here

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

X002548
Not Just a Number

15586 Posts

Posted - 2012-01-04 : 09:06:23

should b.date I guess

It was a complete shot in the dark

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

X002548
Not Just a Number

15586 Posts

Posted - 2012-01-04 : 09:34:32

well..obviously it makes no sense...and I didn't (or wouldn't think) it could be done...but it's just a type conversion issue right?


SELECT COALESCE((SELECT CONVERT(varchar(25),d.DATE,102)
				   FROM TWO b, ONE c, THREE d 
				  WHERE 1=1 
					AND c.onething = b.onething
					AND d.something = b.somethingelse
					AND d.ID = '213'
				)
		, 'NONE') AS DATE

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources