Java HotSpot Virtual Machine

I'm not sure you'll find many people on this forum who can interpret postgres sql

Guessing on what the possible output in this query may be, I'd say yes, Oracle does support what you're attempting to do - but you're not supplying enough information for a definitive answer. There may be a number of ways to come up with a solution to your query - it just depends on what your query is attempting to do

SW

try something like this

create or replace package pkg
as
  type vArray is varray(3) of varchar2(100);
  myArray vArray;
  function getArray(pIndex in integer) return varchar2;
end;
/
create or replace package body pkg
as
  function getArray(pIndex in integer) return varchar2
  is
  begin
    return myArray(pIndex);
  end;
begin
  myArray := vArray();
  myArray.extend;
  myArray(1) := 'karthick';
  myArray.extend;
  myArray(2) := 'oracle';
  myArray.extend;
  myArray(3) := 'nothing';
exception when others then 
  dbms_output.put_line(sqlerrm);
end;
/
select pkg.getArray(1), pkg.getArray(2), pkg.getArray(3)
  from dual
/

Hi,

I'm not a postgresql exponent but the following query might work, though I haven't tested it.

select
r.company_code,
r.date,
udm as d5_udm,
ddm as d5_ddm,
tr as d5_tr,
from (
select
q.company_code,
q.date,
(select avg(abs(highest_rate-opening_rate)) udm,
          avg(abs(opening_rate-lowest_rate)) ddm,
          avg(highest_rate-lowest_rate) tr
 from (select lowest_rate, highest_rate, opening_rate 
        from hkex.quotation q1 
        where not q1.is_trade_suspend = '0' -- as below
        and q1.company_code=q.company_code 
        and q1.date<=q.date 
        order by q1.date desc limit 5) t) as d5   -- not sure what limit 5 does (possibly first 5 rows?)
from hkex.quotation q
where q.is_trade_suspend = '0'  -- Oracle does not support boolean data type for columns so change col to varchar2(1) with '0' and '1' for false and true
) r;

Andre

Edited by: acadet on Mar 4, 2010 3:43 PM