Hi James,

> Hi guys, can you please comment on this. I was trying to this select
> below with a success. However I don't know how come the char field can
> act like integer field.
>
> select "DR ID", "DR No", "Cust ID", "Name", "Deliver Date", "Grand Total"
> from "DR"
> where "Deliver Date" >= '12/1/2003' and "Deliver Date" <= '12/31/2003'
> and "DR No" is not null
> and "DR No" >= '12062' and "DR No" <= '12437'
> order by "DR No"

Wow, are you using delimited case sensitive column names? That
must be a pain.

> But this is my first attempt which i failed. I think its because I
> have some record with null value on "DR No".
>
> select "DR ID", "DR No", "Cust ID", "Name", "Deliver Date", "Grand Total"
> from "DR"
> where "Deliver Date" >= '12/1/2003' and "Deliver Date" <= '12/31/2003'
> and "DR No" is not null
> and cast("DR No" as integer) >= 12062 and cast("DR No" as integer) <=
> 12437
> order by "DR No"
>
> My question is in store procedure can I directly compare the char
> field with integer field?

A stored procedure uses the exact same logic as a normal query.

You can _compare_ an integer to a char field just fine. The problem
comes with your "less than" ( <= ) and greater than. These behave
different on char colmns. Without trying, did your second attempt
return the right results?

And another question: if you're storing numbers, why not use an Integer
type column?

With regards,

Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL
Server.
Upscene Productions
http://www.upscene.com