Progress Script to T-SQL - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Progress Script to T-SQL

Author

Topic

WayneVanRooyen
Starting Member

2 Posts

Posted - 2009-01-22 : 08:43:15

I am trying to migrate some scripts from Progress 4Gl to T-SQL but have never written T-SQL

the current script is in progress and takes nearly 2 hours to run and finish and the db is only 500MB. Please could someone with experience help me get this to SQL so i can see if there is a performance boost. I am not to concerned about the last section where it outputs to a csv as i would rather just write this to a temp table that i can create a report on.

Progress Script

def var XXSTCO as dec no-undo.
def var XXAVCO as dec no-undo.
def var XXLACO as dec no-undo.
def var XXBUCO as dec no-undo.
def var XXPUOM as int no-undo.
def var XX1STD as char no-undo.
def var XXLSTD as char no-undo.
def var XXUSED as date no-undo.
def var XXRECI as recid no-undo.
def var XXSTYL like WDCODE no-undo.
def var XXITMN as char no-undo.
def var XXDATE as char no-undo.
def var XXAGEM as int no-undo.
def var XXSTKW as char no-undo.

def stream S_OUTPUT.
output stream S_OUTPUT to value(XXPATH).

/* First export column headings: */
/* put unformatted */
export stream S_OUTPUT delimiter ","
"Warehouse"
"StkWhse"
"Location"
"Item"
"Item Description"
"LookupName1"
"LookupName2"
"LookupName3"
"LookupName4"
"Analysis1"
"Analysis2"
"Analysis3"
"Analysis4"
"Analysis5"
"Analysis6"
"Colour"
"Dim"
"StockCategory"
"ProductClass"
"ProductGroup"
"ItemSeasonYear"
"MaterialType"
"StockQty"
"All&Iss"
"OpenStock"
"StdCost"
"AvCost"
"LastCost"
"BudgetCost"
"StdVal"
"AvVal"
"LastVal"
"BudgetVal"
"LstRec"
"LstIssue"
"LstPurchase"
"LstRecAge"
"ItemDescCol"
"ItemDesc"
"AutoUse"
"SubItem"
"ReplPrd"
"Tariff"
"LndCst"
"LndVal".

ML:
for each MLOCNF no-lock where MLCOMP = QQCOMP
and (MLSQTY NE 0 or MLIQTY ne 0),
first MMSTRF no-lock where MMCOMP = QQCOMP
and MMITEM = MLITEM
and ((XXTYPE > "" and
MMMTYP = XXTYPE) or
(XXTYPE = "" and
MMMTYP >= XXTYPE)),
first MCOLRF no-lock where MCCOMP = QQCOMP
and MCITEM = MLITEM
and MCICLR = MLICLR
and MCIDMN = MLIDMN:

assign
/* XXUSED = 01/01/1901 */
/* XX1STD = "" */
/* XXLSTD = "" */
/* XXRECI = ? */
/* XXSTYL = "" */
XXSTCO = 0
XXAVCO = 0
XXLACO = 0
XXBUCO = 0.

find ZCNTLF where ZCNTLF.ZCCOMP = QQCOMP
and ZCNTLF.ZCELTY = "UM"
and ZCNTLF.ZCELKY = MMPUOM no-lock no-error.
if not avail ZCNTLF or ZCNTLF.ZCVAN1 = 0 then
XXPUOM = 1.
else
XXPUOM = ZCNTLF.ZCVAN1.

find ZCNTLF where ZCNTLF.ZCCOMP = QQCOMP
and ZCNTLF.ZCELTY = "WH"
and ZCNTLF.ZCELKY = MLWHSE no-lock no-error.
if avail ZCNTLF then
XXSTKW = (if substring(ZCVAA2,3,1) = "n" then "NonStock" else "Stock").
else
XXSTKW = "Unknown".

find MPCSTF where MPCOMP = QQCOMP
and MPITEM = MLITEM
and MPICLR = MLICLR
and MPIDMN = MLIDMN
no-lock no-error.
if not avail MPCSTF then
find MPCSTF where MPCOMP = QQCOMP
and MPITEM = MLITEM
and MPICLR = ""
and MPIDMN = ""
no-lock no-error.

if avail MPCSTF then
assign XXSTCO = MPSTCO / XXPUOM / 100
XXAVCO = MPAVCO / XXPUOM / 100
XXLACO = MPLACO / XXPUOM / 100
XXBUCO = MPBUCO / XXPUOM / 100.

/** 1st and last reciept dates */
/* find first MIRECF where MICOMP = QQCOMP */
/* and MIITEM = MLITEM */
/* and MIICLR = MLICLR */
/* and MIIDMN = MLIDMN */
/* and MIMVTY = "GR" */
/* no-lock no-error. */
/* if not avail MIRECF then */
/* assign XX1STD = "" */
/* XXLSTD = "". */
/* else */
/* do: */
/* XX1STD = string(year(midttr),"9999") + "/" + string(month(midttr),"99"). */
/* find last MIRECF where MICOMP = QQCOMP */
/* and MIITEM = MLITEM */
/* and MIICLR = MLICLR */
/* and MIIDMN = MLIDMN */
/* and MIMVTY = "GR" */
/* no-lock no-error. */
/* if avail MIRECF then */
/* do: */
/* assign */
/* XXAGEM = ((year(today) - year(MIDTTR)) * 12) + (month(today) - month(MIDTTR)) */
/* XXLSTD = string(year(midttr),"9999") + "/" + string(month(midttr),"99"). */
/* end. */
/* else */
/* assign XXLSTD = "" */
/* XXAGEM = 0. */
/* end. */

/* FIND LAST USED DATE */
/* for each WLINKF where WLCOMP = QQCOMP */
/* and WLITEM = MLITEM */
/* and WLICLR = MLICLR */
/* and WLIDMN = MLIDMN */
/* no-lock: */
/* if WLDTZZ > XXUSED then */
/* assign XXUSED = WLDTZZ */
/* XXRECI = RECID(WLINKF). */
/* end. */
/* */
/* if XXRECI ne ? then */
/* do: */
/* find WLINKF where RECID(WLINKF) = XXRECI no-lock no-error. */
/* if avail WLINKF then */
/* do: */
/* find WDETLF where WDCOMP = QQCOMP */
/* and WDPRNO = WLPRNO */
/* and WDPRLN = WLPRLN no-lock no-error. */
/* if avail WDETLF then */
/* XXSTYL = WDCODE. */
/* end. */
/* end. */
/* */
/* if XXUSED = 01/01/1901 then */
/* XXDATE = "". */
/* else */
/* XXDATE = string(year(XXUSED),"9999") + "/" + string(month(XXUSED),"99"). */

export stream S_OUTPUT delimiter ","
TRIM(func_getdesc("WH",MLWHSE,MLWHSE)) /* Warehouse*/
XXSTKW
MLLOCN
trim(MLITEM)
trim(MMDESC[1]) + " " + MMDESC[2]
MMNAM1
MMNAM2
MMNAM3 /*7930*/
MMNAM4
TRIM(func_getdesc("AM","01" + MMANAL[1],MMANAL[1]))/* RM Anal 1 */
TRIM(func_getdesc("AM","02" + MMANAL[2],MMANAL[2]))/* RM Anal 2 */
TRIM(func_getdesc("AM","03" + MMANAL[3],MMANAL[3]))/* RM Anal 3 */
TRIM(func_getdesc("AM","04" + MMANAL[4],MMANAL[4]))/* RM Anal 4 */
TRIM(func_getdesc("AM","05" + MMANAL[5],MMANAL[5]))/* RM Anal 5 */
TRIM(func_getdesc("AM","06" + MMANAL[6],MMANAL[6]))/* RM Anal 6 */
TRIM(func_getdesc("FC",MLICLR,MLICLR)) /* Colour*/
trim(MLIDMN) /*Dim*/
TRIM(func_getdesc("CG",MCCATG,MCCATG)) /* CatG*/
TRIM(func_getdesc("MC",MMMCLS,MMMCLS)) /* Class*/
TRIM(func_getdesc("MG",MMMGRP,MMMGRP)) /* Group*/
trim(MMSEYR) /* SeasonYear*/
TRIM(func_getdesc("MT",MMMTYP,MMMTYP)) /* Type*/
MLSQTY /* Stock */
MLIQTY /*Alloc/Iss QTy*/
MLSQTY - MLIQTY /*Open QTy*/
XXSTCO /* Std cost */
XXAVCO /* Av Cost */
XXLACO /* Last Cost */
XXBUCO /* Budget Cost */
XXSTCO * MLSQTY /* Std Val */
XXAVCO * MLSQTY /* Av Val */
XXLACO * MLSQTY /* Last Val */
XXBUCO * MLSQTY /* Budget Val */
string(year(MCDTLR),"9999") + "/" + string(month(MCDTLR),"99") /* last Receipt */
string(year(MCDTLI),"9999") + "/" + string(month(MCDTLI),"99") /* Last Issue */
string(year(MCDTLP),"9999") + "/" + string(month(MCDTLP),"99") /* Last Purchase */
/* XX1STD /* 1st Rec Date */ */
/* XXLSTD /* Last Rec Date */ */
/* XXDATE /* Last Used Date */ */
/* trim(XXSTYL) /* Last STyle Used */ */
((year(today) - year(MCDTLR)) * 12) + (month(today) - month(MCDTLR)) /* Age based on lst rec*/
trim(MLITEM) + " " +
trim(MMDESC[1]) + " " +
trim(MMDESC[2]) + " " +
trim(func_getdesc("FC",MLICLR,MLICLR))
trim(MLITEM) + " " +
trim(MMDESC[1]) + " " +
trim(MMDESC[2])
MMFCON /*Consume at std */
MMISUB /*Substitute item */
MMREPL /* replenishment period */
MMTARF /* Tarrif code */
(IF XXBUCO NE 0 THEN XXBUCO ELSE XXSTCO) /* Traffic LndCost */
(IF XXBUCO NE 0 THEN XXBUCO * MLSQTY ELSE XXSTCO * MLSQTY). /* Traffic LndVal*/

end.
output stream S_OUTPUT close.

/* hide message no-pause. */
/* message "Export Complete.". */
pause 0.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-22 : 08:46:46

RAISERROR('This is the message', 10, 1) WITH NO_WAIT

E 12°55'05.63"
N 56°04'39.26"

WayneVanRooyen
Starting Member

2 Posts

Posted - 2009-01-22 : 09:42:47

I dont get what that was Peso.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources