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, :ITEMSTYLEID
>, :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