Hello all,
Below is the code that I use to remove the old plans from showing workers' portfolios. Remember to use the TEST system!
Use impresario select * from T_LIST_CONTENTS a left outer join T_PLAN b on a.customer_no=b.customer_no left outer join TX_CUST_PLAN c on b.plan_no=c.plan_no join T_CUSTOMER d on c.customer_no = d.customer_no where a.list_no='39451' -- list made for purpose of executing this query, contains all constituents with plans from last season and b.campaign_no IN (436, 441, 332, 390, 413)-- plan campaign IDs to update and c.show_in_portfolio = 'y' --show in portfolio check box is checked --below is a separate part of the code to run. THIS IS NOT FILTERED, as it is used to uncheck the Show in Portfolio box for all of last season's plans! begin transaction update TX_CUST_PLAN set show_in_portfolio = 'n' where plan_no in (select b.plan_no from T_LIST_CONTENTS a left outer join T_PLAN b on a.customer_no=b.customer_no left outer join TX_CUST_PLAN c on b.plan_no=c.plan_no where a.list_no='39451'and b.campaign_no IN (436, 441, 332, 390, 413)) --same list and capaign IDs in the select statement you did above commit --rollback
Thanks,
Jack Ridenour-Starnes