Trying to run this in Oracle 8i

Question

I'm trying to run this query in Oracle 8i but it's not working!

SELECT DECODE(seqnum, 1, t.ID1,cnt,'0') PI_VALUE1,
  DECODE(seqnum, 1, t.STARTTIME,cnt,t.ENDTIME) timestamp,
  '090.'
  || t2.APP
  || '.BATCH' tagname
FROM
  (SELECT t.*,
    row_number() over(partition BY t.ID1, t.PLANT_UNIT order by t.STARTTIME) AS seqnum,
    COUNT(*) over(partition BY t.ID1, t.PLANT_UNIT) cnt
  FROM tb_steps t
  ) t
INNER JOIN tb_equipments t2
ON t2.plant_unit = t.plant_unit
WHERE (seqnum    = 1
OR SEQNUM        = CNT)
AND (T.STARTTIME  > '15-jul-2013'
AND t.ENDTIME    < '15-aug-2013') ;

I've already made a lot of changes [like changing case when for decode] but it's still not OK...

Can someone help me write the query to be supported by Oracle 8i?

PS.: I know this version is not supported by Oracle for AGES but I'm only querying data for my .NET application so I can't upgrade/touch the DB.

Version is 8.1.7 and the specific error:

ORA-00933: SQL command not properly ended.

Many thanks,

Answer 1

ANSI joins were not introduced until Oracle Database 9iR1 (9.0.1). They are not supported in 8.1.7.

Try re-writing the query without an ANSI style join.

Something like this may work:

SELECT DECODE(seqnum, 1, t.ID1,cnt,'0') PI_VALUE1,
  DECODE(seqnum, 1, t.STARTTIME,cnt,t.ENDTIME) timestamp,
  '090.'
  || t2.APP
  || '.BATCH' tagname
FROM
  (SELECT t.*,
    row_number() over(partition BY t.ID1, t.PLANT_UNIT order by t.STARTTIME) AS seqnum,
    COUNT(*) over(partition BY t.ID1, t.PLANT_UNIT) cnt
  FROM tb_steps t
  ) t, tb_equipments t2
WHERE t2.plant_unit = t.plant_unit
  AND   (t.seqnum    = 1
      or t.seqnum        = t.cnt)
AND (T.STARTTIME  > '15-jul-2013'
AND t.ENDTIME    < '15-aug-2013') ;

Totally untested....

Hope that helps.