oracle第五周周报

最新推荐文章于 2022-06-14 11:06:26 发布

CCB_er

最新推荐文章于 2022-06-14 11:06:26 发布

阅读量982

点赞数

分类专栏： oracle学习文章标签： oracle merge date access crm 任务

本文链接：https://blog.csdn.net/swufer/article/details/8001888

版权

oracle学习专栏收录该内容

8 篇文章 0 订阅

订阅专栏

星期一

横表变成纵表

Source表需要再处理一次变成系统需要的数据

为什么要清算为什么要有结算清单

select *from md_process_control t

where t.start_time>to_date('20120801','yyyymmdd');

CRM抽数过程

Impl

本地数据处理过程

Info

数据准备过程

SP_SETT_SRC

结算任务

select t.*, t.rowidfrom agent_data.settle_task_result t

跑清单

SP_SETT_LIST

select * from md_process_control t

where t.start_time>to_date('20120801','yyyymmdd')

AND t.process_name='SP_SETT_LIST'

如果出现清单任务报错的话

Stat_date 就是任务ID 可以用一下语句查询出来哪个任务的出错了

select t.*, t.rowid from agent_data.settle_task_result t

WHERE t.execute_time>to_date('20120801','yyyymmdd')

Stat_date对应的是task_result_id

select t.*, t.rowidfrom meso_sett_src_prod t传说中很重要的中间表

SELECT *FROM crm2_sett_result_list_prod传说是清单

select t.*, t.rowid from agent_data.settle_task_result t

WHERE t.execute_time>to_date('20120801','yyyymmdd')

SELECT *FROM inst_offer_prod tWHERE t.redu_access_number='14814XXXX'

14814XXXX是电话号码可以换成1898084XXXX

SELECT *
  FROM INST.OFFER_PRODXXXX A
WHERE A.REDU_ACCESS_NUMBER = '14814XXXX';

SELECT C.COMP_OFFER, B.NAME, A.*
  FROM INST_OFFER_MEMBER A, SPEC_OFFER_SPEC B, SPEC_OFFER_SPEC_INFO C
WHERE A.LATN_ID = B.LATN_ID
   AND A.OFFER_SPEC_ID = B.OFFER_SPEC_ID
   AND A.OFFER_SPEC_ID = C.OFFER_SPEC_ID
   AND A.LATN_ID = 11XXXX
   AND A.MEMBER_ID = 35302236XXXX;


SELECT A.ACTIVITY_PRICE_PLAN,
       A.PROD_ID,
       A.OFFER_ID,
       A.PRE_AP_CHARGE,
       A.TYPE_PRICE_PLAN,
       A.TYPE_COMP_PRICE_PLAN,
       A.TYPE_COMP_PAY_PRICE_PLAN,
       A.*
  FROM MESO_SETT_SRC_PROD A
WHERE A.TASK_RESULT_ID = 4XXXX80
   AND A.ACTIONTYPE = 30001
   AND A.PROD_ID = 353XXXX2360466;



SELECT * FROM Settle_Index_Value_Merge
a WHERE a.latn_id=11500
AND a.src_id='10900005XXXX';

SELECT * FROM agent_data.Settle_Index_Value_Merge_his
a WHERE a.latn_id=11500
AND a.src_id='10900005XXXX4';

SELECT * FROM settle_rule a
WHERE a.latn_id=XXXXAND a.expression LIKE '%9XXXX174%'
AND a.expression LIKE '%30001%';

SELECT *
  FROM AGENT_DATA.CONTRACT_RULE_RELATION A,
       AGENT_DATA.RELT_CONTRACT_AGENT    B
WHERE A.CONTRACT_ID = B.CONTRACTID
    AND B.REL_OBJECT_ID = 11XXXX
   AND A.RULE_ID IN (342011,342XXXX);

查询过程对应的session 跑到哪一步了

SELECT DISTINCT A.USERNAME,
                A.SID,
                SERIAL#,
                A.MACHINE,
                A.LOGON_TIME,
                A.MACHINE,a.EVENT
  FROM V$SESSION A, V$LOCKED_OBJECT B
WHERE A.SID = B.SESSION_ID
ORDER BY A.LOGON_TIME;

SELECT C.SQL_TEXT
  FROM V$SESSION A, V$SQLTEXT C
WHERE A.SID = 144
   AND C.HASH_VALUE = A.SQL_HASH_VALUE
   AND C.ADDRESS = A.SQL_ADDRESS
ORDER BY C.PIECE;