C#,ASP。NET里面不能执行,如下SQL,奇怪。
select METERID,DEVICETYPE,max(case when TO_CHAR(DDATE,'yyyymmdd') =TO_CHAR(TO_DATE('2010-4-17','yyyy-mm-dd'),'yyyymmdd') then nvl(METERNLLJ,0) else 0 end) as data1,max(case when TO_CHAR(DDATE,'yyyymmdd') = TO_CHAR(TO_DATE('2010-4-18','yyyy-mm-dd'),'yyyymmdd') then nvl(METERNLLJ,0) else 0 end) as data2,(max(case when TO_CHAR(DDATE,'yyyymmdd') = TO_CHAR(TO_DATE('2010-4-18','yyyy-mm-dd'),'yyyymmdd') then nvl(METERNLLJ,0) else 0 end)-max(case when TO_CHAR(DDATE,'yyyymmdd') = TO_CHAR(TO_DATE('2010-4-17','yyyy-mm-dd'),'yyyymmdd') then nvl(METERNLLJ,0) else 0 end)) as METERNLLJ,max(case when TO_CHAR(DDATE,'yyyymmdd') = TO_CHAR(TO_DATE('2010-4-17','yyyy-mm-dd'),'yyyymmdd') then DEVICESTATUS else 0 end) as DEVICESTATUS1,max(case when TO_CHAR(DDATE,'yyyymmdd') = TO_CHAR(TO_DATE('2010-4-18','yyyy-mm-dd'),'yyyymmdd') then DEVICESTATUS else 0 end) as DEVICESTATUS2 from TMPTODAY WHERE DEVICETYPE=16 AND METERID IN (SELECT METERNO FROM TDOOR_METER WHERE METERTYPE=16 ) group by METERID, DEVICETYPE
select METERID,DEVICETYPE,max(case when TO_CHAR(DDATE,'yyyymmdd') =TO_CHAR(TO_DATE('2010-4-17','yyyy-mm-dd'),'yyyymmdd') then nvl(METERNLLJ,0) else 0 end) as data1,max(case when TO_CHAR(DDATE,'yyyymmdd') = TO_CHAR(TO_DATE('2010-4-18','yyyy-mm-dd'),'yyyymmdd') then nvl(METERNLLJ,0) else 0 end) as data2,(max(case when TO_CHAR(DDATE,'yyyymmdd') = TO_CHAR(TO_DATE('2010-4-18','yyyy-mm-dd'),'yyyymmdd') then nvl(METERNLLJ,0) else 0 end)-max(case when TO_CHAR(DDATE,'yyyymmdd') = TO_CHAR(TO_DATE('2010-4-17','yyyy-mm-dd'),'yyyymmdd') then nvl(METERNLLJ,0) else 0 end)) as METERNLLJ,max(case when TO_CHAR(DDATE,'yyyymmdd') = TO_CHAR(TO_DATE('2010-4-17','yyyy-mm-dd'),'yyyymmdd') then DEVICESTATUS else 0 end) as DEVICESTATUS1,max(case when TO_CHAR(DDATE,'yyyymmdd') = TO_CHAR(TO_DATE('2010-4-18','yyyy-mm-dd'),'yyyymmdd') then DEVICESTATUS else 0 end) as DEVICESTATUS2 from TMPTODAY WHERE DEVICETYPE=16 AND METERID IN (SELECT METERNO FROM TDOOR_METER WHERE METERTYPE=16 ) group by METERID, DEVICETYPE
select METERID,DEVICETYPE,
max(case when TO_CHAR(DDATE,'yyyymmdd') =TO_CHAR(TO_DATE('2010-4-17','yyyy-mm-dd'),'yyyymmdd') then nvl(METERNLLJ,0) else 0 end) as data1,
max(case when TO_CHAR(DDATE,'yyyymmdd') = TO_CHAR(TO_DATE('2010-4-18','yyyy-mm-dd'),'yyyymmdd') then nvl(METERNLLJ,0) else 0 end) as data2,
(max(case when TO_CHAR(DDATE,'yyyymmdd') = TO_CHAR(TO_DATE('2010-4-18','yyyy-mm-dd'),'yyyymmdd') then nvl(METERNLLJ,0) else 0 end)-
max(case when TO_CHAR(DDATE,'yyyymmdd') = TO_CHAR(TO_DATE('2010-4-17','yyyy-mm-dd'),'yyyymmdd') then nvl(METERNLLJ,0) else 0 end)) as METERNLLJ,
max(case when TO_CHAR(DDATE,'yyyymmdd') = TO_CHAR(TO_DATE('2010-4-17','yyyy-mm-dd'),'yyyymmdd') then DEVICESTATUS else 0 end) as DEVICESTATUS1,
max(case when TO_CHAR(DDATE,'yyyymmdd') = TO_CHAR(TO_DATE('2010-4-18','yyyy-mm-dd'),'yyyymmdd') then DEVICESTATUS else 0 end) as DEVICESTATUS2
from TMPTODAY WHERE DEVICETYPE=16 AND METERID IN (SELECT METERNO FROM TDOOR_METER WHERE METERTYPE=16 )
group by METERID, DEVICETYPE
public System.Data.DataTable GetTable(string sTableName)
{
OleDbConnection oleConn = new OleDbConnection(connStr);
OleDbCommand oleCommand = new OleDbCommand(sqlStr, oleConn);
OleDbDataAdapter oleDba = new OleDbDataAdapter(oleCommand);
DataSet dsTable = new DataSet();
oleDba.Fill(dsTable, sTableName);
oleConn.Close();
return dsTable.Tables[sTableName];
}
很是奇怪為什么脫庫子放屁
直接寫成'20100418'不就好了
max(case when TO_CHAR(DDATE,'yyyymmdd') = '20100417' then nvl(METERNLLJ,0) else 0 end) as data1,
max(case when TO_CHAR(DDATE,'yyyymmdd') = '20100418' then nvl(METERNLLJ,0) else 0 end) as data2,
(max(case when TO_CHAR(DDATE,'yyyymmdd') = '20100418' then nvl(METERNLLJ,0) else 0 end)-
max(case when TO_CHAR(DDATE,'yyyymmdd') = '20100417' then nvl(METERNLLJ,0) else 0 end)) as METERNLLJ,
max(case when TO_CHAR(DDATE,'yyyymmdd') = '20100417' then DEVICESTATUS else 0 end) as DEVICESTATUS1,
max(case when TO_CHAR(DDATE,'yyyymmdd') = '20100418' then DEVICESTATUS else 0 end) as DEVICESTATUS2
from TMPTODAY
WHERE DEVICETYPE=16
AND METERID IN (SELECT METERNO FROM TDOOR_METER WHERE METERTYPE=16 )
group by METERID, DEVICETYPE