SQL select where

[angela_g1]

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

[justsomeguy]

You can use NOT IN to check for that I would assume.... WHERE status NOT IN ('h', 'r', 'o') ...