Compare character to an integer field

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

SQL Server 2005 Forums

Transact-SQL (2005)

Compare character to an integer field

Author

Topic

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2008-11-21 : 11:33:02

I need to compare a right justified character field to an integer field:

I am bulding a temp file that contains business units (from DB2/400). Both fields are 12 char and right justified in the table

CREATE TABLE	#AuthorityTable
		(
			busunit_from char(12),
			busunit_to char(12)
		)

INSERT	#AuthorityTable
	(
		busunit_from,
		busunit_to
	)

SELECT			msmcuf, msmcut
FROM				OPENQUERY(vgsys400, 'SELECT * FROM vgisec.f0001')
WHERE			msuser = @UserID

Here is the compare. Field dbo.Job.CompanyJobId is an integer:

INNER JOIN	#AuthorityTable AS at ON at.busunit_from <= dbo.Job.CompanyJobId
		    			AND at.busunit_to >= dbo.Job.CompanyJobId

tfountain
Constraint Violating Yak Guru

491 Posts

Posted - 2008-11-21 : 11:48:37

Convert at.busunit_from and at.busunit_to to integer fields.

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2008-11-21 : 11:50:49

This is presently what I have, but there are some records in bus units that are character. It now gives me error when casting to int. So I need to work with dbo.Job.CompanyJobId

CREATE TABLE	#AuthorityTable
		(
			busunit_from bigint,
			busunit_to bigint
		)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-21 : 12:02:35

quote:
Originally posted by snufse

This is presently what I have, but there are some records in bus units that are character. It now gives me error when casting to int. So I need to work with dbo.Job.CompanyJobId

CREATE TABLE #AuthorityTable
(
busunit_from bigint,
busunit_to bigint
)

just take only numeric values then. filter out rest using WHERE condition.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources