Oracle functions migration to Vertica

Hi,
Why does Cognos need to call the function for every report row? Can you just modiy the report query (i.e. SQL) to include the function's logic?

Example:

    dbadmin=> \d report_data
                                       List of Fields by Tables
     Schema |    Table    | Column | Type | Size | Default | Not Null | Primary Key | Foreign Key
    --------+-------------+--------+------+------+---------+----------+-------------+-------------
     public | report_data | c      | int  |    8 |         | f        | f           |
    (1 row)

    dbadmin=> \d lookup_data
                                           List of Fields by Tables
     Schema |    Table    |  Column   |    Type    | Size | Default | Not Null | Primary Key | Foreign Key
    --------+-------------+-----------+------------+------+---------+----------+-------------+-------------
     public | lookup_data | c         | int        |    8 |         | f        | f           |
     public | lookup_data | some_data | varchar(5) |    5 |         | f        | f           |
    (2 rows)

You can use a function (i.e. get_lookup_data) to get the values in the column LOOKUP_DATA.SOME_DATA for every row in the REPORT_DATA table:

SELECT c, get_lookup_data(c) AS lookup_data
  FROM report_data;

Unfortunatley you can't create a User Defined SQL Function in Vertica to do that...

dbadmin=> CREATE FUNCTION get_lookup_data(c_in INT) RETURN VARCHAR(5)
dbadmin-> AS
dbadmin-> BEGIN
dbadmin->   RETURN (SELECT some_data FROM lookup_data WHERE c = c_in);
dbadmin-> END;
ROLLBACK 2624:  Column "c_in" does not exist

Which is why you probably went to Python... But why do that when you can simply do it in the report SQL?

dbadmin=> SELECT a.c, b.some_data
dbadmin->   FROM report_data a
dbadmin->   JOIN lookup_data b
dbadmin->     ON b.c = a.c
dbadmin->  LIMIT 1;
 c | some_data
---+-----------
 5 | WTUKP
(1 row)

Thanks Jim!!
We were trying to avoid changes in the reporting side of things as the customer did not want their reports to be touched, they have 2000 reports and want to continue to use AS-IS post migration. But it seems from your suggestion it is unavoidable and rather recommended to go down that path. Thanks again.

What does the Oracle function do?

It executes a bunch of SQLs and has some business logic

So these are PL/SQL scripts?

Yes