Re: PL/SQL question

Buddy Wolfe <wolfe_at_gte.net> wrote:
>Help...
>
>in a normal SQL I can do the following query:
>
> SELECT MAX(price_tier)
> FROM state_tier
> WHERE state = 'AR'
> and dollars < 36000;
>
>which will return one row out of a possible 8 rows.
>
>The problem is I need to do this in PL/SQL and assign the value returned to a varible. I
>have tried to use GREATEST, however, I am getting an ORA-06502 PL/SQL:numeric or value
>error. The column price tier is VARCHAR2, but the actual values are 11, 21, 31, 41, etc.
> I am assigning it to a varible v_price_tier state_tier.price_tier%TYPE.
>
>Please email at wolfe_at_gte.net

Hi,

There may be 2 possiblities why the error is generated.

1. Check the data length of pl/sql variable. It may be shorter than actual length of data retrieved.
2. Use to_number before applying max.

Regards

N.Prabhakar Received on Fri Jan 17 1997 - 00:00:00 CST