Re: Problems with Select Max on Date Field

John Gibson wrote:
>
> 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.

You don't need quotes round d_eff_date at all, since it has been declared as a program DATE variable.
(In SQL*Plus, there is no way for a user to supply DATE variables, which therefore have to be processed as CHAR variables)

HTH

-- 
Chrysalis

FABRICATI DIEM, PVNC
('To Protect and to Serve')
Terry Pratchett : "Guards, Guards"