- SQL code
select distinct a.measurePointRowId MEASUREPOINTROWID, a.measurePointName MEASUREPOINTNAME, b.linename LINENAME, a.consumerRowId, a.consumerName CONSUMERNAME, a.totalratedpower TOTALRATEDPOWER, b.jltype JLTYPE, c.cnt CNT, isnull(d.overcnt, 0) OVERCNT, cc.fzlfz FZLFZ from (select distinct measurePointRowId, measurePointName, consumerRowId, consumerName, totalratedpower from hz_zdfzl_v200911 where datadate >= '2009-11-01' and datadate < '2009-12-06' union select distinct measurePointRowId, measurePointName, consumerRowId, consumerName, totalratedpower from hz_zdfzl_v200912 where datadate >= '2009-11-01' and datadate < '2009-12-06') a inner join (select distinct dbid, linename, 6003 jltype from oj_group_dkh a where a.gdjid = 5000532 union select distinct dbid, linename, 8003 jltype from oj_group_pw a where a.gdjid = 5000532) b on b.dbid = a.measurePointRowId left join sys_gdzlhreshold cc on cc.oi_idf = b.dbid left join (select measurePointRowId, count(*) cnt from (select * from hz_zdfzl_v200911 where datadate >= '2009-11-01' and datadate < '2009-12-06') a group by a.measurePointRowId union select measurePointRowId, count(*) cnt from (select * from hz_zdfzl_v200912 where datadate >= '2009-11-01' and datadate < '2009-12-06') b group by b.measurePointRowId) c on c.measurePointRowId = b.dbid left join (select measurePointRowId, count(*) overcnt from (select * from hz_zdfzl_v200911 a left join sys_gdzlhreshold b on a.measurePointRowId = b.oi_idf where a.fzl > b.fzlfz and datadate >= '2009-11-01' and datadate < '2009-12-06') a group by a.measurePointRowId union select measurePointRowId, count(*) overcnt from (select * from hz_zdfzl_v200912 a left join sys_gdzlhreshold b on a.measurePointRowId = b.oi_idf where a.fzl > b.fzlfz and datadate >= '2009-11-01' and datadate < '2009-12-06') b group by b.measurePointRowId) d on d.measurePointRowId = b.dbid
hz_zdfzl_v200911和hz_zdfzl_v200912是2个视图
查询的时候如果只查2个视图中的一个，也就是说，不要union的话是正常的，但是只要查询是跨月份的，那么就要进行union，但是union之后他报了一个这样的错误
09:12:38.937 DBMS 192.168.100.110 -- Error: Number (11040) Severity (16) State (1) Server (SYBASE) Adaptive Server cannot perform the requested action because column 'measurePointRowId' is not within the scope of the joined table expression. Check your command for missing or incorrect database objects, variable names, and/or input data.
百思不解，请高手帮忙
------解决方案--------------------------------------------------------
代码太长，建议分步调试，
根据提示measurePointRowId列没有在连接表中，检查一下
------解决方案--------------------------------------------------------