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