S
spacesam
Guest
I'm being asked to translate a function from Progress4GL to PostgreSQL. Schema structure is basically the same, but I don't have any experience with the former so it's proving a bit of a headache.
The function extracts and processes some data that is then inserted into a data warehouse by another program. It runs every few minutes and keeps a record of the last processed data so it knows where it left off.
End user is complaining of some missing data when using the translated function. Could you point out if I'm misinterpreting any of the syntaxis in the Progress code?
This is the original function:
&scoped-define COC-REFDES "coc"
&scoped-define MOD-REFDES "mod"
define temp-table ModAttach no-undo
field batch_sn as char format "x(22)"
field unit_sn as char format "x(22)"
field module_sn as char format "x(22)"
field module_pn as char format "x(22)"
field cos_sn as char format "x(22)"
index usnidx is primary unique unit_sn.
define output parameter table for ModAttach.
define temp-table mod$ser no-undo
field serKey as int
field serialNumber as char
field module_pn as char
field activityKey as int
field batch_sn as char
field mod_select as int
field partKey as int
index serkeyidx serKey.
define temp-table mod$comp no-undo
field serkey as int
field actkey as int
field compNum as int
field compKey as int
field refDes as char
field compId as char
index seridx serkey
index actidx actkey.
define temp-table modData no-undo
field serKey as int
field unit_sn as char
field module_sn as char format "x(22)"
field module_pn as char format "x(22)"
field cos_sn as char format "x(22)"
field addCompActKey as int
index serkeyidx serKey.
def var startActKey as int no-undo.
def var lastActKey as int no-undo.
def var lastActKey2 as int no-undo.
def var startActKey2 as int no-undo.
def var pk_id as int no-undo.
define buffer act$loc for location.
define buffer mov$loc for location.
define buffer d$project for project.
define stream logg.
{&log} 'Start ' today '@' string(time,"hh:mm:ss") skip.
{&log} GetModAttach skip.
find first customcode no-lock
where custseq = {&MYCUST-SEQ} no-error.
if not available customcode then do:
{&log} 'customcode {&MYCUST-SEQ} not found' skip.
return.
end.
assign
startActKey = customcode.i1
lastActKey = customcode.i1
startActKey2 = customcode.i2
lastActKey2 = customcode.i2.
define buffer changed$dpart for dpart.
{&log} 'part change, start after actkey:' startActKey skip.
for each activity no-lock
where activity.activity-key > startActKey
and (activity.action = 27 or false),
first serial no-lock
where serial.serial-key = activity.serial-key,
first component no-lock
where component.serial-key = serial.serial-key
and (component.ref_designator = {&MOD-REFDES} or component.ref_designator = {&COC-REFDES} or false)
and (component.removed = 0 or false),
first dpart no-lock
where dpart.copics_part-key = serial.part-key
and (dpart.prod_line_id = 'Pump Laser' or dpart.prod_line_id = 'HRS Pump' or false),
first changed$dpart no-lock
where changed$dpart.copics_part_number = activity.cparam1
and (dpart.prod_line_id = 'Pump Laser' or dpart.prod_line_id = 'HRS Pump' or false)
by activity.activity-key.
lastActKey = activity.activity-key.
if dpart.product_number begins "GN-" or changed$dpart.product_number begins "GN-" then do:
find first mod$ser
where mod$ser.serKey = serial.serial-key no-error.
if not available mod$ser then create mod$ser.
assign
mod$ser.serKey = serial.serial-key
mod$ser.serialNumber = serial.serial_number
mod$ser.module_pn = if dpart.product_number begins "GN-" then dpart.product_number else changed$dpart.product_number
mod$ser.activityKey = activity.activity-key
mod$ser.batch_sn = ''
mod$ser.mod_select = serial.module_select
mod$ser.partKey = serial.part-key.
end.
end.
{&log} 'determine batch sn' skip.
for each mod$ser where mod$ser.mod_select ne 7,
each activity no-lock
where activity.serial-key = mod$ser.serkey
and (activity.action = 23 or activity.action = 24 or false)
by activity.activity-key.
if (activity.action = 23) then mod$ser.batch_sn = activity.cparam1.
else mod$ser.batch_sn = ''.
{&log} mod$ser.serialNumber ' ' mod$ser.batch_sn ' ' mod$ser.activityKey ' ' activity.action skip.
end.
{&log} 'move from process "data process", start after actkey:' startActKey2 skip.
for each activity no-lock
where activity.activity-key > startActKey2
and (activity.action ge 10 and activity.action le 13 or false),
first location no-lock
where location.location-key = activity.location-key
and (location.process_name matches "*data process*" or false),
first serial no-lock
where serial.serial-key = activity.serial-key,
first dpart no-lock
where dpart.copics_part-key = serial.part-key
and (dpart.prod_line_id = 'Pump Laser' or dpart.prod_line_id = 'HRS Pump' or false)
by activity.activity-key.
lastActKey2 = activity.activity-key.
find first mod$ser
where mod$ser.serKey = serial.serial-key no-error.
if not available mod$ser then do:
create mod$ser.
assign
mod$ser.serKey = serial.serial-key
mod$ser.serialNumber = serial.serial_number
mod$ser.module_pn = dpart.product_number
mod$ser.activityKey = activity.activity-key
mod$ser.batch_sn = ''
mod$ser.mod_select = serial.module_select
mod$ser.partKey = serial.part-key.
{&log} mod$ser.serialNumber skip.
end.
end.
define buffer child$mod$ser for mod$ser.
for each mod$ser
where mod$ser.mod_select = 7,
each component no-lock
where component.serial-key = mod$ser.serKey
and (component.ref_designator = "UNITSN" or false)
and (component.tracked or false)
and (component.removed = 0 or false),
first serial no-lock
where serial.serial_number = component.component_id,
first dpart no-lock
where dpart.copics_part-key = serial.part-key.
find first child$mod$ser
where child$mod$ser.serKey = serial.serial-key no-error.
if not available child$mod$ser then do:
create child$mod$ser.
assign
child$mod$ser.serKey = serial.serial-key
child$mod$ser.serialNumber = serial.serial_number
child$mod$ser.module_pn = dpart.product_number
child$mod$ser.batch_sn = mod$ser.serialNumber
child$mod$ser.mod_select = serial.module_select
child$mod$ser.partKey = serial.part-key.
end.
else
child$mod$ser.batch_sn = mod$ser.serialNumber.
{&log} 'usn:' child$mod$ser.serialNumber ' bsn:' child$mod$ser.batch_sn ' pn:' child$mod$ser.module_pn skip.
end.
define buffer this$component for component.
{&log} 'pulling components, fill ModAttach' skip.
for each mod$ser
where mod$ser.mod_select ne 7,
each this$component no-lock
where this$component.serial-key = mod$ser.serkey
and (this$component.ref_designator = {&MOD-REFDES} or
this$component.ref_designator = {&COC-REFDES} or false)
and (this$component.removed = 0 or false).
find first ModAttach
where ModAttach.unit_sn = mod$ser.serialNumber no-error.
if not available ModAttach then create ModAttach.
assign
ModAttach.unit_sn = mod$ser.serialNumber
ModAttach.module_pn = mod$ser.module_pn
ModAttach.module_sn = if this$component.ref_designator = {&MOD-REFDES} then this$component.component_id else ModAttach.module_sn.
ModAttach.cos_sn = if this$component.ref_designator = {&COC-REFDES} then this$component.component_id else ModAttach.cos_sn.
ModAttach.batch_sn = mod$ser.batch_sn.
{&log} 'usn:' ModAttach.unit_sn ' msn:' ModAttach.module_sn ' cosn:' ModAttach.cos_sn ' bsn:' ModAttach.batch_sn skip.
end.
{&log} 'last key processed:' lastActKey skip.
do transaction:
find first customcode exclusive
where custseq = {&MYCUST-SEQ} no-error.
if not available customcode then do:
{&log} 'Problem locking customcode record' skip.
{&log} error-status:get-message(1) skip.
return.
end.
customcode.i1 = lastActKey.
customcode.i2 = lastActKey2.
end.
{&log} 'End' skip(1).
And this is what I've come up with so far.
$body$
declare coc_refdes text = 'coc';
declare mod_refdes text = 'mod';
declare var_last_activity_key integer=0;
declare var_max_activity_key integer=0;
begin
var_last_activity_key := (select activity_key from settings where configkey = configkey_key);
var_max_activity_key := (select max(activity_key)
from activity a where
a.activity_key>var_last_activity_key
;
create temp table modattach
(
batch_sn varchar(100),
unit_sn varchar(100),
module_sn varchar(100),
module_pn varchar(100),
cos_sn varchar(100)
) on commit drop;
drop table if exists mod$ser;
create temporary table mod$ser
(
serkey integer, --inx
serialnumber varchar(100),
module_pn varchar(100),
activitykey integer,
batch_sn varchar(100),
mod_select integer,
partkey integer
) on commit drop;
create index mod$ser_i on mod$ser(serkey, mod_select);
insert into mod$ser
select distinct
s.serial_key,
s.serial_number,
p.product_number as module_pn,
a.activity_key,
'' as batch_sn,
s.unit_type as mod_select,
s.part_key as partkey
from activity a
inner join serial s on a.serial_key = s.serial_key
inner join part p on p.part_key = s.part_key
inner join component c on c.serial_key = s.serial_key
where a.activity_key>var_last_activity_key and a.activity_key<var_max_activity_key
and lower(p.prod_line_id) in ('pump laser','hrs pump')
and a.action = 27
and lower(c.ref_designator) in(coc_refdes,mod_refdes)
and c.removed=0;
update mod$ser
set batch_sn = a.a1
from activity a
where
mod$ser.serkey = a.serial_key
and mod$ser.mod_select != 7
and a.action = 23;
insert into mod$ser
select a.serial_key,
a.serial_number,
a.product_number,
a.activity_key,
'' as batch_sn,
a.unit_type,
a.part_key
from mod$ser
right join
(
select distinct
s.serial_key,
s.serial_number,
p.product_number,
a.activity_key,
'' batch_sn,
s.unit_type,
s.part_key
from part p
inner join serial s on p.part_key = s.part_key
inner join activity a on s.serial_key = a.serial_key and p.part_number = a.a1
inner join location l on l.location_key= a.location_key
inner join process pro on l.process_key = pro.process_key
where a.activity_key>var_last_activity_key and a.activity_key<var_max_activity_key
and lower(p.prod_line_id) in ('pump laser','hrs pump')
and lower(pro.process_name) like lower('%data process%')
and (a.action > 10 and a.action < 13)
)a on mod$ser.serkey = a.serial_key
where mod$ser.serkey is null;
update mod$ser
set batch_sn = a.serial_number
from (
select s.serial_key,
s.serial_number,
p.product_number,
0 activity_key,
mod$ser.serialnumber,
s.unit_type,
s.part_key
from mod$ser
inner join component c on mod$ser.serkey=c.serial_key and c.ref_designator='unitsn' and c.component_type=1 and c.removed=0
inner join serial s on c.component_id=s.serial_number
inner join part p on s.part_key=p.part_key
where mod$ser.mod_select=7
) a where mod$ser.serkey = a.serial_key
insert into mod$ser
select a.serial_key,
a.serial_number,
a.product_number,
a.activity_key,
'' as batch_sn,
a.unit_type,
a.part_key
from mod$ser
right join (
select s.serial_key,
s.serial_number,
p.product_number,
0 activity_key,
mod$ser.serialnumber,
s.unit_type,
s.part_key
from mod$ser
inner join component c on mod$ser.serkey=c.serial_key and c.ref_designator='unit_sn' and c.component_type=1 and c.removed=0
inner join serial s on c.component_id=s.serial_number
inner join part p on s.part_key=p.part_key
where mod$ser.mod_select=7
) a on mod$ser.serkey = a.serial_key
where mod$ser.serkey is null;
insert into modattach
select distinct
mod$ser.batch_sn,
mod$ser.serialnumber,
cmod.component_id module_sn,
mod$ser.module_pn,
ccoc.component_id cos_sn
from
mod$ser
left join component ccoc on mod$ser.serkey = ccoc.serial_key and ccoc.ref_designator=coc_refdes and ccoc.removed=0
left join component cmod on mod$ser.serkey = cmod.serial_key and cmod.ref_designator=mod_refdes and cmod.removed=0
where mod$ser.mod_select !=7;
if (debug_mode<1) then
update settings
set activity_key = var_max_activity_key
where configkey = configkey_key;
end if;
return query
select * from modattach order by module_pn;
end;
$body$
Thanks for your help guys.
Continue reading...
The function extracts and processes some data that is then inserted into a data warehouse by another program. It runs every few minutes and keeps a record of the last processed data so it knows where it left off.
End user is complaining of some missing data when using the translated function. Could you point out if I'm misinterpreting any of the syntaxis in the Progress code?
This is the original function:
&scoped-define COC-REFDES "coc"
&scoped-define MOD-REFDES "mod"
define temp-table ModAttach no-undo
field batch_sn as char format "x(22)"
field unit_sn as char format "x(22)"
field module_sn as char format "x(22)"
field module_pn as char format "x(22)"
field cos_sn as char format "x(22)"
index usnidx is primary unique unit_sn.
define output parameter table for ModAttach.
define temp-table mod$ser no-undo
field serKey as int
field serialNumber as char
field module_pn as char
field activityKey as int
field batch_sn as char
field mod_select as int
field partKey as int
index serkeyidx serKey.
define temp-table mod$comp no-undo
field serkey as int
field actkey as int
field compNum as int
field compKey as int
field refDes as char
field compId as char
index seridx serkey
index actidx actkey.
define temp-table modData no-undo
field serKey as int
field unit_sn as char
field module_sn as char format "x(22)"
field module_pn as char format "x(22)"
field cos_sn as char format "x(22)"
field addCompActKey as int
index serkeyidx serKey.
def var startActKey as int no-undo.
def var lastActKey as int no-undo.
def var lastActKey2 as int no-undo.
def var startActKey2 as int no-undo.
def var pk_id as int no-undo.
define buffer act$loc for location.
define buffer mov$loc for location.
define buffer d$project for project.
define stream logg.
{&log} 'Start ' today '@' string(time,"hh:mm:ss") skip.
{&log} GetModAttach skip.
find first customcode no-lock
where custseq = {&MYCUST-SEQ} no-error.
if not available customcode then do:
{&log} 'customcode {&MYCUST-SEQ} not found' skip.
return.
end.
assign
startActKey = customcode.i1
lastActKey = customcode.i1
startActKey2 = customcode.i2
lastActKey2 = customcode.i2.
define buffer changed$dpart for dpart.
{&log} 'part change, start after actkey:' startActKey skip.
for each activity no-lock
where activity.activity-key > startActKey
and (activity.action = 27 or false),
first serial no-lock
where serial.serial-key = activity.serial-key,
first component no-lock
where component.serial-key = serial.serial-key
and (component.ref_designator = {&MOD-REFDES} or component.ref_designator = {&COC-REFDES} or false)
and (component.removed = 0 or false),
first dpart no-lock
where dpart.copics_part-key = serial.part-key
and (dpart.prod_line_id = 'Pump Laser' or dpart.prod_line_id = 'HRS Pump' or false),
first changed$dpart no-lock
where changed$dpart.copics_part_number = activity.cparam1
and (dpart.prod_line_id = 'Pump Laser' or dpart.prod_line_id = 'HRS Pump' or false)
by activity.activity-key.
lastActKey = activity.activity-key.
if dpart.product_number begins "GN-" or changed$dpart.product_number begins "GN-" then do:
find first mod$ser
where mod$ser.serKey = serial.serial-key no-error.
if not available mod$ser then create mod$ser.
assign
mod$ser.serKey = serial.serial-key
mod$ser.serialNumber = serial.serial_number
mod$ser.module_pn = if dpart.product_number begins "GN-" then dpart.product_number else changed$dpart.product_number
mod$ser.activityKey = activity.activity-key
mod$ser.batch_sn = ''
mod$ser.mod_select = serial.module_select
mod$ser.partKey = serial.part-key.
end.
end.
{&log} 'determine batch sn' skip.
for each mod$ser where mod$ser.mod_select ne 7,
each activity no-lock
where activity.serial-key = mod$ser.serkey
and (activity.action = 23 or activity.action = 24 or false)
by activity.activity-key.
if (activity.action = 23) then mod$ser.batch_sn = activity.cparam1.
else mod$ser.batch_sn = ''.
{&log} mod$ser.serialNumber ' ' mod$ser.batch_sn ' ' mod$ser.activityKey ' ' activity.action skip.
end.
{&log} 'move from process "data process", start after actkey:' startActKey2 skip.
for each activity no-lock
where activity.activity-key > startActKey2
and (activity.action ge 10 and activity.action le 13 or false),
first location no-lock
where location.location-key = activity.location-key
and (location.process_name matches "*data process*" or false),
first serial no-lock
where serial.serial-key = activity.serial-key,
first dpart no-lock
where dpart.copics_part-key = serial.part-key
and (dpart.prod_line_id = 'Pump Laser' or dpart.prod_line_id = 'HRS Pump' or false)
by activity.activity-key.
lastActKey2 = activity.activity-key.
find first mod$ser
where mod$ser.serKey = serial.serial-key no-error.
if not available mod$ser then do:
create mod$ser.
assign
mod$ser.serKey = serial.serial-key
mod$ser.serialNumber = serial.serial_number
mod$ser.module_pn = dpart.product_number
mod$ser.activityKey = activity.activity-key
mod$ser.batch_sn = ''
mod$ser.mod_select = serial.module_select
mod$ser.partKey = serial.part-key.
{&log} mod$ser.serialNumber skip.
end.
end.
define buffer child$mod$ser for mod$ser.
for each mod$ser
where mod$ser.mod_select = 7,
each component no-lock
where component.serial-key = mod$ser.serKey
and (component.ref_designator = "UNITSN" or false)
and (component.tracked or false)
and (component.removed = 0 or false),
first serial no-lock
where serial.serial_number = component.component_id,
first dpart no-lock
where dpart.copics_part-key = serial.part-key.
find first child$mod$ser
where child$mod$ser.serKey = serial.serial-key no-error.
if not available child$mod$ser then do:
create child$mod$ser.
assign
child$mod$ser.serKey = serial.serial-key
child$mod$ser.serialNumber = serial.serial_number
child$mod$ser.module_pn = dpart.product_number
child$mod$ser.batch_sn = mod$ser.serialNumber
child$mod$ser.mod_select = serial.module_select
child$mod$ser.partKey = serial.part-key.
end.
else
child$mod$ser.batch_sn = mod$ser.serialNumber.
{&log} 'usn:' child$mod$ser.serialNumber ' bsn:' child$mod$ser.batch_sn ' pn:' child$mod$ser.module_pn skip.
end.
define buffer this$component for component.
{&log} 'pulling components, fill ModAttach' skip.
for each mod$ser
where mod$ser.mod_select ne 7,
each this$component no-lock
where this$component.serial-key = mod$ser.serkey
and (this$component.ref_designator = {&MOD-REFDES} or
this$component.ref_designator = {&COC-REFDES} or false)
and (this$component.removed = 0 or false).
find first ModAttach
where ModAttach.unit_sn = mod$ser.serialNumber no-error.
if not available ModAttach then create ModAttach.
assign
ModAttach.unit_sn = mod$ser.serialNumber
ModAttach.module_pn = mod$ser.module_pn
ModAttach.module_sn = if this$component.ref_designator = {&MOD-REFDES} then this$component.component_id else ModAttach.module_sn.
ModAttach.cos_sn = if this$component.ref_designator = {&COC-REFDES} then this$component.component_id else ModAttach.cos_sn.
ModAttach.batch_sn = mod$ser.batch_sn.
{&log} 'usn:' ModAttach.unit_sn ' msn:' ModAttach.module_sn ' cosn:' ModAttach.cos_sn ' bsn:' ModAttach.batch_sn skip.
end.
{&log} 'last key processed:' lastActKey skip.
do transaction:
find first customcode exclusive
where custseq = {&MYCUST-SEQ} no-error.
if not available customcode then do:
{&log} 'Problem locking customcode record' skip.
{&log} error-status:get-message(1) skip.
return.
end.
customcode.i1 = lastActKey.
customcode.i2 = lastActKey2.
end.
{&log} 'End' skip(1).
And this is what I've come up with so far.
$body$
declare coc_refdes text = 'coc';
declare mod_refdes text = 'mod';
declare var_last_activity_key integer=0;
declare var_max_activity_key integer=0;
begin
var_last_activity_key := (select activity_key from settings where configkey = configkey_key);
var_max_activity_key := (select max(activity_key)
from activity a where
a.activity_key>var_last_activity_key
;create temp table modattach
(
batch_sn varchar(100),
unit_sn varchar(100),
module_sn varchar(100),
module_pn varchar(100),
cos_sn varchar(100)
) on commit drop;
drop table if exists mod$ser;
create temporary table mod$ser
(
serkey integer, --inx
serialnumber varchar(100),
module_pn varchar(100),
activitykey integer,
batch_sn varchar(100),
mod_select integer,
partkey integer
) on commit drop;
create index mod$ser_i on mod$ser(serkey, mod_select);
insert into mod$ser
select distinct
s.serial_key,
s.serial_number,
p.product_number as module_pn,
a.activity_key,
'' as batch_sn,
s.unit_type as mod_select,
s.part_key as partkey
from activity a
inner join serial s on a.serial_key = s.serial_key
inner join part p on p.part_key = s.part_key
inner join component c on c.serial_key = s.serial_key
where a.activity_key>var_last_activity_key and a.activity_key<var_max_activity_key
and lower(p.prod_line_id) in ('pump laser','hrs pump')
and a.action = 27
and lower(c.ref_designator) in(coc_refdes,mod_refdes)
and c.removed=0;
update mod$ser
set batch_sn = a.a1
from activity a
where
mod$ser.serkey = a.serial_key
and mod$ser.mod_select != 7
and a.action = 23;
insert into mod$ser
select a.serial_key,
a.serial_number,
a.product_number,
a.activity_key,
'' as batch_sn,
a.unit_type,
a.part_key
from mod$ser
right join
(
select distinct
s.serial_key,
s.serial_number,
p.product_number,
a.activity_key,
'' batch_sn,
s.unit_type,
s.part_key
from part p
inner join serial s on p.part_key = s.part_key
inner join activity a on s.serial_key = a.serial_key and p.part_number = a.a1
inner join location l on l.location_key= a.location_key
inner join process pro on l.process_key = pro.process_key
where a.activity_key>var_last_activity_key and a.activity_key<var_max_activity_key
and lower(p.prod_line_id) in ('pump laser','hrs pump')
and lower(pro.process_name) like lower('%data process%')
and (a.action > 10 and a.action < 13)
)a on mod$ser.serkey = a.serial_key
where mod$ser.serkey is null;
update mod$ser
set batch_sn = a.serial_number
from (
select s.serial_key,
s.serial_number,
p.product_number,
0 activity_key,
mod$ser.serialnumber,
s.unit_type,
s.part_key
from mod$ser
inner join component c on mod$ser.serkey=c.serial_key and c.ref_designator='unitsn' and c.component_type=1 and c.removed=0
inner join serial s on c.component_id=s.serial_number
inner join part p on s.part_key=p.part_key
where mod$ser.mod_select=7
) a where mod$ser.serkey = a.serial_key
insert into mod$ser
select a.serial_key,
a.serial_number,
a.product_number,
a.activity_key,
'' as batch_sn,
a.unit_type,
a.part_key
from mod$ser
right join (
select s.serial_key,
s.serial_number,
p.product_number,
0 activity_key,
mod$ser.serialnumber,
s.unit_type,
s.part_key
from mod$ser
inner join component c on mod$ser.serkey=c.serial_key and c.ref_designator='unit_sn' and c.component_type=1 and c.removed=0
inner join serial s on c.component_id=s.serial_number
inner join part p on s.part_key=p.part_key
where mod$ser.mod_select=7
) a on mod$ser.serkey = a.serial_key
where mod$ser.serkey is null;
insert into modattach
select distinct
mod$ser.batch_sn,
mod$ser.serialnumber,
cmod.component_id module_sn,
mod$ser.module_pn,
ccoc.component_id cos_sn
from
mod$ser
left join component ccoc on mod$ser.serkey = ccoc.serial_key and ccoc.ref_designator=coc_refdes and ccoc.removed=0
left join component cmod on mod$ser.serkey = cmod.serial_key and cmod.ref_designator=mod_refdes and cmod.removed=0
where mod$ser.mod_select !=7;
if (debug_mode<1) then
update settings
set activity_key = var_max_activity_key
where configkey = configkey_key;
end if;
return query
select * from modattach order by module_pn;
end;
$body$
Thanks for your help guys.
Continue reading...