Re: Problems with Select Max on Date Field

From: <stevec_at_zimmer.csufresno.edu>
Date: 1998/02/27
Message-ID: <6d6vrl$scm$1@nnrp1.dejanews.com>#1/1

Are you aware that your text is loading about 950 characters into variable v_sql_statement? If you don't have it defined as varchar2(1000) or larger, then that is the problem.

If I were writing the code, I would put quotes around each line and use concatenation. This would eliminate your unnecessary blank padding in the actual SQL statement.

For example:

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 ' .... etc...

HTH
Steve Cosner
http://members.aol.com/stevec5088

In article <34F5C86E.7B6A_at_utmb.edu>,
  John Gibson <jogibson_at_utmb.edu> 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.

-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/ Now offering spam-free web-based newsreading Received on Fri Feb 27 1998 - 00:00:00 CST