Pleasure for me :)
:)
Best regards!
:)
Hi new question. I run a report in cognos, save as excel and import to access. I would like to run in the background from access. I can start cognos running but need to know how to do the following. Open the file to run, enter userid and password, save to excel, I know how to do the import part. I have tried to rewrite code in Access but keep blowing up on space. The tables that I read from are 200thousand plus. Below is the code. Any suggestions.
Thanks for your support
GinnyP
select
T1.HANDLING_OFFICE, T2.CUR_ADJUSTOR_NO, T3.ADJUSTOR_NAME, T2.BRANCH_CD,
T2.CASE_NO, T2.SYMBOL, T2.ABSTRACT_DT, T2.CLM_CREATE_DT, T2.CLMNT_NM,
T1.LOSS_DT, T1.LOSS_DESC_1, T1.LOSS_CATAS_NO, T1.CLM_STAT, T2.IND_OS,
T2.IND_PD, T2.LEG_PD, T2.ADJ_PD, (((T2.IND_OS + T2.IND_PD) + (T2.LEG_PD + T2.ADJ_PD))
+ (T2.RECOVERY_REC + T2.SALVAGE_REC)) + T2.SUBRO_REC as INCURRED, T2.RECOVERY_REC,
T2.SALVAGE_REC, T2.SUBRO_REC, T4.POL_NO, T4.INSD_NM, T4.EFF_DT, T2.MAJOR_CLASS_CD,
T2.MAJOR_CLASS_TYPE, T5.MAJOR_CLASS_DESC, T6.DIVISION_CD, T6.DEPARTMENT,
T6.SECTION_CD, T6.PROFIT_UNIT, T7.CLAIM_ST_ALPHA, T2.INJURY_DAMAGE_1,
T1.LOSS_LOCATION, T2.CLM_CLOSE_DT, T2.LOSS_DT_REPORTED, T2.IND_REOPEN_DT, T8.ZIP_1_TO_5, t8.addr_ln_1, t8.addr_ln_2, addr_ln_3,
min(t9.fin_process_dt) as Date_First_Ind_Pmt, min(T10.ASSIGNMENT_OPEN_DT) as Vars_Assign_Date
from
{oj CQLYLRPP.TCLAIM T2 LEFT OUTER JOIN CQLYLRPP.TFINANCIALS T9
on T2.BRANCH_CD = T9.BRANCH_CD and T2.CASE_NO = T9.CASE_NO and T2.SYMBOL = T9.SYMBOL
and t9.resv_typ = 'I' and t9.paid_or_resv_am <> 0},
CQLYLRPP.TREF_ADJUSTOR T3, CQLYLRPP.TPOL T4,
CQLYLRPP.TREF_MAJOR_CLASS T5, CQLYLRPP.TCOVG_KEY_EXTRACT T6,
CQLYLRPP.TREF_STATECD T7, {oj CQLYLRPP.TCASE T1 LEFT OUTER JOIN ANDDLMSP.CLM_NMS T8
on T1.BRANCH_CD = T8.BRANCH_CD and T1.CASE_NO = T8.CASE_NO and t8.use_cd = 'LLA'},
{oj CQLYLRPP.TCLAIM T11 left outer join CQLYLRPP.TASSIGNMENT_REPORT T10 on
T11.BRANCH_CD = T10.BRANCH_CD AND
T11.CASE_NO = T10.CASE_NO AND T11.SYMBOL = T10.SYMBOL_no}
where T2.BRANCH_CD NOT in (676, 677, 678) and
T4.POL_NO <> 9999999 and T4.POL_NO <> 999999999 and T1.CLM_STAT <> 'M' and
T6.DIVISION_CD in (66, 74, 93, 28, 92, 97) and
T1.LOSS_CATAS_NO in ('04605', '04705', '04905', '05005', '05105', '05405') and
T7.CLAIM_ST = T1.LOSS_ACC_ST_PROV and
T2.SYMBOL = T6.SYMBOL and T2.CASE_NO = T6.CASE_NO and
T2.BRANCH_CD = T6.BRANCH_CD and T2.MAJOR_CLASS_CD = T5.MAJOR_CLASS_CD and
T1.POL_NO = T4.POL_NO and T1.MODULE = T4.MODULE and
T1.ANN_STMT_CO = T4.ANN_STMT_CO and
T3.ADJUSTOR_NO = T2.CUR_ADJUSTOR_NO and T1.CASE_NO = T2.CASE_NO and
T1.BRANCH_CD = T2.BRANCH_CD and
T11.branch_cd = T2.branch_cd and T11.CASE_NO = T2.CASE_NO and t11.symbol=t2.symbol and
case when t1.handling_office = 869 and (t2.symbol = 2 or t2.symbol = 3 or t2.symbol = 4 or t2.symbol = 5 or t2.symbol = 6 or t2.symbol = 7 or t2.symbol = 8 or t2.symbol = 9) then 'flag' end is null
group by
T1.HANDLING_OFFICE, T2.CUR_ADJUSTOR_NO, T3.ADJUSTOR_NAME, T2.BRANCH_CD,
T2.CASE_NO, T2.SYMBOL, T2.ABSTRACT_DT, T2.CLM_CREATE_DT, T2.CLMNT_NM,
T1.LOSS_DT, T1.LOSS_DESC_1, T1.LOSS_CATAS_NO, T1.CLM_STAT, T2.IND_OS,
T2.IND_PD, T2.LEG_PD, T2.ADJ_PD, T2.RECOVERY_REC,
T2.SALVAGE_REC, T2.SUBRO_REC, T4.POL_NO, T4.INSD_NM, T4.EFF_DT, T2.MAJOR_CLASS_CD,
T2.MAJOR_CLASS_TYPE, T5.MAJOR_CLASS_DESC, T6.DIVISION_CD, T6.DEPARTMENT,
T6.SECTION_CD, T6.PROFIT_UNIT, T7.CLAIM_ST_ALPHA, T2.INJURY_DAMAGE_1,
T1.LOSS_LOCATION, T2.CLM_CLOSE_DT, T2.LOSS_DT_REPORTED, T2.IND_REOPEN_DT, T8.ZIP_1_TO_5, t8.addr_ln_1, t8.addr_ln_2, addr_ln_3
17
