Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2003
    Location
    Austin, TX
    Posts
    3

    Red face Unanswered: need help in UDF

    Hi all,
    I am getting this problem. Script and error are below


    connect to jagdip user db2inst1 using ibmdb2 @

    set current sqlid='db2inst1' @

    drop function salesamount @
    drop table employee @
    drop table clients @
    drop table products @
    drop table sales @

    create table employee
    (emp_num integer not null,
    emp_name varchar(20),
    emp_commision real,
    emp_salary real
    ) @


    create table clients
    (c_num integer not null,
    c_name varchar(20),
    c_addr varchar(20),
    c_city varchar(15),
    c_state varchar(2),
    c_zip integer,
    c_phone integer
    ) @


    create table products
    (p_num integer not null,
    p_quantity integer,
    p_price real
    ) @


    create table sales
    (s_num integer not null,
    p_num integer not null,
    s_quantity integer not null,
    s_amount real not null,
    emp_num integer not null,
    c_num integer not null
    ) @

    create function salesamount ( s_quant integer, p_number integer ) returns real
    begin atomic
    declare SalesAmount real;
    SET (SalesAmount) = (SELECT p_price from products where p_num = p_number);
    return SalesAmount*s_quant;
    end @


    create unique index employee_idx
    on employee(emp_num) @

    create unique index clients_idx
    on clients(c_num) @

    create unique index products_idx
    on products(p_num) @

    create unique index sales_idx
    on sales(s_num) @

    alter table employee
    add CONSTRAINT employee_idx primary key(emp_num) @

    alter table clients
    add CONSTRAINT clients_idx primary key(c_num) @

    alter table products
    add CONSTRAINT products_idx primary key(p_num) @

    alter table sales
    add CONSTRAINT sales_idx primary key(s_num)
    add foreign key (p_num)
    REFERENCES products(p_num)
    ON DELETE RESTRICT
    add foreign key (emp_num)
    REFERENCES employee(emp_num)
    ON DELETE RESTRICT
    add foreign key (c_num)
    REFERENCES clients(c_num)
    ON DELETE RESTRICT
    add CONSTRAINT sales_constraint check(s_amount = db2inst1.salesamount(s_quantity, p_num)) @


    this script is giving me problem

    DB21034E The command was processed as an SQL st
    valid Command Line Processor command. During SQ
    SQL0440N No function by the name "SALESAMOUNT"
    was found in the function path. SQLSTATE=42884

    can any one help me

    regards,
    Jagdip

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: need help in UDF

    Probably it is this statement throwing the error ...

    drop function salesamount @

    Cheers
    Sathyaram

    Originally posted by jagdip singh
    Hi all,
    I am getting this problem. Script and error are below


    connect to jagdip user db2inst1 using ibmdb2 @

    set current sqlid='db2inst1' @

    drop function salesamount @
    drop table employee @
    drop table clients @
    drop table products @
    drop table sales @

    create table employee
    (emp_num integer not null,
    emp_name varchar(20),
    emp_commision real,
    emp_salary real
    ) @


    create table clients
    (c_num integer not null,
    c_name varchar(20),
    c_addr varchar(20),
    c_city varchar(15),
    c_state varchar(2),
    c_zip integer,
    c_phone integer
    ) @


    create table products
    (p_num integer not null,
    p_quantity integer,
    p_price real
    ) @


    create table sales
    (s_num integer not null,
    p_num integer not null,
    s_quantity integer not null,
    s_amount real not null,
    emp_num integer not null,
    c_num integer not null
    ) @

    create function salesamount ( s_quant integer, p_number integer ) returns real
    begin atomic
    declare SalesAmount real;
    SET (SalesAmount) = (SELECT p_price from products where p_num = p_number);
    return SalesAmount*s_quant;
    end @


    create unique index employee_idx
    on employee(emp_num) @

    create unique index clients_idx
    on clients(c_num) @

    create unique index products_idx
    on products(p_num) @

    create unique index sales_idx
    on sales(s_num) @

    alter table employee
    add CONSTRAINT employee_idx primary key(emp_num) @

    alter table clients
    add CONSTRAINT clients_idx primary key(c_num) @

    alter table products
    add CONSTRAINT products_idx primary key(p_num) @

    alter table sales
    add CONSTRAINT sales_idx primary key(s_num)
    add foreign key (p_num)
    REFERENCES products(p_num)
    ON DELETE RESTRICT
    add foreign key (emp_num)
    REFERENCES employee(emp_num)
    ON DELETE RESTRICT
    add foreign key (c_num)
    REFERENCES clients(c_num)
    ON DELETE RESTRICT
    add CONSTRAINT sales_constraint check(s_amount = db2inst1.salesamount(s_quantity, p_num)) @


    this script is giving me problem

    DB21034E The command was processed as an SQL st
    valid Command Line Processor command. During SQ
    SQL0440N No function by the name "SALESAMOUNT"
    was found in the function path. SQLSTATE=42884

    can any one help me

    regards,
    Jagdip
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Nov 2003
    Location
    Austin, TX
    Posts
    3
    But it is creating the function again after dropping it
    so i think something else is wrong

    regards,
    Jagdip Singh

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •