Problems with Select Max on Date Field

From: John Gibson <jogibson_at_utmb.edu>
Date: 1998/02/26
Message-ID: <34F5C86E.7B6A@utmb.edu>#1/1

When I try to compile the PL/SQL package containing the code shown below I get the following error message.

ORA-06502: PL/SQL: numeric or value error.

effective_date is a DATE field in the table, d_eff_date is a DATE field defined in my code based on input from the user.

As best I can tell, it doesn't like the SELECT MAX subquery on a date field. It acts like the date field is in the wrong format, but I can't figure out how to get it in the format it wants. I'm sure I don't have the correct number of single quotes around the variable d_eff_date but I have tried many combinations of quotes (and no quotes) and it still doesn't work. If I try this same query in SQL*Plus I get the results I'm looking for. Is there some trick when handling date fields inside the package? I'm fairly new to this environment so I haven't figured out all the tricks quite yet.

v_sql_statement := 'SELECT e.emp_number, e.name, p.acct_number,
                           a.description, a.account_owner,
                           p.percent_budgeted, p.effective_date
                      FROM employees e, accounts a, primary_accounts p
                     WHERE e.emp_number = p.emp_number
                       AND a.acct_number = p.acct_number
                       AND e.ras_cost_id in (select grp_id
                                               from group_administrators
                                              where emp_number = ' ||
f_login || ')
                       AND p.effective_date = (SELECT
MAX(effective_date)
                                                 FROM Primary_Accounts
                                                WHERE emp_number =
e.emp_number
                                                  AND acct_number =
a.acct_number
                                                  AND effective_date <=

''' || d_eff_date || ''')';

Any help would be greatly appreciated. Received on Thu Feb 26 1998 - 00:00:00 CST