J Javier Mateo Member Licensed User Longtime User May 25, 2015 #1 I have a problem with the right function into a 'SELECT' query. My query is SELECT MAX(RIGHT(VALUE,2) ) AS MAYOR FROM MYTABLE. This query works good in my database SQLite when I execute but it doesn't work when I use it in B4A. I have made a View and works good when I use it in the database context but the when I try execute it in B4A the compiler send me errors. The code is: myDocu is a cursor myQuery = "SELECT MAX(RIGHT(NUMALB,2)) AS VALOR FROM ALBARAN" myDocu = getData.mData.ExecQuery(myQuery) Error occurred on line: 859 (getventas) android.database.sqlite.SQLiteException: near "(": syntax error (code 1): , while compiling: SELECT MAX(RIGHT(NUMALB,2)) AS MVALUE FROM MYTABLE at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method) at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:886) at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:497) at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588) at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:58) at android.database.sqlite.SQLiteQuery.<init>(SQLiteQuery.java:37) at android.database.sqlite.SQLiteDirectCursorDriver.query(SQLiteDirectCursorDriver.java:44) at android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:1314) at android.database.sqlite.SQLiteDatabase.rawQuery(SQLiteDatabase.java:1253) at anywheresoftware.b4a.sql.SQL.ExecQuery2(SQL.java:167) at anywheresoftware.b4a.sql.SQL.ExecQuery(SQL.java:155) at java.lang.reflect.Method.invokeNative(Native Method) at java.lang.reflect.Method.invoke(Method.java:525) at anywheresoftware.b4a.shell.Shell.runMethod(Shell.java:636) at anywheresoftware.b4a.shell.Shell.raiseEventImpl(Shell.java:302) at anywheresoftware.b4a.shell.Shell.raiseEvent(Shell.java:238) at java.lang.reflect.Method.invokeNative(Native Method) at java.lang.reflect.Method.invoke(Method.java:525) at anywheresoftware.b4a.ShellBA.raiseEvent2(ShellBA.java:121) at anywheresoftware.b4a.BA.raiseEvent2(BA.java:175) at anywheresoftware.b4a.BA.raiseEvent(BA.java:171) at anywheresoftware.b4a.objects.ViewWrapper$1.onClick(ViewWrapper.java:77) at android.view.View.performClick(View.java:4222) at android.view.View$PerformClick.run(View.java:17620) at android.os.Handler.handleCallback(Handler.java:800) at android.os.Handler.dispatchMessage(Handler.java:100) at android.os.Looper.loop(Looper.java:194) at android.app.ActivityThread.main(ActivityThread.java:5370) at java.lang.reflect.Method.invokeNative(Native Method) at java.lang.reflect.Method.invoke(Method.java:525) at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:833) at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:600) at dalvik.system.NativeStart.main(Native Method)
I have a problem with the right function into a 'SELECT' query. My query is SELECT MAX(RIGHT(VALUE,2) ) AS MAYOR FROM MYTABLE. This query works good in my database SQLite when I execute but it doesn't work when I use it in B4A. I have made a View and works good when I use it in the database context but the when I try execute it in B4A the compiler send me errors. The code is: myDocu is a cursor myQuery = "SELECT MAX(RIGHT(NUMALB,2)) AS VALOR FROM ALBARAN" myDocu = getData.mData.ExecQuery(myQuery) Error occurred on line: 859 (getventas) android.database.sqlite.SQLiteException: near "(": syntax error (code 1): , while compiling: SELECT MAX(RIGHT(NUMALB,2)) AS MVALUE FROM MYTABLE at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method) at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:886) at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:497) at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588) at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:58) at android.database.sqlite.SQLiteQuery.<init>(SQLiteQuery.java:37) at android.database.sqlite.SQLiteDirectCursorDriver.query(SQLiteDirectCursorDriver.java:44) at android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:1314) at android.database.sqlite.SQLiteDatabase.rawQuery(SQLiteDatabase.java:1253) at anywheresoftware.b4a.sql.SQL.ExecQuery2(SQL.java:167) at anywheresoftware.b4a.sql.SQL.ExecQuery(SQL.java:155) at java.lang.reflect.Method.invokeNative(Native Method) at java.lang.reflect.Method.invoke(Method.java:525) at anywheresoftware.b4a.shell.Shell.runMethod(Shell.java:636) at anywheresoftware.b4a.shell.Shell.raiseEventImpl(Shell.java:302) at anywheresoftware.b4a.shell.Shell.raiseEvent(Shell.java:238) at java.lang.reflect.Method.invokeNative(Native Method) at java.lang.reflect.Method.invoke(Method.java:525) at anywheresoftware.b4a.ShellBA.raiseEvent2(ShellBA.java:121) at anywheresoftware.b4a.BA.raiseEvent2(BA.java:175) at anywheresoftware.b4a.BA.raiseEvent(BA.java:171) at anywheresoftware.b4a.objects.ViewWrapper$1.onClick(ViewWrapper.java:77) at android.view.View.performClick(View.java:4222) at android.view.View$PerformClick.run(View.java:17620) at android.os.Handler.handleCallback(Handler.java:800) at android.os.Handler.dispatchMessage(Handler.java:100) at android.os.Looper.loop(Looper.java:194) at android.app.ActivityThread.main(ActivityThread.java:5370) at java.lang.reflect.Method.invokeNative(Native Method) at java.lang.reflect.Method.invoke(Method.java:525) at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:833) at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:600) at dalvik.system.NativeStart.main(Native Method)
LucaMs Expert Licensed User Longtime User May 25, 2015 #2 I have not found Right in SQLite documentation. You could use: SELECT MAX(SubStr(VALUE, length(VALUE) - 1)) AS MAYOR FROM MYTABLE Upvote 0
I have not found Right in SQLite documentation. You could use: SELECT MAX(SubStr(VALUE, length(VALUE) - 1)) AS MAYOR FROM MYTABLE
M Mahares Expert Licensed User Longtime User May 26, 2015 #3 Javier Mateo said: SELECT MAX(RIGHT(VALUE,2) ) AS MAYOR FROM MYTABLE Click to expand... Should be: Code: Copy SELECT MAX(SubStr(VALUE, length(VALUE) - 2)) AS MAYOR FROM MYTABLE or Code: Copy SELECT MAX(SubStr(VALUE, -2, 2)) AS MAYOR FROM MYTABLE Upvote 0
Javier Mateo said: SELECT MAX(RIGHT(VALUE,2) ) AS MAYOR FROM MYTABLE Click to expand... Should be: Code: Copy SELECT MAX(SubStr(VALUE, length(VALUE) - 2)) AS MAYOR FROM MYTABLE or Code: Copy SELECT MAX(SubStr(VALUE, -2, 2)) AS MAYOR FROM MYTABLE
LucaMs Expert Licensed User Longtime User May 26, 2015 #4 Mahares said: Should be: Code: Copy SELECT MAX(SubStr(VALUE, length(VALUE) - 2)) AS MAYOR FROM MYTABLE or Code: Copy SELECT MAX(SubStr(VALUE, -2, 2)) AS MAYOR FROM MYTABLE Click to expand... -1, to get the last two characters, not -2 Upvote 0
Mahares said: Should be: Code: Copy SELECT MAX(SubStr(VALUE, length(VALUE) - 2)) AS MAYOR FROM MYTABLE or Code: Copy SELECT MAX(SubStr(VALUE, -2, 2)) AS MAYOR FROM MYTABLE Click to expand... -1, to get the last two characters, not -2
J Javier Mateo Member Licensed User Longtime User May 26, 2015 #5 LucaMs said: I have not found Right in SQLite documentation. You could use: SELECT MAX(SubStr(VALUE, length(VALUE) - 1)) AS MAYOR FROM MYTABLE Click to expand... Thanks a lot for your help LucaMs Upvote 0
LucaMs said: I have not found Right in SQLite documentation. You could use: SELECT MAX(SubStr(VALUE, length(VALUE) - 1)) AS MAYOR FROM MYTABLE Click to expand... Thanks a lot for your help LucaMs
J Javier Mateo Member Licensed User Longtime User May 26, 2015 #6 Mahares said: Should be: Code: Copy SELECT MAX(SubStr(VALUE, length(VALUE) - 2)) AS MAYOR FROM MYTABLE or Code: Copy SELECT MAX(SubStr(VALUE, -2, 2)) AS MAYOR FROM MYTABLE Click to expand... Thanks a lot for you help Mahares, Upvote 0
Mahares said: Should be: Code: Copy SELECT MAX(SubStr(VALUE, length(VALUE) - 2)) AS MAYOR FROM MYTABLE or Code: Copy SELECT MAX(SubStr(VALUE, -2, 2)) AS MAYOR FROM MYTABLE Click to expand... Thanks a lot for you help Mahares,