CODEWITHSUNDEEP
androidsqlite
naffie
naffie

Reputation: 729

recurring ambiguous column name _id sqlite error in android

I have tried editing my code according to the suggestion i found on this site,but nothing seems to work..i still keep getting the following logcat error:

03-24 11:44:27.037: E/AndroidRuntime(1809): Caused by: android.database.sqlite.SQLiteException: 
ambiguous column name: _id (code 1): , while compiling: 
CREATE VIEW customerView AS SELECT _id AS _id,   fName, lName, customerMeterId, customerMeterNumber,   customerPlotNumber, _id FROM customers AS customers  
INNER JOIN meters AS meters ON customerMeterId =_id

The class referenced above in logcat:

public class ViewCustomers {

public static final String TABLE_CUSTOMER_VIEW = "customerView";

public static final String CUSTOMER_VIEW_ID = CustomerTableDetails.KEY_CUSTOMER_ID;
public static final String CUSTOMER_VIEW_FIRSTNAME = CustomerTableDetails.KEY_FIRST_NAME;
public static final String CUSTOMER_VIEW_LASTNAME = CustomerTableDetails.KEY_LAST_NAME;
public static final String CUSTOMER_VIEW_METER = CustomerTableDetails.KEY_METER_NUMBER;
public static final String CUSTOMER_VIEW_PLOT = CustomerTableDetails.KEY_PLOT_NUMBER;
public static final String CUSTOMER_VIEW_KEY_METER_ID = CustomerTableDetails.KEY_METER_ID;

private static final String CREATE_CUSTOMER_VIEW = ""
        + "CREATE VIEW " + TABLE_CUSTOMER_VIEW 
        + " AS SELECT "+ CustomerTableDetails.KEY_CUSTOMER_ID+" AS _id,"+
        " "+CustomerTableDetails.KEY_FIRST_NAME+","+
        " "+CustomerTableDetails.KEY_LAST_NAME+","+
        " "+CustomerTableDetails.KEY_METER_ID+","+
        " "+CustomerTableDetails.KEY_METER_NUMBER+","+
        " "+CustomerTableDetails.KEY_PLOT_NUMBER+","+
        " "+MeterTableDetails.METER_ID+""+
        " FROM "+CustomerTableDetails.TABLE_CUSTOMERS+" AS customers "+" INNER JOIN "+MeterTableDetails.TABLE_METERS+" AS meters"+
        " ON "+CustomerTableDetails.KEY_METER_ID+" ="+MeterTableDetails.METER_ID;


public static void onCreate(SQLiteDatabase database) {
    database.execSQL(CREATE_CUSTOMER_VIEW);
}

public static void onUpgrade(SQLiteDatabase database, int oldVersion,
        int newVersion) {
    Log.w(ViewCustomers.class.getName(), "Upgrading database from version "
            + oldVersion + " to " + newVersion
            + ", which will destroy all old data");
    database.execSQL("DROP VIEW IF EXISTS " + ViewCustomers.TABLE_CUSTOMER_VIEW);
    onCreate(database);
}

I've even used aliases but still no solution.Now i don't know how else to solve it.

EDIT

After the corrections:

 private static final String CREATE_CUSTOMER_VIEW = ""
        + "CREATE VIEW " + TABLE_CUSTOMER_VIEW 
        + " AS SELECT "+MeterTableDetails.TABLE_METERS+"."+MeterTableDetails.METER_ID+""+" AS "+ MeterTableDetails.TABLE_METERS+"."+MeterTableDetails.METER_ID +","+
        " "+CustomerTableDetails.KEY_FIRST_NAME+","+
        " "+CustomerTableDetails.KEY_LAST_NAME+","+
        " "+CustomerTableDetails.KEY_METER_ID+","+
        " "+CustomerTableDetails.KEY_METER_NUMBER+","+
        " "+CustomerTableDetails.KEY_PLOT_NUMBER+","+
        " "+CustomerTableDetails.TABLE_CUSTOMERS+"."+ CustomerTableDetails.KEY_CUSTOMER_ID+

        " FROM "+CustomerTableDetails.TABLE_CUSTOMERS+" AS customers "+" INNER JOIN "+MeterTableDetails.TABLE_METERS+" AS meters"+
        " ON "+CustomerTableDetails.KEY_METER_ID+" ="+MeterTableDetails.TABLE_METERS+"."+MeterTableDetails.METER_ID;

EDIT 2

03-24 13:58:24.687: I/create statement(3098): CREATE VIEW customerView AS SELECT meters._id AS   meters._id, fName, lName, customerMeterId, customerMeterNumber, 
customerPlotNumber, customers._id FROM customers AS customers  
INNER JOIN meters AS meters ON customerMeterId = meters._id

Upvotes: 2

Views: 5898

Answers (1)

DSS
DSS

Reputation: 7259

use tablename._id in order to refer to _id in the query above

change:

CREATE VIEW customerView AS SELECT _id AS _id,
fName, lName, customerMeterId, customerMeterNumber, 
customerPlotNumber, _id FROM customers AS customers  
INNER JOIN meters AS meters ON customerMeterId =_id

to 

CREATE VIEW customerView AS SELECT customers._id AS _id, 
fName, lName, customerMeterId, customerMeterNumber,   customerPlotNumber, 
_id FROM customers AS customers  
INNER JOIN meters AS meters ON 
customerMeterId =customers._id // or meters._id -> whatever you think suits

or

CREATE VIEW customerView AS SELECT meters._id AS _id, 
fName, lName, customerMeterId, customerMeterNumber,   customerPlotNumber, 
_id FROM customers AS customers  
INNER JOIN meters AS meters ON customerMeterId =meters._id

Upvotes: 11

Related Questions