|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
convert INTselect * from testtable where id in (select id from anothertable). The ID field in anothertable is int whereas the ID field in testtable is varchar(originally created by someone else). 99% of the data in the ID field of testtable is an INT, so I usually do not have a problem. But a couple of the entries are text. How can I ignore anything in the ID field of testtable that is not and INT? I currently get an error: Syntax error converting the varchar value 'abcabc' to a column of data type int. Thank you *** Sent via Developersdex http://www.developersdex.com *** select "name the columns-don't use *" from testtable T where exists (select
id from anothertable where id = Cast(T.ID as int)) where isnumeric(T.id) = 1 There are some issues with Isnumeric. Come back to us if you have a problem. Show quote "Joey Martin" <j***@infosmiths.net> wrote in message news:eANO76h5FHA.724@TK2MSFTNGP14.phx.gbl... >I am writing a sql statement similiar to: > select * from testtable where id in (select id from anothertable). > > The ID field in anothertable is int whereas the ID field in testtable is > varchar(originally created by someone else). > > 99% of the data in the ID field of testtable is an INT, so I usually do > not have a problem. But a couple of the entries are text. > > How can I ignore anything in the ID field of testtable that is not and > INT? I currently get an error: Syntax error converting the varchar value > 'abcabc' to a column of data type int. > > > Thank you > > > > > *** Sent via Developersdex http://www.developersdex.com *** You can use the ISNUMERIC( ) function in your WHERE clause to test the
column value. This will filter it from the second table to avoid any join errors. WHERE ISNUMERIC(TableWithVarChars.KeyField) HTH, John Scragg Show quote "Joey Martin" wrote: > I am writing a sql statement similiar to: > select * from testtable where id in (select id from anothertable). > > The ID field in anothertable is int whereas the ID field in testtable is > varchar(originally created by someone else). > > 99% of the data in the ID field of testtable is an INT, so I usually do > not have a problem. But a couple of the entries are text. > > How can I ignore anything in the ID field of testtable that is not and > INT? I currently get an error: Syntax error converting the varchar value > 'abcabc' to a column of data type int. > > > Thank you > > > > > *** Sent via Developersdex http://www.developersdex.com *** > Thanks for the help, but I am receiving another error now:
Here is the actual query: select la_code,mls_acct,street_name,street_num,street_dir,city,current_price,'r es' as prop_type,cast(substring(REMARKS, 1, 5999) as varchar(6000)) AS [REMARKS],udf_photo0 from glar_mlsdata_res t where exists (select agentid from glar_clients where agentid = Cast(T.la_code as int)) where isnumeric(T.la_code) = 1 ERROR: Incorrect syntax near the keyword 'where'. *** Sent via Developersdex http://www.developersdex.com *** Sorry, I made a mistake on my answer.
Change: where isnumeric(T.la_code) = 1 to and isnumeric(T.la_code) = 1 Show quote "Joey Martin" <j***@infosmiths.net> wrote in message news:uq0KyWi5FHA.3136@TK2MSFTNGP09.phx.gbl... > Thanks for the help, but I am receiving another error now: > > Here is the actual query: > select > la_code,mls_acct,street_name,street_num,street_dir,city,current_price,'r > es' as prop_type,cast(substring(REMARKS, 1, 5999) as varchar(6000)) AS > [REMARKS],udf_photo0 > from glar_mlsdata_res t where > exists (select > agentid from glar_clients where agentid = Cast(T.la_code as int)) > where isnumeric(T.la_code) = 1 > > > ERROR: Incorrect syntax near the keyword 'where'. > > *** Sent via Developersdex http://www.developersdex.com *** Joey,
ISNUMERIC is an interesting, but not useful function in this case because "ISNUMERIC returns 1 when the input expression evaluates to a valid integer, floating point number, money or decimal type; otherwise it returns 0." Characters such as $ , E are still allowed. In any case, whether by ISNUMERIC or by NOT LIKE [list of characters you don't want] you could try to filter the rows. However the optimizer can choose to filter rows first, as you would want, or filter them last, after it is too late to you, and that is not under your control. Best bet: Get the data in anothertable cleaned up and then make the ID an INT. Assuming you cannot do that: CAST your INT as VARCHAR so that it matches anothertable. RLF Show quote "Joey Martin" <j***@infosmiths.net> wrote in message news:eANO76h5FHA.724@TK2MSFTNGP14.phx.gbl... >I am writing a sql statement similiar to: > select * from testtable where id in (select id from anothertable). > > The ID field in anothertable is int whereas the ID field in testtable is > varchar(originally created by someone else). > > 99% of the data in the ID field of testtable is an INT, so I usually do > not have a problem. But a couple of the entries are text. > > How can I ignore anything in the ID field of testtable that is not and > INT? I currently get an error: Syntax error converting the varchar value > 'abcabc' to a column of data type int. > > > Thank you > > > > > *** Sent via Developersdex http://www.developersdex.com *** Here a function for that check that can replace Isnumeric.
This checks for all valid numbers including Decimal. It could easily be adapted for INTs only. CREATE FUNCTION dbo.IsReallyNumeric (@num VARCHAR(19)) RETURNS BIT BEGIN RETURN CASE WHEN LEFT(@num,1) LIKE '[-0-9+.]' AND PATINDEX('%[^0-9.]%', SUBSTRING(@num, 2, 18)) = 0 AND LEN(@num) - LEN(REPLACE(@num, '.', '')) <=1 THEN 1 ELSE 0 END END I'd give credit to the person that wrote this if I remembered who it was. Show quote "Russell Fields" <RussellFie***@NoMailPlease.Com> wrote in message news:ushNbPi5FHA.1032@TK2MSFTNGP11.phx.gbl... > Joey, > > ISNUMERIC is an interesting, but not useful function in this case because > "ISNUMERIC returns 1 when the input expression evaluates to a valid > integer, floating point number, money or decimal type; otherwise it > returns 0." Characters such as $ , E are still allowed. > > In any case, whether by ISNUMERIC or by NOT LIKE [list of characters you > don't want] you could try to filter the rows. However the optimizer can > choose to filter rows first, as you would want, or filter them last, after > it is too late to you, and that is not under your control. > > Best bet: Get the data in anothertable cleaned up and then make the ID an > INT. > > Assuming you cannot do that: CAST your INT as VARCHAR so that it matches > anothertable. > > RLF > > "Joey Martin" <j***@infosmiths.net> wrote in message > news:eANO76h5FHA.724@TK2MSFTNGP14.phx.gbl... >>I am writing a sql statement similiar to: >> select * from testtable where id in (select id from anothertable). >> >> The ID field in anothertable is int whereas the ID field in testtable is >> varchar(originally created by someone else). >> >> 99% of the data in the ID field of testtable is an INT, so I usually do >> not have a problem. But a couple of the entries are text. >> >> How can I ignore anything in the ID field of testtable that is not and >> INT? I currently get an error: Syntax error converting the varchar value >> 'abcabc' to a column of data type int. >> >> >> Thank you >> >> >> >> >> *** Sent via Developersdex http://www.developersdex.com *** > > Interesting, but I don't think that it resolves the optimizer precedence
issue to a query. The optimizer has lots of choices, but what we care about here is these two choices: 1 - Run IsReallyNumeric and filter on the bit 2 - Cast the Numeric as INT and join the tables Although ideally they would run in 1, 2 order, the optimizer is equally happy choosing 2, 1 order. But 2, 1 still causes errors. (I have seen code depending on 1, 2 order run for months or years then, one sad day, switch to 2, 1 and cause failures.) Something that will work is to create a Multi-Statement Table-Valued User Defined Function that uses the IsReallyNumeric to prefilter the rows. That step _is_ forced to run first, before choosing other optimizer strategies. (But an In-Line Table-Valued UDF will not force precedence.) RLF Show quote "Raymond D'Anjou" <rdanjou@canatradeNOSPAM.com> wrote in message news:%23JCTcUi5FHA.2552@TK2MSFTNGP10.phx.gbl... > Here a function for that check that can replace Isnumeric. > This checks for all valid numbers including Decimal. > It could easily be adapted for INTs only. > > CREATE FUNCTION dbo.IsReallyNumeric > (@num VARCHAR(19)) > RETURNS BIT > BEGIN > RETURN CASE > WHEN LEFT(@num,1) LIKE '[-0-9+.]' > AND PATINDEX('%[^0-9.]%', SUBSTRING(@num, 2, 18)) = 0 > AND LEN(@num) - LEN(REPLACE(@num, '.', '')) <=1 > THEN 1 > ELSE 0 > END > END > > I'd give credit to the person that wrote this if I remembered who it was. > > "Russell Fields" <RussellFie***@NoMailPlease.Com> wrote in message > news:ushNbPi5FHA.1032@TK2MSFTNGP11.phx.gbl... >> Joey, >> >> ISNUMERIC is an interesting, but not useful function in this case because >> "ISNUMERIC returns 1 when the input expression evaluates to a valid >> integer, floating point number, money or decimal type; otherwise it >> returns 0." Characters such as $ , E are still allowed. >> >> In any case, whether by ISNUMERIC or by NOT LIKE [list of characters you >> don't want] you could try to filter the rows. However the optimizer can >> choose to filter rows first, as you would want, or filter them last, >> after it is too late to you, and that is not under your control. >> >> Best bet: Get the data in anothertable cleaned up and then make the ID an >> INT. >> >> Assuming you cannot do that: CAST your INT as VARCHAR so that it matches >> anothertable. >> >> RLF >> >> "Joey Martin" <j***@infosmiths.net> wrote in message >> news:eANO76h5FHA.724@TK2MSFTNGP14.phx.gbl... >>>I am writing a sql statement similiar to: >>> select * from testtable where id in (select id from anothertable). >>> >>> The ID field in anothertable is int whereas the ID field in testtable is >>> varchar(originally created by someone else). >>> >>> 99% of the data in the ID field of testtable is an INT, so I usually do >>> not have a problem. But a couple of the entries are text. >>> >>> How can I ignore anything in the ID field of testtable that is not and >>> INT? I currently get an error: Syntax error converting the varchar value >>> 'abcabc' to a column of data type int. >>> >>> >>> Thank you >>> >>> >>> >>> >>> *** Sent via Developersdex http://www.developersdex.com *** >> >> > > On Thu, 10 Nov 2005 16:30:31 -0500, Russell Fields wrote:
Show quote >Interesting, but I don't think that it resolves the optimizer precedence Hi Russell,>issue to a query. The optimizer has lots of choices, but what we care about >here is these two choices: > >1 - Run IsReallyNumeric and filter on the bit >2 - Cast the Numeric as INT and join the tables > >Although ideally they would run in 1, 2 order, the optimizer is equally >happy choosing 2, 1 order. But 2, 1 still causes errors. (I have seen code >depending on 1, 2 order run for months or years then, one sad day, switch to >2, 1 and cause failures.) > >Something that will work is to create a Multi-Statement Table-Valued User >Defined Function that uses the IsReallyNumeric to prefilter the rows. That >step _is_ forced to run first, before choosing other optimizer strategies. >(But an In-Line Table-Valued UDF will not force precedence.) Another way to force the evaluation order is to use a CASE expression. For a join, you'd get something like FROM Tab1 INNER JOIN Tab2 ON Tab2.ReallyNumeric = CASE WHEN IsNumeric(Tab1.BadColumn) THEN CAST(Tab1.BadColumn AS int) END If the bad column is not numeric (and IsReallyNumeric or an inline CASE expression could be used instead of IsNumeric), the CASE will evaluate to NULL, the join condition evaluates to Unknown and the row is not included. Best, Hugo -- (Remove _NO_ and _SPAM_ to get my e-mail address)  |
|||||||||||||||||||||||
Other interesting topics