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?
LVL 2
brgdotnetcontractorAsked:
Who is Participating?
 
slightwv (䄆 Netminder) Commented:
Need more information.  What are the error messages.  How are you executing it?

Mine works for me.

Here is my complete test setup and run:
create table mytecotable (tmpcolzer number, some_column number);
insert into mytecotable values(1,1);
insert into mytecotable values(2,2);
commit;


declare
	tmp number;
begin
SELECT tmpColzer into tmp FROM (
SELECT tmpColzer, row_number() over(order by some_column) rn  FROM mytecoTable
)
where rn=1 ;

dbms_output.put_line('Got: ' || to_char(tmp));
end;
/

Open in new window

0
 
slightwv (䄆 Netminder) Commented:
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
0
 
awking00Commented:
declare
tmp datatype;
begin
select tmpColzer into tmp from tecoTable where rownum = 1;
end;
0
 
brgdotnetcontractorAuthor Commented:
Neither of these are working for me? I am getting some compile errors in both which I cannot resolve.
0
 
Mark GeerlingsDatabase AdministratorCommented:
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.