create or replace view application_basic as
select col1,col2,col3,col4,col5,col6,col7,col8,col9,num as col10,trunc((num1+num2+num3),2) as col11,trunc(((num1+num2+num3)/num),2) as col12,col13,col14,col15,col16,col17,col18,col19 from
(select a.apply_no as col1,f.id as col2,f.name as col3,f.relation_to_apply_man as col4,f.iden_id as col5,f.health as col6,f.accountaddr as col7,f.nowaddr as col8,f.unit_title as col9
,(select count(apply_no) from apply_family_member s where s.apply_no=f.apply_no group by s.apply_no ) as num
,(select decode(avg(s1.actual_income),'',0,avg(s1.actual_income)) from apply_family_member u ,income_city_salary s1
where u.id=s1.family_members_id(+) and u.id=f.id group by u.id) as num1,
(select decode(avg(s2.pensions),'',0,avg(s2.pensions)) from apply_family_member u ,income_social_insurance s2
where u.id=s2.family_members_id(+) and u.id=f.id group by u.id) as num2,
(select decode(avg(s3.other_income),'',0,avg(s3.other_income)) from apply_family_member u ,income_other s3
where u.id=s3.family_members_id(+) and u.id=f.id group by u.id) as num3,
c.comm_name as col13,
c.comm_no as col14,
c.area_no as col15,
c.city_no as col16,
c.street_no as col17,
c.sheng_no as col19,
to_char(a.apply_date,'yyyy') as col18
from application a,apply_family_member f,Community_Inf c
where a.apply_no=f.apply_no and c.comm_no=a.comm_no order by a.comm_no asc
)
create or replace view application_detail as
select col1,col2,col3,col4,col5,col6,col7,col8,col9,num as col10,trunc((num1+num2+num3),2) as col11,trunc(((num1+num2+num3)/num),2) as col12,col13,col14,col15,col16,col17,col18,col19,col20,col21,col22,col23,col24,col25,col26,col27,col28,col29
,col30,col31,col32,col33,col34,col35,col36,col37,col38,col39,col40,col41,col42,col43,col44,col45,col46,col47,col48,col49,col50
,col51,col52,col53,col54,col55,col56,col57,col58,col59,col60,col61,col62,col63,col64,col65 from
(
select a.apply_no as col1,f.id as col2,f.name as col3,f.relation_to_apply_man as col4,f.iden_id as col5,f.health as col6,f.accountaddr as col7,f.nowaddr as col8,f.unit_title as col9,
(select count(apply_no) from apply_family_member s where s.apply_no=f.apply_no group by s.apply_no ) as num,
(select decode(avg(s1.actual_income),'',0,avg(s1.actual_income)) from apply_family_member u ,income_city_salary s1 where u.id=s1.family_members_id(+) and u.id=f.id group by u.id) as num1,
(select decode(avg(s2.pensions),'',0,avg(s2.pensions)) from apply_family_member u ,income_social_insurance s2 where u.id=s2.family_members_id(+) and u.id=f.id group by u.id) as num2,
(select decode(avg(s3.other_income),'',0,avg(s3.other_income)) from apply_family_member u ,income_other s3 where u.id=s3.family_members_id(+) and u.id=f.id group by u.id) as num3,
decode(f.identity,'下岗','1','') as col13,
decode(f.identity,'无业','1','') as col14,
decode(f.identity,'打零工','1','') as col15,
decode(h.equity_Type,'私有','1','') as col16,
decode(h.equity_Type,'租住','1','') as col17,
decode(h.equity_Type,'借住','1','') as col18,
h.use_Area as col19,
decode(f.iiiName,'癌症','1','') as col20,
decode(f.iiiName,'尿毒症','1','') as col21,
decode(f.iiiName,'血液透析','1','') as col22,
decode(f.iiiName,'白血病','1','') as col23,
decode(f.iiiName,'慢性病','1','') as col24,
decode(f.iiiName,'其他特殊疾病','1','') as col25,
decode(f.disability,'二级(重度)肢体残疾','1',(decode(f.disability,'一级(重度)肢体残疾','1',''))) as col26,
decode(f.disability,'二级(重度)肢体残疾','1',(decode(f.disability,'一级(重度)肢体残疾','1',''))) as col27,
decode(f.disability,'一级智力残疾','1',(decode(f.disability,'二级智力残疾','1',''))) as col28,
decode(f.disability,'视力残疾一级盲','1',(decode(f.disability,'视力残疾二级盲','1',''))) as col29,
decode(f.disability,'聋哑重残一级','1',(decode(f.disability,'聋哑重残二级','1',''))) as col30,
decode(f.disability,'其它重残(二级以上)','1','') as col31,
decode(f.disability,'三级(重度)肢体残疾','1','') as col32,
decode(f.disability,'三级(重度)精神残疾','1','') as col33,
decode(f.disability,'三级智力残疾','1',(decode(f.disability,'四级智力残疾','1',''))) as col34,
decode(f.disability,'视力残疾三级盲','1',(decode(f.disability,'视力残疾四级盲','1',''))) as col35,
decode(f.disability,'聋哑重残三级','1',(decode(f.disability,'聋哑重残四级','1',''))) as col36,
decode(f.disability,'其他重残(三级以下)','1','') as col37,
--学历
decode(f.education,'小学','1','') as col38,
decode(f.education,'中学','1','') as col39,
decode(f.education,'高中','1','') as col40,
decode(f.education,'本科','1','') as col41,
(select (case when (trunc(months_between(sysdate,g.born_date)/12)+1) between 60 and 69 then '1' end) AS AGE from apply_family_member g where f.id=g.id ) as col42,
(select (case when (trunc(months_between(sysdate,g.born_date)/12)+1) between 70 and 79 then '1' end) AS AGE from apply_family_member g where f.id=g.id ) as col43,
(select (case when (trunc(months_between(sysdate,g.born_date)/12)+1) between 80 and 89 then '1' end) AS AGE from apply_family_member g where f.id=g.id ) as col44,
(select (case when (trunc(months_between(sysdate,g.born_date)/12)+1)>=90 then '1' end) AS AGE from apply_family_member g where f.id=g.id ) as col45,
decode(f.FAMILY_TYPE,'伤残军人','1','') as col46,
decode(f.FAMILY_TYPE,'在乡军人','1','') as col47,
decode(f.FAMILY_TYPE,'复员军人','1','') as col48,
decode(f.FAMILY_TYPE,'烈属','1','') as col49,
decode(f.FAMILY_TYPE,'华侨','1','') as col50,
decode(f.FAMILY_TYPE,'朝侨','1','') as col51,
decode(f.FAMILY_TYPE,'三无对象','1','') as col52,
decode(f.INSURANCE,'医疗保险','1','') as col53,
decode(f.INSURANCE,'养老保险','1','') as col54,
a.phone as col55,
c.comm_name as col56,
c.comm_no as col57,
c.street_no as col58,
c.city_no as col59,
c.area_no as col60,
decode(a.gr,'集中供热','1','') as col61,
decode(a.gr,'小区供热','1','') as col62,
decode(a.gr,'其它','1','') as col63,
to_char(a.apply_date,'yyyy') as col64,
c.sheng_no as col65
from application a,apply_family_member f,income_house h,Community_Inf c
where a.apply_no=f.apply_no and c.comm_no=a.comm_no and f.id=h.family_members_id(+) order by a.apply_no asc
) apply
分享到:
相关推荐
##通过sqlcmd执行sql文件 由于sql文件过大,超过了100M,再数据库的窗口执行,结果超出内存了,对于特别大的sql文件可以使用sqlcmd进行执行 ###1.打开cmd窗口 运行–cmd–进入到sql文件所在的文件夹。 如果是win7可...
在我个人编写SQL脚本时,至少会把SQL的格式排列成易于阅读的,因为其他人会阅读到你的SQL,无论是在程序中或是脚本文件中,良好的排版不仅让人看起来赏心悦目,在和他人之间做交流时也省时省力,不会因为揉成一团的...
SQL优化是数据库管理中的关键环节,它涉及到提升查询性能、减少资源消耗以及改善系统整体效率。SQL优化软件和工具能够帮助数据库管理员(DBA)和开发人员找出性能瓶颈,优化查询逻辑,从而提高数据库系统的响应速度...
在IT行业中,数据库管理系统是核心组成部分,SQL Server和Oracle分别是微软和甲骨文公司推出的两款广泛应用的关系型数据库系统。在企业级应用中,有时需要在不同的数据库系统间进行数据迁移或兼容性处理,这就涉及到...
本人在Windows7 64位+SQL Server 2012环境下测试通过(系统是全新安装) 使用方法: 1,安装SQLPrompt v5.3,这个不多说。 2,安装完毕后,断开网络连接。 3,打开Visual Studio或者SQL Server Management Studio(版本...
SQLPrompt for SQLServer2016 智能提示插件 SQL2016 提示 SQLPrompt最新版本 绿色版 SQL Prompt 是一款拥有SQL智能提示功能的SQL Server和VS插件。SQL Prompt能根据数据库的对象名称,语法和用户编写的代码片段自动...
《SQL Server与Sql Prompt:提升数据库管理与脚本编写的高效工具》 SQL Server作为一款广泛应用的关系型数据库管理系统,以其强大的功能和稳定性深受广大IT从业者的喜爱。在SQL Server的日常管理和开发过程中,高效...
SQL Server 导入超大 SQL 脚本文件 SQL Server 是一种关系型数据库管理系统,广泛应用于各种行业。然而,在实际应用中,我们经常会遇到导入超大 SQL 脚本文件的问题。本文将介绍如何使用 osql 工具来导入超大 SQL ...
在Android开发中,有时我们需要与远程数据库进行交互,例如SQLServer。这个场景通常是通过Web服务,如WebService来实现。本文将详细介绍如何在Android应用中利用WebService接口连接到SQLServer数据库,实现数据的增...
SQL Prompt是Redgate Software开发的一款高效SQL代码编辑工具,它为SQL Server的开发人员提供了智能提示、格式化、重构和代码分析等功能,极大地提升了编写和维护SQL代码的效率。SQL Prompt 9是该系列的最新版本,...
SQL 基础 SQL 首页 SQL 简介 SQL 语法 SQL select SQL distinct SQL where SQL AND & OR SQL Order By SQL insert SQL update SQL delete SQL 高级 SQL Top SQL Like SQL 通配符 SQL In SQL Between ...
《SQLMonitor:Oracle数据库SQL跟踪与分析利器》 在IT行业中,数据库的高效管理与优化是至关重要的。针对Oracle数据库,有一款名为SQLMonitor的工具,它专为跟踪和监控SQL语句而设计,帮助开发者和DBA们找出程序...
开源的AI自动生成SQL语句源代码,这款SQLCoder-70B-Alpha在文本到SQL的转换能力上超越了包括GPT-4在内的所有通用模型,它能更准确地理解你的需求,并生成相应的SQL查询。SQLCoder2和SQLCoder-7B模型已经向公众开放,...
SQL Server驱动包是用于Java应用程序通过JDBC(Java Database Connectivity)接口与Microsoft SQL Server数据库进行交互的必备组件。本文将详细介绍这两个重要的驱动文件——sqljdbc.jar和sqljdbc4.jar,以及如何...
标题中的"sqlservr32和sqlservr64.zip"指的是SQL Server 2005服务中的两个关键组件,`sqlservr32.exe`和`sqlservr64.exe`。这两个文件是SQL Server服务的核心执行文件,分别对应于32位和64位操作系统。在Windows 8和...
SQL Server驱动包`sqljdbc4.jar`是微软官方提供的Java数据库连接器(JDBC),用于在Java应用程序中与Microsoft SQL Server进行通信。JDBC是Java编程语言中的一个标准API,它使得开发人员能够以标准化的方式访问各种...
SQLTracker是一款专为数据库操作监控设计的工具,它在IT领域中主要用于跟踪和记录SQL语句的执行情况。SQL(Structured Query Language)是用于管理关系数据库的编程语言,包括查询、更新、插入和删除数据等操作。SQL...
解决oracle sqldeveloper无法连接mysql、SQLServer问题,sqlDeveloper是ORACLE数据库开发工具,自带的是无法连接MS SQL Server以及mysql的,想连接的话需要第三方工具。 使用方法: 解压出来后将2个jar放入jlib...
在数据库管理领域,Oracle SQL和MySQL SQL是两种广泛使用的SQL方言,它们在语法和功能上存在一定的差异。当需要将一个基于Oracle SQL的应用程序迁移到MySQL环境时,就需要进行SQL语句的转换工作。本项目提供了一个...
在Kettle中配置SQL Server数据库连接时,我们需要依赖特定的数据库驱动,这就是SQL JDBC驱动。 SQL JDBC驱动是微软提供的Java Database Connectivity (JDBC) 驱动,使得Java应用程序能够与SQL Server进行交互。有两...