Solved

Adding ISNULL to this query

Posted on 2011-09-28
4
197 Views
Last Modified: 2012-06-27
I have this query which is returning a NULL value instead of the values I need.  I need to add a ISNULL to the first line, but with all the trims, lefts, I am having trouble.

Where exactly would that go?  It needs to be attached to dd.ODJCJOB
select 
			'Job: ' + LEFT(LTRIM(dd.ODJCJOB) + SPACE(16), 16) +
			'Cost Code: ' + LEFT(LTRIM(dd.ODJCPHS) + SPACE(16), 16) + 
			'Amount: ' + LEFT(LTRIM(STR(dd.ODAMT, 14, 2)) + SPACE(16), 16)
		from [ADCData].dbo.MASTER_APM_RECORD_2 dd 
		where dd.ODINV = '061711' and dd.ODVND = 'ASSURANTEM'

Open in new window

0
Comment
Question by:red_75116
  • 2
  • 2
4 Comments
 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 500 total points
ID: 36717945
select 
			'Job: ' + COALESCE(LEFT(LTRIM(dd.ODJCJOB), '') + SPACE(16), 16) +
			'Cost Code: ' + COALESCE(LEFT(LTRIM(dd.ODJCPHS), '') + SPACE(16), 16) + 
			'Amount: ' + COALESCE(LEFT(LTRIM(STR(dd.ODAMT, 14, 2)), '') + SPACE(16), 16)
		from [ADCData].dbo.MASTER_APM_RECORD_2 dd 
		where dd.ODINV = '061711' and dd.ODVND = 'ASSURANTEM'

Open in new window

0
 

Author Comment

by:red_75116
ID: 36717991
i get this error when I run this

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value 'Job: ' to data type int.
0
 

Author Closing Comment

by:red_75116
ID: 36718040
thanks!
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 36718046
Sorry, try:


select 
	'Job: ' + LEFT(LTRIM(COALESCE(dd.ODJCJOB, '')) + SPACE(16), 16) +
		'Cost Code: ' + LEFT(LTRIM(COALESCE(dd.ODJCPHS, '')) + SPACE(16), 16) + 
		'Amount: ' + LEFT(LTRIM(STR(COALESCE(dd.ODAMT, ''), 14, 2)) + SPACE(16), 16)
	from [ADCData].dbo.MASTER_APM_RECORD_2 dd 
	where dd.ODINV = '061711' and dd.ODVND = 'ASSURANTEM'

Open in new window

0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now