Hi
I h ave created some code for a report but just takes ages can anyone see if they can find a faster way. There are lots of indexes for tables. I need to run it for 6 months but one day takes ages.
DEFINE STREAM stOrd.
define temp-table tt-Orders
field ttord-date LIKE ordstatus.kord-date
FIELD ttkspecsn LIKE ordstatus.kspecsn
field ttkcussn LIKE ordstatus.kcussn
FIELD ttkorder LIKE ordstatus.korder
FIELD ttkspeccode LIKE ordstatus.kspeccode
FIELD ttItem LIKE ovar.kvar-code
FIELD type1 LIKE specliq.type1
INDEX idx-cusno is PRIMARY ttkcussn.
OUTPUT STREAM stOrd TO c:\Caroline.csv.
FOR EACH ordstat WHERE ordstat.kord-date >= 02/01/07 AND ordstat.kord-date <= 03/01/07 NO-LOCK:
FOR EACH ordline OF ordstatus NO-LOCK:
FIND ovar WHERE ovar.kvseqnum = ordline.kvseqnum NO-LOCK.
IF AVAILABLE ovar THEN
FIND speclabel WHERE speclabel.label-code = ovar.kvar-code NO-LOCK.
IF AVAILABLE speclabel THEN
FIND specliq WHERE specliq.col-spec = speclabel.col-spec NO-LOCK.
CREATE tt-Orders.
ASSIGN tt-Orders.ttord-date = kord-date
tt-Orders.ttkcussn = ordstat.kcussn
tt-Orders.ttkorder = ordstat.korder
tt-Orders.ttkspecsn = ordstat.kspecsn
tt-Orders.ttkspeccode = ordstat.kspeccode
tt-Orders.ttItem = ovar.kvar-code
tt-Orders.type1[1] = specliq.type1[1]
tt-Orders.type1[2] = specliq.type1[2]
tt-Orders.type1[3] = specliq.type1[3]
tt-Orders.type1[4] = specliq.type1[4]
tt-Orders.type1[5] = specliq.type1[5]
tt-Orders.type1[6] = specliq.type1[6]
tt-Orders.type1[7] = specliq.type1[7]
tt-Orders.type1[8] = specliq.type1[8]
tt-Orders.type1[9] = specliq.type1[9]
tt-Orders.type1[10] = specliq.type1[10].
PUT STREAM stOrd UNFORMATTED
tt-Orders.ttord-date ","
tt-Orders.ttkcussn ","
tt-Orders.ttkorder ","
tt-Orders.ttkspecsn ","
tt-Orders.ttkspeccode ","
tt-Orders.ttItem ","
tt-Orders.type1[1] ","
tt-Orders.type1[2] ","
tt-Orders.type1[3] ","
tt-Orders.type1[4] ","
tt-Orders.type1[5] ","
tt-Orders.type1[6] ","
tt-Orders.type1[7] ","
tt-Orders.type1[8] ","
tt-Orders.type1[9] ","
tt-Orders.type1[10]
SKIP.
END.
END.
OUTPUT STREAM stOrd CLOSE.
I h ave created some code for a report but just takes ages can anyone see if they can find a faster way. There are lots of indexes for tables. I need to run it for 6 months but one day takes ages.
DEFINE STREAM stOrd.
define temp-table tt-Orders
field ttord-date LIKE ordstatus.kord-date
FIELD ttkspecsn LIKE ordstatus.kspecsn
field ttkcussn LIKE ordstatus.kcussn
FIELD ttkorder LIKE ordstatus.korder
FIELD ttkspeccode LIKE ordstatus.kspeccode
FIELD ttItem LIKE ovar.kvar-code
FIELD type1 LIKE specliq.type1
INDEX idx-cusno is PRIMARY ttkcussn.
OUTPUT STREAM stOrd TO c:\Caroline.csv.
FOR EACH ordstat WHERE ordstat.kord-date >= 02/01/07 AND ordstat.kord-date <= 03/01/07 NO-LOCK:
FOR EACH ordline OF ordstatus NO-LOCK:
FIND ovar WHERE ovar.kvseqnum = ordline.kvseqnum NO-LOCK.
IF AVAILABLE ovar THEN
FIND speclabel WHERE speclabel.label-code = ovar.kvar-code NO-LOCK.
IF AVAILABLE speclabel THEN
FIND specliq WHERE specliq.col-spec = speclabel.col-spec NO-LOCK.
CREATE tt-Orders.
ASSIGN tt-Orders.ttord-date = kord-date
tt-Orders.ttkcussn = ordstat.kcussn
tt-Orders.ttkorder = ordstat.korder
tt-Orders.ttkspecsn = ordstat.kspecsn
tt-Orders.ttkspeccode = ordstat.kspeccode
tt-Orders.ttItem = ovar.kvar-code
tt-Orders.type1[1] = specliq.type1[1]
tt-Orders.type1[2] = specliq.type1[2]
tt-Orders.type1[3] = specliq.type1[3]
tt-Orders.type1[4] = specliq.type1[4]
tt-Orders.type1[5] = specliq.type1[5]
tt-Orders.type1[6] = specliq.type1[6]
tt-Orders.type1[7] = specliq.type1[7]
tt-Orders.type1[8] = specliq.type1[8]
tt-Orders.type1[9] = specliq.type1[9]
tt-Orders.type1[10] = specliq.type1[10].
PUT STREAM stOrd UNFORMATTED
tt-Orders.ttord-date ","
tt-Orders.ttkcussn ","
tt-Orders.ttkorder ","
tt-Orders.ttkspecsn ","
tt-Orders.ttkspeccode ","
tt-Orders.ttItem ","
tt-Orders.type1[1] ","
tt-Orders.type1[2] ","
tt-Orders.type1[3] ","
tt-Orders.type1[4] ","
tt-Orders.type1[5] ","
tt-Orders.type1[6] ","
tt-Orders.type1[7] ","
tt-Orders.type1[8] ","
tt-Orders.type1[9] ","
tt-Orders.type1[10]
SKIP.
END.
END.
OUTPUT STREAM stOrd CLOSE.