Adding ISNULL to this query

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

red_75116Asked:
Who is Participating?
 
Patrick MatthewsConnect With a Mentor Commented:
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
 
red_75116Author Commented:
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
 
red_75116Author Commented:
thanks!
0
 
Patrick MatthewsCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.