conversion of varchar overflowed int column

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 2000 Forums

Transact-SQL (2000)

conversion of varchar overflowed int column

Author

Topic

pams_shadow
Starting Member

3 Posts

Posted - 2004-02-24 : 10:03:29

I'm using SQL Server Version 8.0. I'm attempting to find gaps in a varchar column. The column isn't sequential meaning the numbers go from 001000001111 to 001011101111 and I need to find any gaps with a number like 00100000%. I can find all of the gaps, but when I put the like statement in the where clause, I get "The conversion of the varchar value '003000000001' overflowed an int column. Maximum integer value exceeded." I know that this the min and max functions implicitly convert the varchar to an int. I've tried explicitly converting the varchar to bigint, but I get the exception, "Error converting datatype varchar to bigint." Any help would be appreciated. I can't change the column type to bigint, boss says it has to stay varchar. Boss doesn't like memory overhead, so using a temp table is out of the question too. Here is my sql statement that causes the int overflow exception.

SELECT PartNum
FROM PartMstr w
WHERE (PartNum LIKE '00100000%')
AND (PartNum IN
(SELECT y.PartNum
FROM PartMstr y INNER JOIN PartMstr x
ON y.PartNum <> x.PartNum - 1
WHERE y.PartNum = (SELECT MAX(z.partnum)
FROM partmstr z
WHERE z.partnum < x.partnum)))

Thanks in advance for any help!

X002548
Not Just a Number

15586 Posts

Posted - 2004-02-24 : 11:25:07

Can you post the table DDL? Including the PK and Indexes?

AND Sample data like...

INSERT INTO table(col list)
SELECT data UNION ALL
SELECT data UNION ALL
SELECT data UNION ALL
ect
SELECT data

Brett

8-)

X002548
Not Just a Number

15586 Posts

Posted - 2004-02-24 : 11:31:40

..but you're problem is here


SELECT PartNum
  FROM PartMstr w
 WHERE PartNum LIKE '00100000%'
   AND PartNum IN (
			SELECT y.PartNum
			  FROM PartMstr y 
		    INNER JOIN PartMstr x 
			   ON y.PartNum <> x.PartNum - 1
			 WHERE y.PartNum = (SELECT MAX(z.partnum)
					      FROM partmstr z
					     WHERE z.partnum < x.partnum
					    )
		  )

You have non numeric data in your varchar column, which is causing the error..

What does the give you?

SELECT COUNT(*) FROM PartMstr WHERE ISNUMERIC(partnum) = 0

Brett

8-)

pams_shadow
Starting Member

3 Posts

Posted - 2004-02-24 : 11:44:28

pams_shadow
Starting Member

3 Posts

Posted - 2004-02-24 : 11:54:10

Brett,

I found the partnum that wasn't numeric and corrected it so that it is numeric, when I do the count now, it returns 0. However, I'm still getting the int column overflow exception.

Thanks for helping me with this. :-)

Pam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources