HI Jonathan Finch contacted me off list and showed me the error of my ways :)
The answer is to cast the ref field, i.e. ... SELECT * FROM table WHERE CAST(ref AS VarChar(2) = :testCode OR code1 = :testCode OR code2 = :testCode Where testCode is defined as a VarChar(20) in the procedure header. Many thanks Jonathan Rob Jonathan D. Finch On 2/07/2012 9:47 a.m., Robert martin wrote: > Hi > > I have a stored procedure that takes a 'code' as a varChar parameter. > This parameter needs to be checked against 3 fields, code1, code2 and ref . > So I have an SQL like > > Select * > from table > WHERE ref = :testCode > OR code1 = :testCode > OR code2 = :testCode > > > The problem is that while code1 and code2 are character fields, ref is > always numeric. Normally the testCode will be a numeric string but > sometimes it wont. When this happens the SQL breaks. We are using FB > 2.1 and there is no built in is numeric test function. I had thought to > use internal exception trapping like > > try > Select * > from table > WHERE ref = :testCode > OR code1 = :testCode > OR code2 = :testCode > except > Select * > from table > WHERE code1 = :testCode > OR code2 = :testCode > end; > > .... Continue on > > > However is seems that PSQL exception handling doesn't allow this. > > Show any suggestions would be appreciated ! > > Cheers > Rob > > > > ------------------------------------ > > ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ > > Visit http://www.firebirdsql.org and click the Resources item > on the main (top) menu. Try Knowledgebase and FAQ links ! > > Also search the knowledgebases at http://www.ibphoenix.com > > ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ > Yahoo! Groups Links > > > > > > ----- > No virus found in this message. > Checked by AVG - www.avg.com > Version: 2012.0.2178 / Virus Database: 2437/5104 - Release Date: 07/01/12 > > ------------------------------------ ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ Visit http://www.firebirdsql.org and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links ! Also search the knowledgebases at http://www.ibphoenix.com ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ Yahoo! Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: [email protected] [email protected] <*> To unsubscribe from this group, send an email to: [email protected] <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
