Jump to content

SQL select where


angela_g1
 Share

Recommended Posts

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

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 account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...