Creating a view based on a user-defined function

[Former Member]

Hi

I have a user function which works fine in a select statement, but I need to define a view based on the function. But the response to my ddl,

create or replace view test as select batch_tanks_list (id) namel from batches

is

General error;-9205 POS(1) System error: AK Catalog information not found:000000000000000000BA00

create or replace view test as select batch_tanks_list (id) namel from batches

Is it possible to do this in 7.6.03.15?

Joe

[lbreddemann · Accepted solution]

Hi Joe,

I had a quick look into the files you send me.

The problem is caused by the definition of the return value for the function.

You used:

CREATE Function Batch_Tanks_List (ID INTEGER) 
    RETURNS VARCHAR AS

VAR
    Tanks    Varchar (100) ;
    BatchTank Varchar (100) ;
   
TRY
[---]
RETURN ltrim (Tanks, ', ') ;
//
create or replace view test as select batch_tanks_list (id) namel from batches

Because of the unknown size of the return value the view cannot be created.

If you use CHAR(100) as return value it should work w/o problems:

CREATE Function Batch_Tanks_List (ID INTEGER) 
    RETURNS CHAR(100) AS

VAR
    Tanks    Varchar (100) ;
    BatchTank Varchar (100) ;
   
TRY
[---]
RETURN ltrim (Tanks, ', ') ;
//
create or replace view test as select batch_tanks_list (id) namel from batches

I believe that currently the feature of variable length return values to be used in views is not yet supported, but I would need to check this further.

KR Lars

[Former Member · Accepted solution]

Thanks Lars

I tried returning varchar (100) but the function definition failed. Char (100) works as you suggested - the function compiles and the view can be defined.

Joe

[lbreddemann]

Hi Joe,

please provide all the DDL (also for the function) as well as some test data.

I've done a quick test and it's absoluteley possible to use functions in views.

Anyhow, "batch_tanks_list" sounds like it should return a list rather than a single value.

That ist something which is not supported.

KR Lars