SQL request with case in order by throws "(1) 1st ORDER BY term does not match any column in the result set"

Question

I've got a small problem with an SQL cursor. I'm trying to execute an SQL command on android. I'm trying to sort the returned content by cases, but it seems like the system doesn't accept the returned values (?!) I've tried everything! Do you have the solution ? ;)

cursor = db.rawQuery(c, null);
String c = "SELECT * FROM characters WHERE UPPER(descriptions) LIKE '%" + 
    TextUtils.join("%", arr) + "%' UNION ALL SELECT * FROM words WHERE
    UPPER(descriptions) LIKE '%" + TextUtils.join("%", arr) + "%'
    ORDER BY CASE WHEN UPPER(descriptions) LIKE '" + s + "' THEN 1
    WHEN UPPER(descriptions) LIKE '" + s + "|%' THEN 2 WHEN
    UPPER(descriptions) LIKE '%|" + s + "|%' THEN 2 WHEN UPPER(descriptions)
    LIKE '%|" + s + "' THEN 2 WHEN UPPER(descriptions)
    LIKE '%" + TextUtils.join(" ", arr) + "%' THEN 3 ELSE 4 END, descriptions ASC";

Do I need to sort the command like this?

SELECT * FROM characters WHERE UPPER(descriptions) LIKE '%" + TextUtils.join("%", arr) + "%' 
UNION ALL
SELECT * FROM words WHERE UPPER(descriptions) LIKE '%" + TextUtils.join("%", arr) + "%'                                     
ORDER BY
    CASE
        WHEN UPPER(descriptions) LIKE '" + s + "' THEN 1          
        WHEN UPPER(descriptions) LIKE '" + s + "|%' THEN 2 
        WHEN UPPER(descriptions) LIKE '%|" + s + "|%' THEN 2 
        WHEN UPPER(descriptions) LIKE '%|" + s + "' THEN 2 
        WHEN UPPER(descriptions) LIKE '%" + TextUtils.join(" ", arr) + "%' THEN 3 
        ELSE 4 
    END,
descriptions ASC

Thanks ;)

EDIT: An SQL command after inserting the search term "a test"

SELECT * FROM characters WHERE UPPER(descriptions) LIKE '%A%TEST%'
UNION ALL
SELECT * FROM words WHERE UPPER(descriptions) LIKE '%A%TEST%'
ORDER BY
    CASE
        WHEN UPPER(descriptions) LIKE 'A TEST' THEN 1
        WHEN UPPER(descriptions) LIKE 'A TEST|%' THEN 2
        WHEN UPPER(descriptions) LIKE '%|A TEST|%' THEN 2
        WHEN UPPER(descriptions) LIKE '%|A TEST' THEN 2
        WHEN UPPER(descriptions) LIKE '%A TEST%' THEN 3
        ELSE 4
    END,
descriptions ASC

table schema:

characters (t TEXT,s TEXT,jy TEXT,descriptions TEXT)
words (t TEXT,s TEXT,jy TEXT,descriptions TEXT)

Answer 1

When you are SELECTing records from a single table, you can use anything from those records for sorting.

However, when you are combining multiple queries with UNION, the sorting is done on the entire result, so you must use some column from the result for ordering. In this case, this means that you must move the calculation into the query itself:

SELECT t, s, jy, descriptions, CASE ... END AS ordernr FROM ...
UNION ALL
SELECT t, s, jy, descriptions, CASE ... END AS ordernr FROM ...
ORDER BY ordernr,
         descriptions