Link to home
Start Free TrialLog in
Avatar of brgdotnet
brgdotnetFlag for United States of America

asked on

Selecting a top value and storing it into a parameter

I have some Sql server code, and I need it converted to Oracle

SET @tmp = (SELECT TOP 1 tmpColzer FROM tecoTable);

Can someone show me how this is done in Oracle?
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

You need to define the colum that needs to order by then something like:
SELECT tmpColzer into tmp FROM (
SELECT tmpColzer, row_number() over(order by some_column) rn  FROM tecoTable
)
where rn=1
declare
tmp datatype;
begin
select tmpColzer into tmp from tecoTable where rownum = 1;
end;
Avatar of brgdotnet

ASKER

Neither of these are working for me? I am getting some compile errors in both which I cannot resolve.
ASKER CERTIFIED SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
This looks like just one small part of what may be a bigger problem (or program).  What will this result be used for?  In Oracle, maybe you don't need to declare a parameter to hold a value, only to pass it to some other program or query.  But, we have no idea of what the big picture is here for you.  If you describe the bigger business problem, we may be able to help you solve it in a simpler way in Oracle.