Thanks for replying with a valuable suggestion. Until now I am not
aware there is a 'starting with' syntax. I should read your book
completely.

Actually the integer value here, I mean the column number listed in
the select statement. For example if the search has to by by vendor
then i would use 1 for UPPER value, else if by department then I
would use 2, for Category 3 and so on...

Thanks,

Muthu Annamalai

--- In firebird-support@yahoogroups.com, Helen Borrie <helebor@...>
wrote:

>
> At 01:57 PM 9/12/2006, you wrote:
> >I have the following stored procedure and I am not able to pass a
> >variable to the upper function in the select. I need to pass

integer

> >value as per requirement.
>
> What requirement? UPPER() takes a string argument.
>
> >When I pass integer value directly in stored
> >procedure and compile, it compiles, but when i pass integer

variable it

> >says column unknown.
> >
> >FOR
> > SELECT VENDOR, DEPARTMENT,

CATEGORY,ITEMSTYLE,PRICE,ITEMSTYLE.ID,

> >BARCODE
> > FROM DEPARTMENT, CATEGORY, ITEM, ITEMSTYLE, VENDOR
> > WHERE ITEMSTYLE.ITEMID = ITEM.ID AND
> > ITEM.VENDORID = VENDOR.ID AND
> > ITEM.CATEGORYID = CATEGORY.ID AND
> > CATEGORY.DEPTID = DEPARTMENT.ID AND
> > UPPER(DEPARTMENT) LIKE UPPER(:SEARCHCHAR) ||'%'
> > ORDER BY 1,2,3,4
> >
>
>INTO :VENDOR, :DEPARTMENT, :CATEGORY, :ITEMSTYLE, :PRICE, :ITEMSTYL

EID

> >, :BARCODE
> > DO SUSPEND;
>
> First, don't use LIKE for this search, use STARTING WITH.
>
> Secondly, in most conditions, an integer can be autocast to a
> string. However, because its type is not known at the point where
> you are concatenating it, you are getting this error. The trick is

to

> begin with an empty string and then to concatenate the integer to

it.

>
> Thirdly, you don't need UPPER() when the search string is all
> numerals. There is no such animal as an uppercase numeral. In any
> case, STARTING WITH is case-insensitive.
>
> FOR
> SELECT VENDOR, DEPARTMENT,

CATEGORY,ITEMSTYLE,PRICE,ITEMSTYLE.ID,

> BARCODE
> FROM DEPARTMENT, CATEGORY, ITEM, ITEMSTYLE, VENDOR
> WHERE ITEMSTYLE.ITEMID = ITEM.ID AND
> ITEM.VENDORID = VENDOR.ID AND
> ITEM.CATEGORYID = CATEGORY.ID AND
> CATEGORY.DEPTID = DEPARTMENT.ID AND
> DEPARTMENT starting with '' || :SEARCHCHAR
> ORDER BY 1,2,3,4
>
> I said "...in most conditions..." If you find that the autocast
> still throws an Unknown Type error, then use an explicit cast, to
> provide an actual maximum size for the search string:
>
> FOR
> SELECT VENDOR, DEPARTMENT,

CATEGORY,ITEMSTYLE,PRICE,ITEMSTYLE.ID,

> BARCODE
> FROM DEPARTMENT, CATEGORY, ITEM, ITEMSTYLE, VENDOR
> WHERE ITEMSTYLE.ITEMID = ITEM.ID AND
> ITEM.VENDORID = VENDOR.ID AND
> ITEM.CATEGORYID = CATEGORY.ID AND
> CATEGORY.DEPTID = DEPARTMENT.ID AND
> DEPARTMENT starting with cast(:SEARCHCHAR as varchar

(10))

> ORDER BY 1,2,3,4
>
> ./heLen
>