Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2011
    Posts
    18

    Question Unanswered: Function Generic

    Hello. I have a problem with functions. I need to create a function where i give the params, table name, schema name and field name. Is a function that will go get the value of a field of parameter.

    Example:
    CREATE FUNCTION "SCHEMA_FUNC".FNC_GET_ID (
    SCHEMA_NAME VARCHAR(50),
    TABLE_NAME VARCHAR(50),
    FIELD_NAME VARCHAR(50))
    RETURNS TABLE ( FIELD_NAME INTEGER )
    RETURN SELECT FIELD_NAME
    FROM SCHEMA_NAME.TABLE_NAME
    COMMIT;

    SELECT * FROM TABLE("SCHEMA_FUNC".FNC_GET_ID('TEST', 'USER', 'ID_USER'))

    Thank you.

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    You cannot do that directly with a SQL function. You can try doing the "query" in a stored procedure and then wrap the SP with the function.

    Andy

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    What is the advantage of (a) over (b)?

    (a) SELECT * FROM TABLE("SCHEMA_FUNC".FNC_GET_ID('TEST', 'USER', 'ID_USER'))

    (b) SELECT ID_USER FROM TEST.USER

  4. #4
    Join Date
    Mar 2011
    Posts
    18
    It was just an example code that I quoted. The idea is to reuse code. When there is a need for maintenance, the idea is to have to make corrections in just one place. Another example would be the following:

    CREATE FUNCTION "SCHEMA_FUNC".FNC_GET_ID (
    SCHEMA_NAME VARCHAR(50),
    TABLE_NAME VARCHAR(50),
    FIELD_NAME VARCHAR(50))
    RETURNS TABLE ( FIELD_NAME INTEGER )
    RETURN SELECT MAX(FIELD_NAME)+1
    FROM SCHEMA_NAME.TABLE_NAME
    COMMIT;

    SELECT * FROM TABLE("SCHEMA_FUNC".FNC_GET_ID('TEST', 'USER', 'ID_USER'))

    My system currently has over 2000 tables. And if for some reason I wanted to be the id MAX + 2? I would have too big a job, having to go through all the tables to correct.

  5. #5
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    And if for some reason I wanted to be the id MAX + 2? I would have too big a job, having to go through all the tables to correct.
    won't it be better to put max+1 in as a scalar function, rather than trying to call udf which calls stored procs.
    Visit the new-look IDUG Website , register to gain access to the excellent content.

Tags for this Thread

Posting Permissions

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