conversion - SQL Server Forums

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

SQL Server Development (2000)

conversion

Author

Topic

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2009-08-21 : 14:42:17

[code]
I got the below error..

Syntax error converting the varchar value 'OA' to a column of data type int.

I want to exclude counts which are not INT
Ex:OA is not integer because of which i was getting error...

Please help..

DECLARE @Student TABLE

(

SID Int,

DSCRPTR VARCHAR(50)

)

INSERT INTO @Student (SID, DSCRPTR)

(
SELECT '0', 'Tested Social' union all
SELECT '1', 'Tested Math' union all
SELECT '77', 'Tested English' union all
SELECT 'OA', 'Tested with Wrong Data in the table'
)

SELECT A.DscrPtr,(
SELECT COUNT(*) FROM dbo.st_info Z WHERE Z.ST_ID = A.SID)
FROM @Student A
ORDER BY A.SID

[/code]

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-08-21 : 15:04:11

does this work for you?

where convert(varchar, st_id) = a.sid

Or maybe this:
WHERE Z.ST_ID = A.SID
AND a.sid not like '%[^0-9]%'

both methods may have some problems depending on your data. Why isn't SID an integer???

Be One with the Optimizer
TG

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2009-08-21 : 15:21:20

Thanks TG..
Both solutions are not working..there are some junk in the table..

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-08-21 : 15:26:35

>>Both solutions are not working
Post some values (from both tables) that should be matching up but are not

EDIT:
and post the datatype of: st_info.st_id

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources