January 28, 2011 at 9:58 am
Developer gave me failing sql. Part of the sql does a convert of data that is varchar to money.
The sql below works and I get the 3 records. If however I add this where clause at the end
"and CONVERT(MONEY,(RA.thevalue)) > 0" it fails trying to convert some varchar values to money
i would have thought that the other where clauses would have isolated the records I need, but it looks like this last convert tries this where clause against all records and fails against some text values. Is there a better way to apply the where. Thanks
select convert(money,ra.thevalue)
from PDreferralattribute ra
iNNER JOIN PDqattribute AS QAT
ON RA.attributeid = QAT.attributeid
AND QAT.attributeclass = 'REFERRAL'
AND QAT.description = 'ACCRUAL'
inner join pdReferral as Ref on ra.referralid = ref.referralid
where ra.referralid in ('QMXRM044356', 'QMXRM081370', 'QMXRM081969')
January 28, 2011 at 10:06 am
The convert needs to be executed first to do the comparaison. Hence it's impossible to use the convert for the validation because you have a no win situation.
Assuming you need to find all the rows with "only digits" then you can use this condition :
WHERE somecolumn NOT LIKE '%[^0-9]%'
You can add more allowed characters in the brackets as you need.
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply