SQLite for Android custom table view (SQL VIEW, not Android view) discrepancy?

Question

I've created a custom view in an SQLite database for an Android application.
I'm using Sqliteman on Ubuntu to test my SQL statements before I put them in my app.
I'm trying to do a simple select statement on my view.
The select statement works fine in SQLiteman but when I put the same statement in my code it throws an error.

The statement:

select * from item_view where parent_item_id = 0;

The view (converted to Java as a String):

"create view if not exists item_view as select " +
    "item._id, item.status, item.name, item.position, " +
    "item.parent_item_id, item.note_id, item.other_id, " + 
    "note.contents, other.priority " +
"from item, note, other where item.note_id = note._id and item.other_id = other._id"

The error:

07-16 14:21:15.153: ERROR/AndroidRuntime(5054): Caused by: android.database.sqlite.SQLiteException: no such column: parent_item_id: , while compiling: SELECT * FROM item_view WHERE parent_item_id = 0

I first tried calling the field item.parent_item_id in my select statement, but that didn't work.
Then I pulled the db and opened it with Sqliteman.
The fields were listed as they were in the original tables (_id, status, name, etc.)
So I ran the SQL above in Sqliteman and was able to retrieve the appropriate data no problem but I can't get it to work in my app either way.
I also noticed that dropping the view as a DROP TABLE command worked in SQLiteman but not in my app.
I'm wondering if I'm maybe missing some other VIEW specific functionality.
I didn't see any in either the android documentation or any SQL documentation though.

Technically I could just do this with a more complex SQL call using the original tables, but all my tables follow certain guidelines so that I can dynamically generate SQL calls. I'd like to have the view tables work to keep my code uniform and always reusing the same calls to avoid maintenance and other buggy issues from duplicate code.

Answer 1

It turns out that you need to specifically name each field you're creating so that android can treat it like a regular table. The solution...

"create view if not exists item_view as select " +
    "item._id as _id, item.status as item_status, item.name as item_name, item.position as item_position, " +
    "item.parent_item_id as item_parent_item_id, item.note_id as item_note_id, item.other_id as item_other_id, " + 
    "note.contents as note_contents, other.priority as other_priority " +
"from item, note, other where item.note_id = note._id and item.other_id = other._id"