ibm midrange - SQL Statement to select row where previous row status = 'C' AS400 -

this being run on sql ibmi series 7

i have table stores info orders. each row has order number (on), part number(pn), , sequence number(seq). each on have multiple pn's linked them , each part number has multiple seq number. each sequence number represents order in work on part. somewhere else in system once part @ location , ready worked on shows flag. want list of orders location have not yet arrived have been closed out on previous location( means part on it's way).

i have query listed below believe should work following error: "the column qualifier or table t undefined". issue at?

select * (select m2on order__number , m2seq sequence__number, m2pn product__number,ml2oq order__quantity                     m2p  m2pn in (select r1pn r1p (rtwc = '7411') , (r1seq = m2seq) )                             , m2on in (select m1on m1p ml1rcf = '')                                          order ml2osm asc) t   t.order__number in  (select t3.m2on (select * from(select * m2p m2on = t.order__number , m2pn = t.product__number order m2seq asc fetch first 2 rows )as t1 order m2seq asc fetch first row ) t3 t3.m2stat = 'c') 

edit- answer else issue clutton's answer worked slight modification thank him fast response! had name outer table , specify in subquery otherwise as400 kick , tell me couldn't find columns. had order sequence number descending grabbed highest record below parameter(otherwise example if sequence number 20 grab 5 though 10 available , should shown first. here subquery use. please note actual query names m2p t1.

ifnull((     select         m2stat             m2p m2p_1             m2on = t1.m2on         , m2seq < t1.m2seq     , m2pn in (select r1pn r1p (rtwc = @wc) , (r1seq = t1.m2seq))     order m2seq desc     fetch first row ), 'null') prior_m2stat             

just reading question, looks emulate rpg readpe op codes. key m2p order/seq? if so, here basic piece may build out rest of query.

i assuming trying prior record key using sql. in rpg doing readpe on key file order/seq key.

here example using subquery status field of prior record.

select      m2on, m2pn, m2oq, m2stat,     ifnull((         select             m2stat                     m2p m2p_1                     m2p_1.m2on = m2on             , m2p_1.m2seq < m2seq         fetch first row     ), '') prior_m2stat     m2p 

note wraps subquery in ifnull handle case first sequence number , no prior sequence exists.