oracle报表经典语句
oracle报表的典型sql语句
分类:学习天地
Select LEAD(m.app_no,(Select Count(1) From app_mtfeature Where app_no='04')-1,'') over(Order By m.app_no) appno, m.feature_seq||'' feature_seq,m.month||'' month,m.pointfee,m.discount From app_mtfeature m Where m.app_no='04' Union All Select '','合计','',Sum(pointfee),Sum(discount) From app_mtfeature Where app_no='04' Union All Select LEAD(m.app_no,(Select Count(1) From app_mtfeature Where app_no='06')-1,'') over(Order By m.app_no) appno, m.feature_seq||'',m.month||'',m.pointfee,m.discount From app_mtfeature m Where m.app_no='06' Union All Select '','合计','',Sum(pointfee),Sum(discount) From app_mtfeature Where app_no='06' zxbxiaobo@ pwd:xiaobo 看看首项,第二项效果怎么样! /blog/34094 一些开发脚本 (1)./* 根据报表文件名称关键字查找报表的执行文件名称等信息*/ SELECT A.USER_CONCURRENT_PROGRAM_NAME, A.CONCURRENT_PROGRAM_NAME, A.OUTPUT_FILE_TYPE, B.EXECUTION_FILE_NAME, B.EXECUTABLE_NAME, FND_L.MEANING, B.USER_EXECUTABLE_NAME, B.DESCRIPTION FROM FND_CONCURRENT_PROGRAMS_VL A, fnd_executables_vl B, FND_LOOKUPS FND_L WHERE A.APPLICATION_ID=B.APPLICATION_ID AND A.EXECUTABLE_ID=B.EXECUTABLE_ID AND B.EXECUTION_METHOD_CODE= FND_L.LOOKUP_CODE(+) AND FND_L.LOOKUP_TYPE='CP_EXECUTION_METHOD_CODE' AND A.USER_CONCURRENT_PROGRAM_NAME LIKE 'C%杂项出入库报表%' (2)./*查找在标准请求组里提交的报表所在的职责*/ SELECT A.RESPONSIBILITY_NAME,B.USER_CONCURRENT_PROGRAM_NAME,B.CONCURRENT_PROGRAM_NAME FROM FND_RESPONSIBILITY_VL A, FND_CONCURRENT_PROGRAMS_VL B, FND_REQUEST_GROUP_UNITS C WHERE A.APPLICATION_ID=C.APPLICATION_ID AND A.REQUEST_GROUP_ID=C.REQUEST_GROUP_ID AND B.APPLICATION_ID=C.UNIT_APPLICATION_ID AND B.CONCURRENT_PROGRAM_ID=C.REQUEST_UNIT_ID AND B.USER_CONCURRENT_PROGRAM_NAME LIKE '%杂项出入库报表%' (3)./*查找在菜单里提交的报表所在职责*/ SELECT A.RESPONSIBILITY_NAME,B.PROMPT,F.USER_CONCURRENT_PROGRAM_NAME FROM FND_RESPONSIBILITY_VL A, FND_MENU_ENTRIES_VL B, FND_FORM_FUNCTIONS_VL C, FND_REQUEST_GROUPS D, FND_REQUEST_GROUP_UNITS E, FND_CONCURRENT_PROGRAMS_VL F WHERE A.MENU_ID=B.MENU_ID AND B.FUNC