0 Replies Latest reply: Sep 12, 2013 5:23 AM by mahmoud.mansy RSS

    ORACLE COMPICATED QUERY FROM 3 TABLES

    mahmoud.mansy

      i need to get a report from our oracle database 10g r2 have these info like this one:

       

       

          member_no, member_name, member_mobile_no, mb_membership_date, number of

          subscriptions for year 2009, total subscriptions months for the year 2009,

          number of subscriptions for year 2010, total subscriptions months for the year

          2010, number of subscriptions for year 2011, total subscriptions months for the

          year 2011, number of subscriptions for year 2012, total subscriptions months

          for the year 2012, number of subscriptions for year 2013, total subscriptions

          months for the year 2013, last subscription end date, last subscription payment

          type ,last subscription branch no

       

       

       

       

      guys i have 3 tables as described below:

       

       

      1-ft_subscriptions as :

       

       

          SU_SUB_NO NUMBER(12,0)

          SU_SUB_DATE DATE

          SU_BRANCH_NO VARCHAR2(10 BYTE)

          SU_MEMBER_NO VARCHAR2(12 BYTE)

          SU_SUB_STATUS CHAR(1 BYTE)

          SU_SUB_TYPE CHAR(1 BYTE)

          SU_SERVICE_NO NUMBER(5,0)

          SU_SERVICE_TYPE VARCHAR2(5 BYTE)

          SU_NO_OF_UNIT NUMBER(5,0)

          SU_START_DATE DATE

          SU_END_DATE DATE

          SU_PRICE_AMT NUMBER(13,2)

          SU_DISC_AMT NUMBER(13,2)

          SU_EXTRA_DISC NUMBER(13,2)

          SU_NET_AMT NUMBER(13,2)

          SU_GROUP_NO NUMBER(12,0)

          SU_PROM_OFFER_NO NUMBER(12,0)

          SU_UPGRADING_SUB_NO NUMBER(12,0)

          SU_SECOND_SUB_NO VARCHAR2(12 BYTE)

          SU_NOTES VARCHAR2(2000 BYTE)

          SU_COMMENTS1 VARCHAR2(2000 BYTE)

          SU_COMMENTS2 VARCHAR2(2000 BYTE)

          SU_CRE_DATE DATE

          SU_CRE_BY VARCHAR2(30 BYTE)

          SU_AVAIL_CPN NUMBER(4,0)

          SU_EXTRA_DISC_PCT NUMBER(6,3)

          SU_CREDIT_AMT NUMBER(13,2)

          SU_CUSTOMER_NO VARCHAR2(10 BYTE)

          SU_AUDITING_BY VARCHAR2(30 BYTE)

          SU_AUDITING_DATE DATE

          SU_AUDITING_NOTES VARCHAR2(2000 BYTE)

          SU_POSTED CHAR(1 BYTE)

          SU_ACTIVIATION_DATE DATE

          SU_NO_OF_MONTHS NUMBER(7,2)

          SU_ENTRYTIME_CODE NUMBER(5,0)

          SU_CATEGORY VARCHAR2(5 BYTE)

          SU_ACT_START_DATE DATE

          SU_ACT_END_DATE DATE

          SU_SECOND_BRANCH_NO VARCHAR2(3 BYTE)

          SU_ADMIN_DISC_EMP VARCHAR2(10 BYTE)

          SU_DISC_TYPE VARCHAR2(1 BYTE)

          SU_BRANCH_PRICE_FLAG VARCHAR2(1 BYTE)

          SU_MISSING_DATA_FLAG VARCHAR2(1 BYTE)

          SU_CREDIT_PCT NUMBER(6,3)

          SU_BONUS_DAYS NUMBER(5,0)

          SU_UNPS_TRANS_BRANCH VARCHAR2(3 BYTE)

          SU_UNPS_TRANS_SERIAL NUMBER(12,0)

          SU_UNPS_TRANS_DATE DATE

          SU_ALLOWED_TRANS VARCHAR2(1 BYTE)

          SU_PREVIOUS_BAL NUMBER(10,0)

          SU_CASHIER_BRANCH VARCHAR2(10 BYTE)

          SU_CASHIER_SERIAL NUMBER(12,0)

          SU_PAY_TYPE CHAR(1 BYTE)

          SU_OFFER_FREE_DAYS NUMBER(4,0)

          SU_DOCNO VARCHAR2(20 BYTE)

          SU_MSPNO VARCHAR2(20 BYTE)

          SU_DOCTYPE VARCHAR2(10 BYTE)

          SU_LOCNO VARCHAR2(10 BYTE)

          SU_DOC_PFX VARCHAR2(5 BYTE)

          SU_DOC_NO NUMBER(10,0)

          SU_MOD_BY VARCHAR2(30 BYTE)

          SU_MOD_DATE DATE

          SU_MEMBER_FRIEND VARCHAR2(12 BYTE)

          SU_OFFER_FREE_SERVICE NUMBER(5,0)

          SU_FRIEND_START_DATE DATE

       

       

      2-ft_members as :

       

       

          MB_BUS_UNIT VARCHAR2(5 BYTE)

          MB_MEMBER_NO VARCHAR2(12 BYTE)

          MB_MEMBER_NAME VARCHAR2(60 BYTE)

          MB_MEMBER_STATUS CHAR(1 BYTE)

          MB_MEMBERSHIP_DATE DATE

          MB_MEMBER_BRANCH VARCHAR2(10 BYTE)

          MB_MEMBER_TYPE CHAR(1 BYTE)

          MB_MEMBER_NAT VARCHAR2(5 BYTE)

          MB_ID_TYPE NUMBER(3,0)

          MB_ID_NO VARCHAR2(15 BYTE)

          MB_ID_DATE DATE

          MB_BIRTH_DATE DATE

          MB_PHONE_NO VARCHAR2(15 BYTE)

          MB_MOBILE_NO VARCHAR2(15 BYTE)

          MB_POBOX NUMBER(10,0)

          MB_POSTAL_CODE VARCHAR2(25 BYTE)

          MB_ADDRESS VARCHAR2(2000 BYTE)

          MB_EMAIL VARCHAR2(100 BYTE)

          MB_CUSTOMER_NO VARCHAR2(10 BYTE)

          MB_EMP_NO VARCHAR2(15 BYTE)

          MB_MARKETTING_WAY CHAR(2 BYTE)

          MB_IMG VARCHAR2(350 BYTE)

          MB_NOTES VARCHAR2(2000 BYTE)

          MB_ACTIVE_NOTE VARCHAR2(2000 BYTE)

          MB_INACTIVE_NOTE VARCHAR2(2000 BYTE)

          MB_CRE_BY VARCHAR2(30 BYTE)

          MB_CRE_DATE DATE

          MB_VIA_MEMBER VARCHAR2(12 BYTE)

          MB_VIA_BRANCH VARCHAR2(10 BYTE)

          MB_VIA_SUB NUMBER(12,0)

          MB_ID_EXPIRY_DATE DATE

          MB_ID_DATE_HJ DATE

          MB_BIRTH_DATE_HJ DATE

          MB_ID_EXPIRY_DATE_HJ DATE

          MB_LANG VARCHAR2(1 BYTE)

          MB_JOB_TYPE NUMBER(10,0)

          MB_JOB_CLASS NUMBER(10,0)

          MB_JOB_PLACE NUMBER(10,0)

          MB_EMP_ID_EXPIRY_DATE DATE

          MB_EMP_ID_EXPIRY_DATE_HJ DATE

          MB_MEDIA_TYPE VARCHAR2(5 BYTE)

          MB_RELATIVE_MEMBER VARCHAR2(12 BYTE)

          MB_RELATIVE_EMP VARCHAR2(10 BYTE)

          MB_ID_COPY NUMBER(2,0)

          MB_ACQUIST_DISC_PCT NUMBER(6,3)

          MB_ACQUIST_DISC_TYPE VARCHAR2(1 BYTE)

          MB_ADDED_BONUS_FLAG VARCHAR2(1 BYTE)

          MB_REVIEW_SUB_NO NUMBER(12,0)

          MB_REVIEW_BRH_NO VARCHAR2(10 BYTE)

          MB_RECEIVED_SMS VARCHAR2(1 BYTE)

          MB_CARD_NO VARCHAR2(8 BYTE)

          MB_MOD_BY VARCHAR2(30 BYTE)

          MB_MOD_DATE DATE

          MB_MEMBER_NO7 VARCHAR2(12 BYTE)

          3-ft_services as :

          SRV_BUS_UNIT VARCHAR2(5 BYTE)

          SRV_SERVICE_NO NUMBER(5,0)

          SRV_SERVICE_DESC1 VARCHAR2(50 BYTE)

          SRV_SERVICE_DESC2  VARCHAR2(50 BYTE)

          SRV_SERVICE_TYPE VARCHAR2(5 BYTE)

          SRV_SERVICE_STATUS CHAR(1 BYTE)

          SRV_SERVICE_PRICE NUMBER(12,5)

          SRV_NO_OF_UNIT NUMBER(5,0)

          SRV_START_TIME NUMBER(7,0)

          SRV_END_TIME NUMBER(7,0)

          SRV_DURATION NUMBER(7,0)

          SRV_POINTS NUMBER(7,0)

          SRV_NOTES VARCHAR2(2000 BYTE)

          SRV_CRE_BY VARCHAR2(30 BYTE)

          SRV_CRE_DATE DATE

          SRV_MIN_PEND_DAYS NUMBER(3,0)

          SRV_MAX_PEND_DAYS NUMBER(3,0)

          SRV_MIN_EXT_DAYS NUMBER(3,0)

          SRV_MAX_EXT_DAYS NUMBER(3,0)

          SRV_BONUS_DAYS NUMBER(3,0)

          SRV_MAX_PEND_COUNT NUMBER(3,0)

          SRV_MAX_EXT_COUNT NUMBER(3,0)

          SRV_CATEGORY VARCHAR2(5 BYTE)

          SRV_EXTEND_DAYS NUMBER(3,0)

          SRV_NO_OF_MONTHS NUMBER(7,2)

          SRV_ENTRYTIME_CODE NUMBER(5,0)

          SRV_ALLOW_PERIOD NUMBER(5,0)

          SRV_RELATIVE_EMP_PCT NUMBER(6,3)

          SRV_RELATIVE_MBR_PCT NUMBER(6,3)

          SRV_DAYS_BEFORE_END NUMBER(3,0)

          SRV_DAYS_AFTER_END NUMBER(3,0)

          SRV_ALLOWED_WAIVE VARCHAR2(1 BYTE)

          SRV_ALLOWED_SUB_TRANS VARCHAR2(1 BYTE)

          SRV_UPGRADE_LIMIT NUMBER(4,0)

          SRV_MIN_AEG_ALLOWED NUMBER(5,2)

          SRV_MAX_AEG_ALLOWED NUMBER(5,2)

          SRV_WAIVE_LIMIT NUMBER(4,0)

          SRV_ALLOW_ACQUIRED_DISC VARCHAR2(1 BYTE)

       

       

       

       

       

       

       

       

      i need to get a report  have these info like this one:

       

       

          member_no, member_name, member_mobile_no, mb_membership_date, number of

          subscriptions for year 2009, total subscriptions months for the year 2009,

          number of subscriptions for year 2010, total subscriptions months for the year

          2010, number of subscriptions for year 2011, total subscriptions months for the

          year 2011, number of subscriptions for year 2012, total subscriptions months

          for the year 2012, number of subscriptions for year 2013, total subscriptions

          months for the year 2013, last subscription end date, last subscription payment

          type ,last subscription branch no

       

       

      -----

      guys this is very urgent for my company needs,thnx in advance