安西加入甲骨文8i

Question

我在Oracle 8i中有这样的查询：

 select
  decode(seqnum,
            1  , '1',
            cnt, '0'
        ) as                   value1,
  decode(seqnum,
            1  , t.BEGIN_DT,
            cnt, t.END_DT
  ) as                         TIME1,   
           t4.UNIT1 || '.SUBBATCH_TRIGGER' TAG     
      from (select t.*,
                   row_number() over(partition by t.BATCH_ID, t.plant_unit, t3.ID2 order by t.BEGIN_DT) as seqnum,
                   count(*) over(partition by t.BATCH_ID, t.plant_unit, t3.ID2) as cnt
              from SCH2.tb_pg_unit_stap t
    join (select ID1,batch_id from SCH2.VW_BATCH) t2 on t.BATCH_ID = t2.BATCH_ID
    join (select ID2,ID1 from SCH1.STEP) t3 on t3.ID1 = t2.ID1) t
    join SCH2.TB_W_MACHINE t4 on t4.plant_unit = t.plant_unit
    where (seqnum = 1
        or seqnum = cnt) AND (t.BEGIN_DT > '01-jan-2013' AND t.BEGIN_DT < '01-feb-2013');

我最近询问了Stackoverflow社区，他们告诉我Oracle8i不支持ansi。

如何为Oracle 8i重写此查询？

提前感谢！

Answer 1

select decode(seqnum, 1 , '1',
                      cnt, '0') as value1,
       decode(seqnum, 1, t.BEGIN_DT,
                      cnt, t.END_DT) as TIME1,
       t4.UNIT1 || '.SUBBATCH_TRIGGER' TAG
from (select t.*,
             row_number() over(partition by t.BATCH_ID, t.plant_unit, t3.ID2 order by t.BEGIN_DT) as seqnum,
             count(*) over(partition by t.BATCH_ID, t.plant_unit, t3.ID2) as cnt
      from SCH2.tb_pg_unit_stap t,
           (select ID1,batch_id from SCH2.VW_BATCH) t2,
           (select ID2,ID1 from SCH1.STEP) t3
      where t.BATCH_ID = t2.BATCH_ID
        and t3.ID1 = t2.ID1) t,
     SCH2.TB_W_MACHINE t4
where t4.plant_unit = t.plant_unit
  and (seqnum = 1 or seqnum = cnt)
  AND (t.BEGIN_DT > '01-jan-2013' AND t.BEGIN_DT < '01-feb-2013');