视图:
create or replace view etl_opr_view
(oprtype,oprtypename ,oprid,code,name,classified,status,projectid
,projectname,principalid ,principal ,description ,remark,crtuserid ,crtuser
,crtdate ,modifypersonid,modifyperson,modifydate
,reqstartdate,reqenddate,planstartdate,planenddate,actualstartdate
,actualenddate,planwork,actualwork,actualcost,severity,priority,completepercent
,filterstartdate, filterenddate
)
as
select 21, '任务', taskid, wbscode, taskname,
(case when metatype='0' then '任务' when metatype='1' then '里程碑'
when metatype='2' then '阶段' when metatype='3' then '交付物' when
metatype='4' then '摘要任务'
end) ,
(case when actualstartdate='' or actualstartdate is null then '未开始' when actualenddate='' or actualenddate is null then '
开始' else '完成'
end) STATUS,
projectid, (select projectname from pj_baseinfo where
projectid=tk_baseinfo.projectid),
inchargepersonid, (select username from org_userinfo where
userid=tk_baseinfo.inchargepersonid),
'', '',
-1,'',null,
modifyperson,(select username from org_userinfo where
userid=tk_baseinfo.modifyperson), modifydate,
null,null,
planstartdate, planenddate,
actualstartdate,actualenddate,
planworkload,actualworkload,0,
'','',completepercent*100
,planstartdate, planenddate
from tk_baseinfo where delflag=0 and completepercent<1
;
创建是成功的,然后导出为.sql文件再导入到另一个库中报错,错误如下:
Query:
/*!50001 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `etl_opr_view` AS select 95 AS `21`,'任务' AS `任务`,`tk_baseinfo`.`taskid` AS `taskid`,`tk_baseinfo`.`wbscode` AS `wbscode`,`tk_baseinfo`.`taskname` AS `taskname`,(case when (`tk_baseinfo`.`metatype` = '0') then '任务' when (`tk_baseinfo`.`metatype` = '1') then '里程碑' when (`tk_baseinfo`.`metatype` = '2') then '阶段' when (`tk_baseinfo`.`metatype` = '3') then '交付物' when (`tk_baseinfo`.`metatype` = '4') then '摘要任务' end) AS `(case when metatype='0' then '任务' when metatype='1' then '里程碑'
when metatype='2' then '阶段' when metatype='3' then '交付物' when
metatype='4' then '摘要任务'
end)`,(case when ((`tk_baseinfo`.`actualstartdate` = '') or isnull(`tk_baseinfo`.`actualstartdate`)) then '未开始' when ((`tk_baseinfo`.`actualenddate` = '') or isnull(`tk_baseinfo`.`actualenddate`)) then '\n开始' else '完成' end) AS `STATUS`,`tk_baseinfo`.`projectid` AS `projectid`,(select `pj_baseinfo`.`projectname` from `pj_baseinfo` where (`pj_baseinfo`.`projectid` = `tk_baseinfo`.`projectid`)) AS `(select projectname from pj_baseinfo where
projectid=tk_baseinfo.projectid)`,`tk_baseinfo`.`inchargepersonid` AS `inchargepersonid`,(select `org_userinfo`.`UserName` from `org_userinfo` where (`org_userinfo`.`userid` = `tk_baseinfo`.`inchargepersonid`)) AS `(select username from org_userinfo where
userid=tk_baseinfo.inchargepersonid)`,'' AS ``,'' AS ``,-(1) AS `-1`,'' AS ``,NULL AS `NULL`,`tk_baseinfo`.`modifyperson` AS `modifyperson`,(select `org_userinfo`.`UserName` from `org_userinfo` where (`org_userinfo`.`userid` = `tk_baseinfo`.`modifyperson`)) AS `(select username from org_userinfo where
userid=tk_baseinfo.modifyperson)`,`tk_baseinfo`.`modifydate` AS `modifydate`,NULL AS `NULL`,NULL AS `NULL`,`tk_baseinfo`.`planstartdate` AS `planstartdate`,`tk_baseinfo`.`planenddate` AS `planenddate`,`tk_baseinfo`.`actualstartdate` AS `actualstartdate`,`tk_baseinfo`.`actualenddate` AS `actualenddate`,`tk_baseinfo`.`planworkload` AS `planworkload`,`tk_baseinfo`.`actualworkload` AS `actualworkload`,0 AS `0`,'' AS ``,'' AS ``,(`tk_baseinfo`.`completepercent` * 100) AS `completepercent*100`,`tk_baseinfo`.`planstartdate` AS `planstartdate`,`tk_baseinfo`.`planenddate` AS `planenddate` from `tk_baseinfo` where ((`tk_baseinfo`.`delflag` = 0) and (`tk_baseinfo`.`completepercent` < 1)) */
Error occured at:2014-07-17 10:34:00
Line no.:29840
Error Code: 1166 - Incorrect column name '(case when metatype='0' then '任务' when metatype='1' then '里程碑'
when metatype='2' then ''
2014年7月21日 14:55