Oracle SQL Query

try

with t as (
select b.itemNo, b.effectiveDate, b.unitCost,
       row_number() over (partition by b.ItemNo order by effectiveDate desc) rn
  from ToBePurchased a
  left join itemCost b  
    on a.itemNo = b.itemNo
       )
select *
  from t
 where rn=1
 order by itemNo

Select allOpen in new window

Thx but it does works. Any idea ?
SQL> show errors
Errors for PROCEDURE TEST:

LINE/COL ERROR
-------- -----------------------------------------------------------------
9/3    PL/SQL: SQL Statement ignored
11/8    PL/SQL: ORA-30483: window  functions are not allowed here
17/9    PLS-00103: Encountered the symbol "end-of-file" when expecting
    one of the following:
    ( begin case declare end exception exit for goto if loop mod
    null pragma raise return select update while with
    <an identifier> <a double-quoted delimited-identifier>
    <a bind variable> << continue close current delete fetch lock
    insert open rollback savepoint set sql execute commit forall
    merge pipe purge json_exists json_value json_query
    json_object json_array

LINE/COL ERROR
-------- -----------------------------------------------------------------

create or replace procedure test

as


begin

      
  with t as (
select b.itemNo, b.effectiveDate, b.unitCost from ToBePurchased a,
       row_number() over (partition by b.ItemNo order by effectiveDate desc) rn
  left join itemCost b  
    on a.itemNo = b.itemNo order by b.unitcost
       )
select * from t where rn=1  

end test;
/

Select allOpen in new window

sorry, copy/paste issue...
fixed, please refresh the page...

Sorry, still doesn't work.

maybe a missing semicolon at the end ";"

Return error :

9/2    PLS-00428: an INTO clause is expected in this SELECT statement

this looks like it is coming from somewhere else

please post full sql code...

create or replace procedure test 
as

begin

 with t as (
select b.itemNo, b.effectiveDate, b.unitCost,
       row_number() over (partition by b.ItemNo order by effectiveDate desc) rn
  from ToBePurchased a
  left join itemCost b  
    on a.itemNo = b.itemNo
       )
select *
  from t
 where rn=1
 order by itemNo;
 
end test;
/

Select allOpen in new window

if you want this query returned as an output parameter, you have to use a cursor
and call stored procedure, get cursor and consume it in your app

CREATE OR REPLACE PROCEDURE get_Items ( p_recordset OUT SYS_REFCURSOR) AS 
BEGIN

OPEN p_recordset FOR
with t as (
select b.itemNo, b.effectiveDate, b.unitCost,
       row_number() over (partition by b.ItemNo order by b.effectiveDate desc) rn
  from ToBePurchased a
  left join itemCost b  
    on a.itemNo = b.itemNo
       )
select *
  from t
 where rn=1
 order by itemNo;
 
END;

Select allOpen in new window