在Union Query中引入WHERE子句

I have the following table structure:

我有以下表结构:

_id | NotificationFor | DateToNotify | isExtraOrdinary | Notification Data | TypeNotification | FriendsName | RadioType | PrimaryId

The above table is called as Notifications and I have a replica of the same schema -- that table is called as NotificationsCron. Now I have the following Query:

上面的表被称为Notifications,我有一个相同模式的副本 - 该表被称为NotificationsCron。现在我有以下查询:

String magicQuery ="SELECT * FROM (SELECT * FROM Notifications UNION SELECT * FROM NotificationsCron) T ORDER BY SUBSTR(DATE('NOW'), 0)>SUBSTR(DateToNotify, 0), SUBSTR(DateToNotify, 0)";

In the above query I want to use a where clause and make it as follows:

在上面的查询中,我想使用where子句并使其如下所示:

String magicQuery ="SELECT * FROM (SELECT * FROM Notifications Where TypeNotification <> Event UNION SELECT * FROM NotificationsCron) T ORDER BY SUBSTR(DATE('NOW'), 0)>SUBSTR(DateToNotify, 0), SUBSTR(DateToNotify, 0)";

However I get the following error:

但是我收到以下错误:

 android.database.sqlite.SQLiteException: no such column: Event (code 1): , while compiling: SELECT * FROM (SELECT * FROM Notifications Where TypeNotification <> Event UNION SELECT * FROM NotificationsCron) T ORDER BY SUBSTR(DATE('NOW'), 0)>SUBSTR(DateToNotify, 0), SUBSTR(DateToNotify, 0)
07-08 09:37:09.594: E/AndroidRuntime(30637):    at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2189)
07-08 09:37:09.594: E/AndroidRuntime(30637):    at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2216)
07-08 09:37:09.594: E/AndroidRuntime(30637):    at android.app.ActivityThread.access$600(ActivityThread.java:149)
07-08 09:37:09.594: E/AndroidRuntime(30637):    at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1305)
07-08 09:37:09.594: E/AndroidRuntime(30637):    at android.os.Handler.dispatchMessage(Handler.java:99)
07-08 09:37:09.594: E/AndroidRuntime(30637):    at android.os.Looper.loop(Looper.java:153)
07-08 09:37:09.594: E/AndroidRuntime(30637):    at android.app.ActivityThread.main(ActivityThread.java:5000)
07-08 09:37:09.594: E/AndroidRuntime(30637):    at java.lang.reflect.Method.invokeNative(Native Method)
07-08 09:37:09.594: E/AndroidRuntime(30637):    at java.lang.reflect.Method.invoke(Method.java:511)
07-08 09:37:09.594: E/AndroidRuntime(30637):    at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:821)
07-08 09:37:09.594: E/AndroidRuntime(30637):    at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:584)
07-08 09:37:09.594: E/AndroidRuntime(30637):    at dalvik.system.NativeStart.main(Native Method)
07-08 09:37:09.594: E/AndroidRuntime(30637): Caused by: android.database.sqlite.SQLiteException: no such column: Event (code 1): , while compiling: SELECT * FROM (SELECT * FROM Notifications Where TypeNotification <> Event UNION SELECT * FROM NotificationsCron) T ORDER BY SUBSTR(DATE('NOW'), 0)>SUBSTR(DateToNotify, 0), SUBSTR(DateToNotify, 0)
07-08 09:37:09.594: E/AndroidRuntime(30637):    at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
07-08 09:37:09.594: E/AndroidRuntime(30637):    at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:882)
07-08 09:37:09.594: E/AndroidRuntime(30637):    at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:493)
07-08 09:37:09.594: E/AndroidRuntime(30637):    at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588)
07-08 09:37:09.594: E/AndroidRuntime(30637):    at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:58)
07-08 09:37:09.594: E/AndroidRuntime(30637):    at android.database.sqlite.SQLiteQuery.<init>(SQLiteQuery.java:37)
07-08 09:37:09.594: E/AndroidRuntime(30637):    at android.database.sqlite.SQLiteDirectCursorDriver.query(SQLiteDirectCursorDriver.java:44)
07-08 09:37:09.594: E/AndroidRuntime(30637):    at android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:1314)
07-08 09:37:09.594: E/AndroidRuntime(30637):    at android.database.sqlite.SQLiteDatabase.rawQuery(SQLiteDatabase.java:1253)
07-08 09:37:09.594: E/AndroidRuntime(30637):    at com.exa.digitalanniversaries.DBAdapter.magicQuery(DBAdapter.java:536)
07-08 09:37:09.594: E/AndroidRuntime(30637):    at com.exa.digitalanniversaries.FragmentTab1.setList(FragmentTab1.java:301)
07-08 09:37:09.594: E/AndroidRuntime(30637):    at com.exa.digitalanniversaries.FragmentTab1.onCreateView(FragmentTab1.java:180)
07-08 09:37:09.594: E/AndroidRuntime(30637):    at android.app.FragmentManagerImpl.moveToState(FragmentManager.java:829)
07-08 09:37:09.594: E/AndroidRuntime(30637):    at android.app.FragmentManagerImpl.moveToState(FragmentManager.java:1035)
07-08 09:37:09.594: E/AndroidRuntime(30637):    at android.app.BackStackRecord.run(BackStackRecord.java:635)
07-08 09:37:09.594: E/AndroidRuntime(30637):    at android.app.FragmentManagerImpl.execPendingActions(FragmentManager.java:1397)
07-08 09:37:09.594: E/AndroidRuntime(30637):    at android.app.Activity.performStart(Activity.java:5029)
07-08 09:37:09.594: E/AndroidRuntime(30637):    at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2162)
07-08 09:37:09.594: E/AndroidRuntime(30637):    ... 11 more
07-08 09:37:09.872: E/NativeCrypto(30637): ssl=0x5274af58 cert_verify_callback x509_store_ctx=0x52858ab0 arg=0x0
07-08 09:37:09.872: E/NativeCrypto(30637): ssl=0x5274af58 cert_verify_callback calling verifyCertificateChain authMethod=ECDHE_ECDSA

Any hints would be helpful! Thanks!

任何提示都会有所帮助!谢谢!

1 个解决方案

#1

You are using the value Event as though it were a column.

您正在使用值Event,就好像它是一个列。

Put some quotes around it:

在它周围加上一些引用:

String magicQuery ="SELECT * FROM 
                     (SELECT * 
                      FROM Notifications 
                      Where TypeNotification <> 'Event' 
                      UNION 
                      SELECT * 
                      FROM NotificationsCron) T 
                   ORDER BY SUBSTR(DATE('NOW'), 0)>SUBSTR(DateToNotify, 0), SUBSTR(DateToNotify, 0)";

#1