angela_g1

Hi I am trying to Select * where table1.member_no=table2.member_no and status is not 'O','H','R' there could be multiples of the same member_no and the other status is 'C' i do not want to insert and members where all status's are 'c' Hope this makes sense! How do i do this?

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!!

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!!