oracle报错30001,oracle第五周周报

星期一

横表变成纵表

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

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

select*frommd_process_control t

wheret.start_time>to_date('20120801','yyyymmdd');

CRM抽数过程

Impl

本地数据处理过程

Info

数据准备过程

SP_SETT_SRC

结算任务

selectt.*, t.rowidfromagent_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

selectt.*, t.rowidfrommeso_sett_src_prod t传说中很重要的中间表

SELECT*FROMcrm2_sett_result_list_prod传说是清单

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

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

SELECT*FROMinst_offer_prod tWHEREt.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 跑到哪一步了

SELECTDISTINCTA.USERNAME,A.SID,SERIAL#,A.MACHINE,A.LOGON_TIME,A.MACHINE,a.EVENTFROMV$SESSIONA,V$LOCKED_OBJECTBWHEREA.SID=B.SESSION_IDORDERBYA.LOGON_TIME;

SELECTC.SQL_TEXTFROMV$SESSIONA,V$SQLTEXTCWHEREA.SID=144ANDC.HASH_VALUE=A.SQL_HASH_VALUEANDC.ADDRESS=A.SQL_ADDRESSORDERBYC.PIECE;

星期二

第一步：给出一个号码 1898084XXXX

Select * from inst_offer_prod  t  where t.redu_access_number =’1898080XXXX’

查询出prod_id

第二步：根据prod id 查询出 offer_spec_id 就是settle_index_value _merge表中的src_id

SELECTC.COMP_OFFER, B.NAME, A.*

FROMINST_OFFER_MEMBER A, SPEC_OFFER_SPEC B, SPEC_OFFER_SPEC_INFO C

WHEREA.LATN_ID = B.LATN_ID

ANDA.OFFER_SPEC_ID = B.OFFER_SPEC_ID

ANDA.OFFER_SPEC_ID = C.OFFER_SPEC_ID

ANDA.LATN_ID =11500

ANDA.MEMBER_ID =353022360466;

最新未结算 脚本如下：

SELECT*FROMAGT_AREA T;

SELECTT.*, T.ROWID

FROMMESO_SETT_SRC_PROD T

WHERET.TASK_RESULT_ID =41280

ANDT.PROD_ID =35302236XXXX;---查出actiontype

SELECT*

FROMINST.OFFER_PROD@DBLINK_CRM2_DB1 A

WHEREA.REDU_ACCESS_NUMBER ='1339837XXXX';--查出prod_id

SELECTC.COMP_OFFER, B.NAME, A.*

FROMINST_OFFER_MEMBER A, SPEC_OFFER_SPEC B, SPEC_OFFER_SPEC_INFO C

WHEREA.LATN_ID = B.LATN_ID

ANDA.OFFER_SPEC_ID = B.OFFER_SPEC_ID

ANDA.OFFER_SPEC_ID = C.OFFER_SPEC_ID

ANDA.LATN_ID =1XXXX

ANDA.MEMBER_ID =16010277XXXX;-------查出的offer_spec_id就是settle_index_value_merge 中的src_id

SELECTA.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.*

FROMMESO_SETT_SRC_PROD A

WHEREA.TASK_RESULT_ID =41XXXX

ANDA.ACTIONTYPE =XXXX

ANDA.PROD_ID =16010277XXXX;

SELECT*

FROMSETTLE_INDEX_VALUE_MERGE A

WHEREA.LATN_ID =10XXXX

ANDA.SRC_ID ='100000000XXXX';

SELECT*

FROMAGENT_DATA.SETTLE_INDEX_VALUE_MERGE_HIS A

WHEREA.LATN_ID =XXXXANDA.SRC_ID ='10900XXXX';----可以查询出规则更改的最近一次时间

SELECT*

FROMSETTLE_RULE A

WHEREA.LATN_ID =10XXXX0

ANDA.EXPRESSIONLIKE'%93XXXX%'

ANDA.EXPRESSIONLIKE'%XXXX%';---可以看到expression表达式！

SELECT*

FROMSETTLE_RULE A

WHEREA.LATN_ID =10XXXX0

ANDA.RULE_ID =34XXXX;

SELECT*

FROMAGENT_DATA.CONTRACT_RULE_RELATION A,

AGENT_DATA.RELT_CONTRACT_AGENT    B

WHEREA.CONTRACT_ID = B.CONTRACTID

ANDB.REL_OBJECT_ID =12XXXX08------119XXXX是co_branch_id

ANDA.RULE_IDIN(353XXXX05,35XXXX25);-----查合同

星期三

商店员表的整理

agt_agent

agt_branch

emp_employee

按照字段提取！

广安重新出账

星期四

Powerdesign中的表的箭头的含义是指向的那张表  向源表提供数据   也就是说指向那张表有外键

熟悉电信渠道系统