Android Question Union all sql

[FabioCirillo]

hello
I have a problem with a sql query and precisely with the union all.
The query as written works perfectly, I also tested the program Db sql lite Browser, but when you run basic4android tells me that there is an error that a database column does not exist.

This is the error:

Error occurred on line: 529 (frmfinale)
java.lang.IllegalArgumentException: column 'idtipofase' does not exist
at android.database.AbstractCursor.getColumnIndexOrThrow(AbstractCursor.java:303)
at anywheresoftware.b4a.sql.SQL$CursorWrapper.GetString(SQL.java:355)
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:163)
at anywheresoftware.b4a.BA.raiseEvent(BA.java:159)
at anywheresoftware.b4a.objects.ViewWrapper$1.onClick(ViewWrapper.java:66)
at android.view.View.performClick(View.java:4475)
at android.view.View$PerformClick.run(View.java:18786)
at android.os.Handler.handleCallback(Handler.java:730)
at android.os.Handler.dispatchMessage(Handler.java:92)
at android.os.Looper.loop(Looper.java:176)
at android.app.ActivityThread.main(ActivityThread.java:5419)
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:1046)
at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:862)
at dalvik.system.NativeStart.main(Native Method)

but I can confirm that the vb syntax is well written.

this is my vb code:

Dim cursore As Cursor
cursore = sqldb.ExecQuery("SELECT fase_finale.id,fase_finale.idevento,fase_finale.idtorneo,fase_finale.idsquadra,fase_finale.idtipofase,fase_finale.nrbicchieri,squadre.nome FROM fase_finale JOIN squadre ON fase_finale.idsquadra=squadre.id WHERE idtorneo="& frmtorneo.idtorneo &" AND idevento="& frmtorneo.idevento & _
" UNION ALL Select classifica_fase_gironi.id,classifica_fase_gironi.idevento,classifica_fase_gironi.idtorneo,classifica_fase_gironi.idsquadra,classifica_fase_gironi.idtipofase,classifica_fase_gironi.nrbicchieri,squadre.nome FROM classifica_fase_gironi JOIN squadre ON classifica_fase_gironi.idsquadra=squadre.id WHERE idtorneo="& frmtorneo.idtorneo &" AND idevento="& frmtorneo.idevento &" ORDER BY idtipofase DESC, fase_finale.nrbicchieri DESC")
For i = 0 To cursore.RowCount - 1
cursore.Position = i
If cursore.GetInt("fase_finale.idtipofase")=3 Then
.............
End If
If cursore.GetInt("fase_finale.idtipofase")=2 Then 'semifinali
..............
End If
Next
cursore.Close

help me please

 

[udg]

In your ORDER BY you cite idtipofase but don't specify its table name as done previously (fase_finale or cassifica_fase_gironi).
Could be that?

 

[FabioCirillo]

I wrote this:

ORDER BY fase_finale.idtipofase DESC, fase_finale.nrbicchieri DESC"

but continues to make the same mistake

 

[LucaMs]

Sometimes it happens to point to the wrong db, in which the field "idtipofase" does not exists (an old version).

I would try the query without ORDER BY and if everything works well, I would make sure to point to the right db.

 

[FabioCirillo]

ok confirmed that removing the order by works but I need the Order by

 

[udg]

ORDER BY fase_finale.idtipofase DESC, fase_finale.nrbicchieri DESC

I am not a Sqlite expert, but reading its doc it seems the ASC/DESC property should be used just once, at the end. So you should try:

ORDER BY fase_finale.idtipofase, fase_finale.nrbicchieri DESC

Edit: according to this I am wrong..but another doc shows what suggested above

Umberto​

 

[FabioCirillo]

in my query can add code that prevents you to select the records of two tables that have the same squadre.nome?

 

[LucaMs]

This type of error has happened to others (just search in the forum).

The message specifically says that there is no that field in the table.
It happens pointing to an older version of db, sorry for the repetition.

I'm searching my "FieldExists" function but it is disappeared

Try a simple "select idtipofase... "

 

[Mahares]

If you alias your columns that match with the same alias, I think it will work for you. Here is a simple example that can give you an idea to apply to your tables:

"SELECT A.Field1 AS F1, A.Field2 AS F2, A.Field3 AS F3 FROM Table1 A " _
& "UNION ALL Select B.FieldX AS F1, B.FieldY As F2, B.FieldZ AS F3 FROM Table2 B  ORDER BY F3 DESC, F1 ASC"

 

[manuaaa]

If you alias your columns that match with the same alias, I think it will work for you. Here is a simple example that can give you an idea to apply to your tables:

"SELECT A.Field1 AS F1, A.Field2 AS F2, A.Field3 AS F3 FROM Table1 A " _
& "UNION ALL Select B.FieldX AS F1, B.FieldY As F2, B.FieldZ AS F3 FROM Table2 B  ORDER BY F3 DESC, F1 ASC"

i had already tested this but it's not working.....

how to use this union all command

WITH Employeelist as
(SELECT boss.fsid, boss.Name, boss.designation, boss.RPTO, 1 as EmpLevel from FS6 as boss where boss.fsid =384
UNION ALL
SELECT emp.fsid, emp.Name, emp.designation, emp.RPTO, EL.EmpLevel + 1 from FS6 as emp INNER JOIN Employeelist AS EL ON emp.RPTOID = EL.FSID where emp.RPTOID IS NOT NULL)
SELECT * FROM Employeelist