我尝试访问我的视图时收到此错误
我已经建立了我的数据库/视图
CREATE TABLE Boxer(
BoxerId INTEGER PRIMARY KEY AUTOINCREMENT,
Firstname NVARCHAR(50) NOT NULL,
Lastname NVARCHAR(50) NOT NULL
);
CREATE TABLE Match(
MatchId INTEGER PRIMARY KEY AUTOINCREMENT,
BoxerA INTEGER NOT NULL FOREIGN KEY REFERENCES Boxer(BoxerId),
BoxerB INTEGER NOT NULL FOREIGN KEY REFERENCES Boxer(BoxerId),
MatchDate date NOT NULL DEFAULT GETDATE(),
NumberOfRounds INTEGER NOT NULL DEFAULT 12
);
CREATE TABLE Round(
RoundId INTEGER PRIMARY KEY AUTOINCREMENT,
MatchId INTEGER NOT NULL FOREIGN KEY REFERENCES Match(MatchId),
BoxerA INTEGER NOT NULL DEFAULT 0,
BoxerB INTEGER NOT NULL DEFAULT 0,
Position INTEGER NOT NULL
);
/*
Building a view which dislpays matches with boxers names and total scores
*/
CREATE VIEW MatchDetail AS
SELECT Match.MatchId, A.BoxerId AS IdA, B.BoxerId AS IdB, A.Firstname + ' ' + A.Lastname AS NameA, B.Firstname + ' ' + B.Lastname AS NameB,
(SELECT SUM(R.BoxerA) AS Score FROM Round AS R WHERE (R.MatchId = Match.MatchId)) AS ScoreA,
(SELECT SUM(R.BoxerB) AS Score FROM Round AS R WHERE (R.MatchId = Match.MatchId)) AS ScoreB,
Match.MatchDate, Match.NumberOfRounds
FROM Boxer AS A INNER JOIN Match ON A.BoxerId = Match.BoxerA INNER JOIN Boxer AS B ON Match.BoxerB = B.BoxerId
到目前为止,我已经使用记事本示例构建了我的应用程序,因此我调用了DbHelper
Cursor MatchesCursor = mDbHelper.fetchAllMatchDetails();
然后调用查询
public Cursor fetchAllMatchDetails(){
return mDb.query(VIEW_MATCHDETAIL, new String[] {
"MatchId"
}, null, null, null, null, null);
}
VIEW_MATCHDETAIL定义为字符串=“ MatchDetail”
它在这里崩溃说
没有这样的表MatchDetail:从MatchDetail编译SELECT MatchId时
有人以前有这个问题吗?
最佳答案
您那里有一些漂亮的SQL。不幸的是,只有SQL的第一行会在SQLiteDatabase.execSQL中执行。其余的将被静默忽略(方便吗?)。像这样手动拆分语句:
https://github.com/browep/fpt/blob/master/src/com/github/browep/nosql/NoSqlSqliteOpener.java
或者,如果您希望将sql放在单独的文件中,请尝试以下操作:
String sqlText = getSqlText();
for(String sqlStmt : sqlText.split(";"))
myDb.execSQL(slqStmt + ";");