>I have a table with child records that I need to get records of a certain type that have not been processed.
>
>Summary
>All records where workctr = 'INS' and operations before it have been closed.
>
>the table structure
>
>recseq mainindex operation operation_date workctr
>
>123 999 10 02/23/2015 MFG
>456 999 20 02/23/2015 DFM
>789 999 30 INS
>
>122 888 10 02/22/2015 MFG
>133 888 20 DMF
>144 888 30 INS
>
>This is what I have but will not work because it give the 'INS' even if operation before it are open.
>
>
>
>testconnString = "Select MIN(operation) as operation, twono, workctr From webprddt1.drawmtwop" +;
>" Join webprddt1.drawmtwo On webprddt1.drawmtwo.recseq = webprddt1.drawmtwop.twono" +;
>" Where datec Is NULL And operationd Is NULL And workctr = 'INS' Group By twono, workctr"
>
>
select RecSeq, MainIndex, Operation, OperationDate from MyTable T
where WorkCtr = 'INS' and not exists (select * from myTable T2 where
T2.MainIndex = T.MainIndex AND
T2.RecSeq < T.RecSeq
and T2.Operation_Date IS NULL)
If it's not broken, fix it until it is.
My Blog