Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2008

    Unanswered: Need help with query that uses a function

    I'd really appreciate some help with the below situation. Because of the sensitivity of our app I can't post the actual query with names but here's a generalized one that matchs:

    Select Distinct B1.col1, B1.col2 B1.col3, A1.col5,
    fcn_funcname(B1.col1, B1.col2, B1.col3, A1.col5) as my_col
    From Table1 A1,
    Table2 B1
    Where A1.col1 = B1.col1
    And A1.col4 = B1.col4
    And B1.col6 = 'XXXX'

    This query retrieves 10k rows in ~100 MS without the function but takes 18 seconds with the function. I'm using the function to retrieve the last column (my_col) because there are multiple rows of this column that match the criteria of each distinct combination of the 1st 4 columns (B1.col1, B1.col2, B1.col3, A1.col5) and that won't work. What I do in the function is create a comma-delimited list of the values that match and return them as one value, which I then use to drive an WHERE IN clause in a subsequent query. I've used this strategy with great success in another case when I've just sent one variable as input to a function and the "lookup table" was something entirely different from the tables in the driver query. I think a root of my current problem may be that the query in the function uses the same two tables as the driver query and contains 4 input variables to the Where clause. Heres the code in the function:

    CREATE OR REPLACE function ALPHA_USER.fcn_SCC2_formrowid
    (in_col1 varchar2,
    in_col2 varchar2,
    in_col3 date,
    in_col5 date)
    return varchar2 is

    sql_statement := 'Select Distinct TO_CHAR(col4) As my_col
    From Table1 A, Table2 B
    Where A.col1 = :in_col1
    And B.col2 = :in_col2
    And B.col6 = 'XXXX'
    And B.col4 = A.col4
    And B.col3 = :in_col3
    And A.col5 = :in_col5
    And A.col1 = B.col1';

    I'm using Dynamic SQL to build the query and I tried to use bind variables to make it faster. FYI, I know I could retrieve the function info by using a simple 2nd query but cold fusion isn't great at making 10K individual db calls in a timely fashion. This is my 1st ever DB forums post so please overlook any cultural or procedural shortcomings in this post.

  2. #2
    Join Date
    Dec 2003
    You could try

    Select Distinct B1.col1, 
    From Table1 A1, 
         Table2 B1
        (Select Distinct TO_CHAR(col4) As my_col, A.col1, B.col2, B.col3, A.col5 
         From Table1 A, 
              Table2 B 
         Where B.col6 = 'XXXX' 
           And B.col4 = A.col4 
           And A.col1 = B.col1') C1
    Where A1.col1 = B1.col1
    And   A1.col4 = B1.col4
    And   B1.col6 = 'XXXX' 
    And   B1.col1 = C1.col1
    And   B1.col2 = C1.col2
    And   B1.col3 = C1.col3
    And   A1.col5 = C1.col5

  3. #3
    Join Date
    Jan 2008

    Thanks for answering. When I run your query I get 26k + rows. This seems to represent one row for each distinct combination of all 5 fields in the main query. The reason I was using the function is because there are 1 to many results of my_col for each distinct set of columns 1-4 and I want/need to get a single value returned that represents all of the 1 to many iterations in one data string. My function code is taking each value I get for any distinct combination of columns 1-4, packing them in a comma-delimited varchar2 variable (ex: '1234','5678','0101'), and sending them back in the main query. I'm then using this string to supply the IN value for a subsequent WHERE clause. Have I ran your query incorrectly? If you have any other ideas they're welcomed.

  4. #4
    Join Date
    Dec 2003
    With fake data, it's kind of tough to help. I was only showing you how to convert a FUNCTION over to a subselect, which should increase the speed in which the SQL executes. As far as 26K goes, I don't know if that's too many records, too few, or if you incorrectly converted the function into a subselect.

    You could try pseudocoding it out with a small table, adding data, and then show what you expect to get from that data. That's usually a good approach when trying to communicate hypothetical data.


Posting Permissions

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