Simple Union - Error converting data type varchar

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

Simple Union - Error converting data type varchar

Author

Topic

rjackman1959
Yak Posting Veteran

60 Posts

Posted - 2009-01-17 : 09:45:53

I am trying to write a simple union to send a file and I am getting this error. Any help would be appreciated. Thank You
Msg 8114, Level 16, State 5, Line 3
Error converting data type varchar to numeric.

declare @parm1 varchar(67), @parm2 numeric(3)
set @parm2 = 683
select 'HDR',
@parm2,
replace(convert(varchar (8), current_timestamp, 10), '-', ''),
@parm1,
'',
'',
'',
''
union
--create proc
select 'EMP',
@parm2,
'9999999999',
' ',
' ',
' ',
' ',
' '
union
--create proc wynne_hsa_edi
--as
select 'DTL',
@parm2,
s.socscnum,
round(sum(d.uprtrxam),2),
rtrim(s.lastname) + ',' + rtrim(s.frstname),
' ',
'',
''
from upr30300 as d left join upr00100 as s on d.employid = s.employid
where d.chekdate = '2009-01-09 00:00:00.000' and d.payrolcd = 'HSA'
group by s.socscnum,
s.lastname, s.frstname

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-17 : 11:20:52

whats the datatype of s.socscnum? it should be varchar. ALso make round(sum(d.uprtrxam),2) cast(round(sum(d.uprtrxam),2) as varchar(50))

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-17 : 11:41:09

All columns in an UNIONed resultset must have compatible datatypes.

E 12°55'05.63"
N 56°04'39.26"

rjackman1959
Yak Posting Veteran

60 Posts

Posted - 2009-01-17 : 11:49:07

s.socscnum is char(15) and when I try round(sum(d.uprtrxam),2) cast(round(sum(d.uprtrxam),2) as varchar(50)) I get this error.
Msg 102, Level 15, State 1, Line 4
Incorrect syntax near 'round'.

select 'DTL',
@parm2,
s.socscnum,
round(sum(d.uprtrxam),2) cast(round(sum(d.uprtrxam),2) as varchar(50)),
rtrim(s.lastname) + ',' + rtrim(s.frstname),
' ',
'',
''
from upr30300 as d left join upr00100 as s on d.employid = s.employid
where d.chekdate = '2009-01-09 00:00:00.000' and d.payrolcd = 'HSA'
group by s.socscnum,
s.lastname, s.frstname

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-17 : 12:00:49

[code]select 'DTL',
@parm2,
s.socscnum,
cast(round(sum(d.uprtrxam),2) as varchar(50)),
rtrim(s.lastname) + ',' + rtrim(s.frstname),
' ',
'',
''
from upr30300 as d
left join upr00100 as s on d.employid = s.employid
where d.chekdate = '2009-01-09 00:00:00.000'
and d.payrolcd = 'HSA'
group by s.socscnum,
s.lastname,
s.frstname[/code]

E 12°55'05.63"
N 56°04'39.26"

rjackman1959
Yak Posting Veteran

60 Posts

Posted - 2009-01-17 : 12:13:43

Thanks I am allmost there. I'm not getting errors, now when I run it it is putting the DLT union first. I need it to put the HDR first, EMP second and finish with the DTL.

Here is the final code

declare @parm1 varchar(67), @parm2 numeric(3)
set @parm2 = 683
select 'HDR',
@parm2,
replace(convert(varchar (8), current_timestamp, 10), '-', ''),
@parm1,
'',
'',
'',
''
union
--create proc
select 'EMP',
@parm2,
'9999999999',
' ',
' ',
' ',
' ',
' '
union
select 'DTL',
'683',
s.socscnum,
cast(round(sum(d.uprtrxam),2) as varchar(50)),
rtrim(s.lastname) + ',' + rtrim(s.frstname),
' ',
'',
''
from upr30300 as d left join upr00100 as s on d.employid = s.employid
where d.chekdate = '2009-01-09 00:00:00.000' and d.payrolcd = 'HSA'
group by s.socscnum,
s.lastname, s.frstname

rjackman1959
Yak Posting Veteran

60 Posts

Posted - 2009-01-17 : 12:29:55

I got it. I forgot the union all. Thaks all for your help

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-17 : 12:46:29

UNION nor UNION ALL will guarantee the order of records in a SELECT.

DECLARE @Parm1 VARCHAR(67),
	@Parm2 NUMERIC(3)
	
SET	@Parm2 = 683

SELECT		Col1,
		Col2,
		Col3,
		Col4,
		Col5,
		Col6,
		Col7,
		Col8
FROM		(
			SELECT 'HDR' AS Col1,
				@Parm2 AS Col2,
				REPLACE(CONVERT(VARCHAR (8), GETDATE(), 10), '-', '') AS Col3,
				@Parm1 AS Col4,
				'' AS Col5,
				'' AS Col6,
				'' AS Col7,
				'' AS Col8,
				1 AS theOrder

			UNION ALL

			SELECT	'EMP',
				@Parm2,
				'9999999999',
				' ',
				' ',
				' ',
				' ',
				' ',
				2 AS theOrder

			UNION ALL

			SELECT		'DTL',
					'683',
					s.SocScNum,
					CAST(ROUND(SUM(d.uprtrxam),2) AS VARCHAR(50)),
					RTRIM(s.lastname) + ',' + RTRIM(s.frstname),
					' ',
					'',
					'',
					3 AS theOrder
			FROM		upr30300 AS d
			left join	upr00100 AS s ON d.employid = s.employid
			WHERE		d.chekdate = '2009-01-09'
					AND d.payrolcd = 'HSA'
			GROUP BY	s.socscnum,
					s.lastname,
					s.frstname
		) AS x
ORDER BY	theOrder,
		Col3

E 12°55'05.63"
N 56°04'39.26"

rjackman1959
Yak Posting Veteran

60 Posts

Posted - 2009-01-17 : 12:51:36

Thanks Peso. On the last select it is giving the result of d.uprtrxam as a 5 decimal result. Is there a way to make this only show 2 decimals?

select 'DTL',
'683',
s.socscnum,
cast(round(sum(d.uprtrxam),2) as varchar(8)),
rtrim(s.lastname) + ',' + rtrim(s.frstname),
' ',
'',
''
from upr30300 as d
left join upr00100 as s on d.employid = s.employid
where d.chekdate = '2009-01-09 00:00:00.000'
and d.payrolcd = 'HSA'
group by s.socscnum,
s.lastname,
s.frstname

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-17 : 12:55:33

whats the precision of d.uprtrxam?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-17 : 12:59:43

just post the column type from definition...it will be of form decimal(p,s) where p is precision and s will be scale

rjackman1959
Yak Posting Veteran

60 Posts

Posted - 2009-01-17 : 13:09:36

I think this is what your asking? UPRTRXAM(numeric(19,5)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-17 : 13:14:04

use:-


left(cast(round(sum(d.uprtrxam),2) as varchar(8)),case when charindex('.',cast(round(sum(d.uprtrxam),2) as varchar(8)))>0 then charindex('.',cast(round(sum(d.uprtrxam),2) as varchar(8))) else len(cast(round(sum(d.uprtrxam),2) as varchar(8)))-2 end +2)

rjackman1959
Yak Posting Veteran

60 Posts

Posted - 2009-01-17 : 13:20:55

Wow thats a lot of code and it worked perfect. Maybe someday I will understand why, but for now thank you.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-17 : 13:26:57

welcome

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-17 : 15:52:24

quote:
Originally posted by visakh16

left(cast(round(sum(d.uprtrxam),2) as varchar(8)),case when charindex('.',cast(round(sum(d.uprtrxam),2) as varchar(8)))>0 then charindex('.',cast(round(sum(d.uprtrxam),2) as varchar(8))) else len(cast(round(sum(d.uprtrxam),2) as varchar(8)))-2 end +2)

Somewhat shorter

ltrim(str(sum(d.uprtrxam), 15, 2))

E 12°55'05.63"
N 56°04'39.26"

rjackman1959
Yak Posting Veteran

60 Posts

Posted - 2009-01-17 : 16:17:17

Thank You Peso. All help is very much appreciated

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources