Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2004
    Posts
    35

    Unanswered: SQL0440N getting when running query against user function

    Hi,
    I created functions and im trying to call them through a simple query, but im getting the following error.
    can someone tell me what i did wrong??

    what i did is :


    create function time_diff()
    deterministic
    no external action
    returns int
    return -16@

    create function s_id()
    deterministic
    no external action
    returns int
    return 0@

    create function DateFrom()
    deterministic
    no external action
    returns timestamp
    return timestamp('2003-05-01-08.00.00')@

    create function dateto()
    deterministic
    no external action
    returns timestamp
    return timestamp('2003-05-02-08.00.00.000000')@


    now running the function
    select 1
    from event_meter_buckets
    where emb_session_id = s_id() and
    emb_date_time >= DateFrom() and
    emb_date_time < DateTo()


    and getting
    SQL0440N No authorized routine named "S_ID" of type "FUNCTION" having
    compatible arguments was found. SQLSTATE=42884

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    I have alway found that not fully qualifying things leads to problems.

    Try something like:

    create function my_schema.time_diff()
    deterministic
    no external action
    returns int
    return -16@

    create my_schema.function s_id()
    deterministic
    no external action
    returns int
    return 0@

    create function my_schema.DateFrom()
    deterministic
    no external action
    returns timestamp
    return timestamp('2003-05-01-08.00.00')@

    create function my_schema.dateto()
    deterministic
    no external action
    returns timestamp
    return timestamp('2003-05-02-08.00.00.000000')@



    select 1
    from event_meter_buckets
    where emb_session_id = my_schema.s_id() and
    emb_date_time >= my_schema.DateFrom() and
    emb_date_time < my_schema.DateTo()


    HTH

    Andy

Posting Permissions

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