angela_g1 Posted March 6, 2014 Share Posted March 6, 2014 Hi I am trying to add to existing SQL for an insert in to an Oracle application Express application. This is the existing codeINSERT INTO RE_EZPAY_AT_AGENCY_DAILY(SNAPSHOT_DATE,MEMBER_NO_WCD,MEMBER_NO,RPT_GROUP,AGG_ORDER_NO,STATUS,DEBT,NUMBER_INSTALMENTS)SELECTVE_EZP_AT_AGENCY_DAILY.SNAPSHOT_DATE,VE_EZP_AT_AGENCY_DAILY.MEMBER_NO_WCD,VE_EZP_AT_AGENCY_DAILY.MEMBER_NO,VE_MEMBER_MERCH_MAX_ORDER_AMT.MERCH_GROUP,VE_EZP_AT_AGENCY_DAILY.AGG_ORDER_NO,DECODE(SIGN(SYSDATE-VE_MEMBER.MEMBER_JOIN_DATE-365),1,'R','O') AS STATUS,(SELECT SUM(VE_EZP_ORDER_TRANS.EZP_INSTALL_TOTAL_AMT)FROM VE_EZP_ORDER_TRANSWHERE VE_EZP_ORDER_TRANS.MEMBER_NO = VE_EZP_AT_AGENCY_DAILY.MEMBER_NOAND ((VE_EZP_ORDER_TRANS.EZP_BILL_STATUS = 'AT')OR (VE_EZP_ORDER_TRANS.EZP_BILL_STATUS = 'WB'))GROUP BY VE_EZP_AT_AGENCY_DAILY.SNAPSHOT_DATE,VE_EZP_AT_AGENCY_DAILY.MEMBER_NO_WCD,VE_EZP_AT_AGENCY_DAILY.MEMBER_NO,VE_EZP_AT_AGENCY_DAILY.AGG_ORDER_NO,DECODE(SIGN(SYSDATE-VE_MEMBER.MEMBER_JOIN_DATE-365),1,'R','O')) AS DEBT,(SELECT COUNT(VE_EZP_ORDER_TRANS.EZP_INSTALL_TOTAL_AMT)FROM VE_EZP_ORDER_TRANSWHERE VE_EZP_ORDER_TRANS.MEMBER_NO=VE_EZP_AT_AGENCY_DAILY.MEMBER_NOAND VE_EZP_ORDER_TRANS.EZP_BILL_STATUS IN ('AT','WB')) AS NUMBER_INSTALMENTSFROM VE_EZP_AT_AGENCY_DAILYJOIN VE_MEMBER ON VE_EZP_AT_AGENCY_DAILY.MEMBER_NO = VE_MEMBER.MEMBER_NOJOIN VE_MEMBER_MERCH_MAX_ORDER_AMT ON (VE_MEMBER_MERCH_MAX_ORDER_AMT.MEMBER_NO = VE_EZP_AT_AGENCY_DAILY.MEMBER_NO)WHERE ((NOT EXISTS (SELECT 1 FROM RE_EZPAY_AT_AGENCY_DAILY WHERE VE_EZP_AT_AGENCY_DAILY.MEMBER_NO=RE_EZPAY_AT_AGENCY_DAILY.MEMBER_NO))AND VE_EZP_AT_AGENCY_DAILY.SNAPSHOT_DATE > (SELECT MAX (SNAPSHOT_DATE) from RE_EZPAY_AT_AGENCY_DAILY))AND VE_MEMBER_MERCH_MAX_ORDER_AMT.RANK_NUM = 1;I want to also insert records where every member has a status of 'c' or status is not = 'H','R', 'O' but already exists. I am only trying to prevent duplicate records being added which havn't yet been closed ('c'). The main problem I am having is how exclude members being inserted who already have an open (h,o,r) status. Thanks!! Link to comment Share on other sites More sharing options...
justsomeguy Posted March 6, 2014 Share Posted March 6, 2014 You can use NOT IN to check for that I would assume.... WHERE status NOT IN ('h', 'r', 'o') ... Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now