[stackoverflow] [progress Openedge Abl] Convert Progress Query To Sql

[Harjot Singh]

Here is my Code Sample and i need to convert this progress code in sql code for a application and i need it very urgently and please help me out with this query. Thanks in advance

DEFINE VARIABLE cWhole AS CHARACTER.
DEFINE VARIABLE div AS CHARACTER.
def var c as int.
def var wWhole like wholesaler-competitor.wholesaler.
def var pcnt as int.
def var tcnt as int.
def var OnTime as logical.
def var prct as decimal.

cWhole = "KRATL,KRCIN,KRGRL,KRCOL,KRIND,KRLVL,KRMEM,KRRNK,KROGR,KROGR,KRDIL,"
+ "KING,KRFRY,FREDM,KRRAL,KRFFL,KRQFC,KRSMI,F4L,JAYC,KRNSH,KRRND".

div = "011,014,018,016,021,024,025,029,034,035,615,"
+ "620,660,701,703,704,705,706,708,090,026,534".


def temp-table tt-kis
field job like kro-items-sent.Job-No
field div like kro-items-sent.Division
field mkt like kro-items-sent.Market
field cmp like kro-items-sent.Comp
field ct like kro-items-sent.Check-Type
field upc like kro-items-sent.UPC-Code
field price like kro-items-sent.price
field ind like kro-items-sent.Price-Indicator
field edate like kro-items-sent.Ext-Date
field wend like kro-items-sent.Week-End
index main
job
div
mkt
cmp
ct
upc
price
ind
edate
index jb-mk-ck
job
mkt
ct.

def temp-table tt-job
field job like job-schedule.job-no
field div like kro-items-sent.Division
index jb
job.

def temp-table tt-ssct
field ct like sku-subset.check-type
field sscnt as int
index chktp
ct.

{rds/src/mel-match.rd}

for each kro-items-sent no-lock
where Ext-Date >= sdate
and Ext-Date <= edate:

find first tt-kis
where tt-kis.job = kro-items-sent.Job-No
and tt-kis.div = kro-items-sent.Division
and tt-kis.mkt = kro-items-sent.Market
and tt-kis.cmp = kro-items-sent.Comp
and tt-kis.ct = kro-items-sent.Check-Type
and tt-kis.upc = kro-items-sent.UPC-Code
and tt-kis.price = kro-items-sent.price
and tt-kis.ind = kro-items-sent.Price-Indicator
and tt-kis.edate = kro-items-sent.Ext-Date no-lock no-error.
if not available tt-kis then do:
create tt-kis.
assign tt-kis.job = kro-items-sent.Job-No
tt-kis.div = kro-items-sent.Division
tt-kis.mkt = kro-items-sent.Market
tt-kis.cmp = kro-items-sent.Comp
tt-kis.ct = kro-items-sent.Check-Type
tt-kis.upc = kro-items-sent.UPC-Code
tt-kis.price = kro-items-sent.price
tt-kis.ind = kro-items-sent.Price-Indicator
tt-kis.edate = kro-items-sent.Ext-Date
tt-kis.wend = kro-items-sent.Week-End.
end.

find first job-schedule
where job-schedule.job-no = kro-items-sent.Job-No no-lock no-error.
if available job-schedule then do:
find first tt-job
where tt-job.job = kro-items-sent.Job-No no-lock no-error.
if not available tt-job then do:

create tt-job.
assign tt-job.job = kro-items-sent.Job-No
tt-job.div = kro-items-sent.Division.

end.
end.

end.

output to value(ofile).

export delimiter "\009"
"Division"
"Account"
"Wholesaler"
"Market"
"Comp"
"Job"
"Store ID"
"Store Name"
"Store City"
"Store ST"
"RetailData Check Type"
"Kroger Check Type"
"Primary Count"
"Total Count"
"Subset Count"
"Percent Collected"
"Week End Date"
"Job Due Date"
"Extract Date"
"On Time".

for each tt-job no-lock:

division:
do c = 1 to num-entries(div):

if entry(c,div) = tt-job.div then do:
wWhole = entry(c,cWhole).
leave division.
end.

end.

find first job-schedule
where job-schedule.job-no = tt-job.job no-lock no-error.

find first store of job-schedule no-lock.

find first wholesaler-competitor
where wholesaler-competitor.wholesaler = wWhole
and wholesaler-competitor.Comp-chain-no = job-schedule.chain-no
and wholesaler-competitor.Comp-zone = job-schedule.zone
no-lock no-error.

empty temp-table tt-ssct.
for each sku-subset no-lock
where sku-subset.wholesaler = ""
and sku-subset.subset-code = job-schedule.subset-code:

do c = 1 to num-entries(trim(sku-subset.check-type)):

find first tt-ssct
where tt-ssct.ct = entry(c,trim(sku-subset.check-type))
no-lock no-error.
if not available tt-ssct then do:
create tt-ssct.
assign tt-ssct.ct = entry(c,trim(sku-subset.check-type)).
end.
tt-ssct.sscnt = tt-ssct.sscnt + 1.

end.
end.

for each tt-ssct no-lock:

assign pcnt = 0
tcnt = 0.
for each tt-kis no-lock
where tt-kis.job = job-schedule.job-no
and tt-kis.mkt = wholesaler-competitor.Division
and tt-kis.ct = tt-ssct.ct
break by tt-kis.upc:

if first-of(tt-kis.upc)
then pcnt = pcnt + 1.

tcnt = tcnt + 1.

end.

if tt-kis.edate <= job-schedule.due-date
then OnTime = Yes.
else OnTime = No.

prct = (pcnt / tt-ssct.sscnt) * 100.

put unformatted tt-kis.div.
put control "\009".
put unformatted job-schedule.accountid.
put control "\009".
if available wholesaler-competitor
then put unformatted wholesaler-competitor.wholesaler.
put control "\009".
put unformatted tt-kis.mkt.
put control "\009".
put unformatted tt-kis.cmp.
put control "\009".
put unformatted job-schedule.job-no.
put control "\009".
put unformatted store.StoreID.
put control "\009".
put unformatted store.StoreName.
put control "\009".
put unformatted store.City.
put control "\009".
put unformatted store.State.
put control "\009".
put unformatted job-schedule.check-type.
put control "\009".
put unformatted tt-ssct.ct.
put control "\009".
put unformatted pcnt.
put control "\009".
put unformatted tcnt.
put control "\009".
put unformatted tt-ssct.sscnt.
put control "\009".
put unformatted prct.
put control "\009".
put unformatted tt-kis.wend.
put control "\009".
put unformatted job-schedule.due-date.
put control "\009".
put unformatted tt-kis.edate.
put control "\009".
put unformatted OnTime.

put CONTROL "\015\012".


end.

end.


Here is my Code Sample and i need to convert this progress code in sql code for a application and i need it very urgently and please help me out with this query. Thanks in advance

Continue reading...