The following runs just fine but none of the sql commands actually execute even though if I paste this in the server interactive window they do work.
Also the UI_Information window doesn't pop up????
Dim SQL as c
Dim conn as SQL::connection
Dim args as SQL::Arguments
Dim rs as SQL::ResultSet
args.add("group",e.datasubmitted.Group)
args.add("PERIOD",e.datasubmitted.Period)
args.add("YEAR",e.datasubmitted.Year)
vtitle1 = "File Building"
vmsg1 = "Please Wait"
ui_msg_box(vtitle1,vmsg1,UI_INFORMATION_SYMBOL)
SQL = "DELETE FROM cplib.VAR015_HOLD"
conn.Open("::Name::CPLAW")
flag = conn.execute(SQL)
SQL = "insert into cplib.var015_hold SELECT RU.company as rollup_company, RU.unit as rollup_unit, RU.description as rollup_description, L.company, L.operation, L.customer_id, CHAR(L.advdep_id) as advdep_id, L.event_date, value(G.advdep,0) as advdep_id, L.amount as log_amount, sum(value(R.amount,0)) as received, sum(value(P.amount,0)) as used, (sum(value(R.amount,0)) - sum(value(P.amount,0))) as balance, D.base_date from cplib.tadvdepl L left join (SELECT distinct company, max(date) as base_date from cplib.TCALENDAR where company in (SELECT distinct company from cplib.tgrpdtl where groupcode = :group) and year = :YEAR and period = :PERIOD GROUP BY company ) D on ( D.company = L.company ) left join (SELECT company, operation, advdep_id, sum(amount) as amount from cplib.tadvdepr where year < :YEAR or (year = :YEAR and period <= :PERIOD and ( company, operation ) in (SELECT company, operation from cplib.tgrpdtl where groupcode = :group) ) GROUP BY company, operation, advdep_id) R on (R.company = L.company and R.operation = L.operation and R.advdep_id = L.advdep_id) left join (SELECT company, operation, memo, sum(amount) as amount from cplib.TPAYMENT where (year < :YEAR or (year = :YEAR and period <= :PERIOD)) and payment_type = 'D' and ( company, operation ) in (SELECT company, operation from cplib.tgrpdtl where groupcode = :group) GROUP BY company, operation, memo ) P on ( P.company = L.company and P.operation = L.operation and P.memo = CHAR(L.advdep_id) ) left join (SELECT company, operation, advdep from cplib.tcorp_gl where year = :YEAR and period = :PERIOD and ( company, operation ) in (SELECT company, operation from cplib.tgrpdtl where groupcode = :group) ) G on (G.company = L.company and G.operation = L.operation) LEFT JOIN cplib.TROLLUP RU on (RU.groupcompany = L.company and RU.groupunit = L.operation and RU.code = '01') where ( L.company, L.operation ) in (SELECT company, operation from cplib.tgrpdtl where groupcode = :group) GROUP BY RU.company , RU.unit, RU.description, L.company, L.operation, L.customer_id, L.advdep_id, L.event_date, G.advdep, L.amount, D.base_date"
conn.Open("::Name::CPLAW")
flag = conn.execute(SQL,args)
conn.close()
vText = conn.callResult.text
vTitle1 = "FINI"
vMsg1 = vText
ui_msg_box(vTitle1, vMsg1,UI_INFORMATION_SYMBOL)
Also the UI_Information window doesn't pop up????
Dim SQL as c
Dim conn as SQL::connection
Dim args as SQL::Arguments
Dim rs as SQL::ResultSet
args.add("group",e.datasubmitted.Group)
args.add("PERIOD",e.datasubmitted.Period)
args.add("YEAR",e.datasubmitted.Year)
vtitle1 = "File Building"
vmsg1 = "Please Wait"
ui_msg_box(vtitle1,vmsg1,UI_INFORMATION_SYMBOL)
SQL = "DELETE FROM cplib.VAR015_HOLD"
conn.Open("::Name::CPLAW")
flag = conn.execute(SQL)
SQL = "insert into cplib.var015_hold SELECT RU.company as rollup_company, RU.unit as rollup_unit, RU.description as rollup_description, L.company, L.operation, L.customer_id, CHAR(L.advdep_id) as advdep_id, L.event_date, value(G.advdep,0) as advdep_id, L.amount as log_amount, sum(value(R.amount,0)) as received, sum(value(P.amount,0)) as used, (sum(value(R.amount,0)) - sum(value(P.amount,0))) as balance, D.base_date from cplib.tadvdepl L left join (SELECT distinct company, max(date) as base_date from cplib.TCALENDAR where company in (SELECT distinct company from cplib.tgrpdtl where groupcode = :group) and year = :YEAR and period = :PERIOD GROUP BY company ) D on ( D.company = L.company ) left join (SELECT company, operation, advdep_id, sum(amount) as amount from cplib.tadvdepr where year < :YEAR or (year = :YEAR and period <= :PERIOD and ( company, operation ) in (SELECT company, operation from cplib.tgrpdtl where groupcode = :group) ) GROUP BY company, operation, advdep_id) R on (R.company = L.company and R.operation = L.operation and R.advdep_id = L.advdep_id) left join (SELECT company, operation, memo, sum(amount) as amount from cplib.TPAYMENT where (year < :YEAR or (year = :YEAR and period <= :PERIOD)) and payment_type = 'D' and ( company, operation ) in (SELECT company, operation from cplib.tgrpdtl where groupcode = :group) GROUP BY company, operation, memo ) P on ( P.company = L.company and P.operation = L.operation and P.memo = CHAR(L.advdep_id) ) left join (SELECT company, operation, advdep from cplib.tcorp_gl where year = :YEAR and period = :PERIOD and ( company, operation ) in (SELECT company, operation from cplib.tgrpdtl where groupcode = :group) ) G on (G.company = L.company and G.operation = L.operation) LEFT JOIN cplib.TROLLUP RU on (RU.groupcompany = L.company and RU.groupunit = L.operation and RU.code = '01') where ( L.company, L.operation ) in (SELECT company, operation from cplib.tgrpdtl where groupcode = :group) GROUP BY RU.company , RU.unit, RU.description, L.company, L.operation, L.customer_id, L.advdep_id, L.event_date, G.advdep, L.amount, D.base_date"
conn.Open("::Name::CPLAW")
flag = conn.execute(SQL,args)
conn.close()
vText = conn.callResult.text
vTitle1 = "FINI"
vMsg1 = vText
ui_msg_box(vTitle1, vMsg1,UI_INFORMATION_SYMBOL)
Comment