oracle group表达式,不是group by 表达式,该怎么处理

当前位置:我的异常网» Oracle开发 » 不是group by 表达式,该怎么处理

不是group by 表达式,该怎么处理

www.myexceptions.net  网友分享于：2013-03-09  浏览：118次

不是group by 表达式

CREATE OR REPLACE VIEW V_DATMT_NONGIS_ONU_PORT_1 AS

SELECT Distinct

R.Id REGIONID,

R.NAMECN REGIONNAME,

F.Name SITENAME,--站点

D.Name ROOMNAME,--机房

DNDEVICE.SHORTNAME DNDEVICENAME,

DNDEVICE.SHORTCODE DNDEVICECODE,

DNSUBNE.NO DNSUBNENO,

DNPACKAGE.SHORTCODE DNPACKAGESHORTCODE,

DNPORT.SHORTCODE DNPORTNO,

DNDEVICE.ID DNDEVICEID,

DNPORT.ID DNPORTID,

DECODE(DNPORT.SERVICESTATUS,0,'空闲', 1, '预占用', 2, '保留', 3, '封锁', 4, '实占用',5, '调度中', 6,'时隙划分','未知') SERVICESTATUS,

DECODE(DNPORT.VOICESERVICESTATUS,0,'空闲',1,'预占用',2,'保留',3,'封锁',4,'实占用',5,'调度中',6,'时隙划分','未知') VOICESERVICESTATUS,

DNPORT.TELNUM TELNUM,

DNPORT.ADSLACCESSNO,

DNPORT.PHYSTATUS PHYSTATUS,

DECODE(DNPORT.USABILITY,1,'可用',0,'不可用','未知') USABILITY ,--端子可用性

DNPORT.COMMENTS,

DNPORT.IDINEMS IDINEMS, --网管标识

--add by yangzhao

MIN(DNVLAN.VLANID) VLANID,

DNPORT_DNVLAN.Dnportid dnportvlan,

V_DATMT_DNPORT_DNPORTCONNECTOR.DNPORTID VDNPORTID,

CONNECTMODULE.NAME MDFNAME,--MDF横列名称

CONNECTMODULE.NO MDFNO, --MDF横列编码

CONNECTPANEL.NO PANELNO,--MDF面板编号

CONNECTOR.NAME CONNECTORNO,--MDF横列端子编号

DNMDFMODULE.SHORTCODE MDFLCODE, --MDF列编号

DNDEVICETYPE.ID DNDEVICETYPE_ID,

-- V_DATMT_DNPORT_QE_VLANID.VLANID VLANID, --VLANID

-- V_DATMT_DNPORT_CONNECTOR.MDFNAME MDFNAME, -- MDF横列名称

-- V_DATMT_DNPORT_CONNECTOR.MDFNO MDFNO, --MDF横列编码

-- V_DATMT_DNPORT_CONNECTOR.PANELNO PANELNO, --MDF面板编码

--V_DATMT_DNPORT_CONNECTOR.CONNECTORNO CONNECTORNO,--MDF横列端子编号

-- V_DATMT_DNPORT_CONNECTOR.MDFLCODE MDFLCODE, --MDF列编号

'' IMPORTFLAG --是否更新导入

FROM DNPORT, DNPACKAGE, DNSUBNE, DNDEVICE, HOST D,HOST F, REGION R,

--add by yangzhao

DNPORT_DNVLAN, DNVLAN, DNDEVICETYPE,

CONNECTOR,

CONNECTPANEL,

CONNECTMODULE,

DNMDFMODULE,

V_DATMT_DNPORT_DNPORTCONNECTOR

-- V_DATMT_DNPORT_QE_VLANID,V_DATMT_DNPORT_CONNECTOR

WHERE DNPORT.DNPACKAGEID = DNPACKAGE.ID

AND DNPACKAGE.DNSUBNEID = DNSUBNE.ID

AND DNSUBNE.DNDEVICEID = DNDEVICE.ID

AND DNDEVICE.HOSTID = D.ID

AND F.REGIONID = R.ID

AND D.PARENTHOSTID=F.ID

And dndevice.dndevicetypeid=dndevicetype.id

And dndevicetype.category=21

AND DNPORT.ID =DNPORT_DNVLAN.Dnportid(+)

AND DNPORT.ID = DNVLAN.VLANID(+)

--add V_DATMT_DNPORT_CONNECTOR querycondition

And (CONNECTMODULE.ID = CONNECTPANEL.CONNECTMODULEID)

And (CONNECTPANEL.ID = CONNECTOR.CONNECTPANELID)

And (CONNECTOR.ID = V_DATMT_DNPORT_DNPORTCONNECTOR.CONNECTORID)

And(CONNECTMODULE.ISTEMPLATE = 0)

And (CONNECTPANEL.ISTEMPLATE = 0)

And (CONNECTOR.ISTEMPLATE = 0)

And (DNMDFMODULE.ID(+) = CONNECTPANEL.DNMDFMODULEID)

--add V_DATMT_DNPORT_QE_VLANID querycondition

And(DNPORT.ID = DNPORT_DNVLAN.DNPORTID)

文章评论