Menu
a couple of queries regarding a database query:
So I have the following database query, which has a WHERE statement which includes three String variables (rackingSystem, manufacturer, "Amber Risk").
public Cursor fetchComponentsAndSpecForManufacturer(long inspectionId, String rackingSystem, String manufacturer) {
Cursor mCursor =
rmDb.query(true, DAMAGED_COMPONENTS_TABLE, new String[] {
DAMAGED_COMPONENT_ID,
LOCATION_LINK,
RUN_LINK,
AREA_LINK,
INSPECTION_LINK,
LOCATION_REF,
RACKING_SYSTEM,
COMPONENT,
COMPONENT_TYPE,
QUANTITY,
POSITION,
RISK,
ACTION_REQUIRED,
NOTES_GENERAL,
MANUFACTURER,
TEXT1,
TEXT2,
TEXT3,
TEXT4,
NOTES_SPEC,
SPEC_SAVED},
INSPECTION_LINK + " = " + inspectionId + " AND " + RACKING_SYSTEM + " = ? AND " + MANUFACTURER + " = ? AND " + RISK + " = ? ",
new String[] {rackingSystem, manufacturer, "Amber Risk"},
COMPONENT_TYPE + ", " + TEXT1 + ", " + TEXT2 + ", " + TEXT3 + ", " + TEXT4 + ", " + NOTES_SPEC,
null, null, null);
if (mCursor != null) {
mCursor.moveToFirst();
}
return mCursor;
}
Question 1:
How do I search for NULL as, in the above example, manufacturer may be NULL in the database (as the user may not have inputed anything into the database for that bit yet).
Question 2:
Can I include an OR statement in the WHERE statement? So at the moment I am seraching for all records that are "Amber Risk", but I need to also include in this list entries that are "Red Risk". If I can do this, how should I edit the WHERE statement?
237
SQLite NULL is the term used to represent a missing value. A NULL value in a table is a value in a field that appears to be blank. A field with a NULL value is a field with no value. It is very important to understand that a NULL value is different than a zero value or a field that contains spaces.
To determine whether an SQLite database file is empty, execute the following query: SELECT COUNT(*) FROM sqlite_schema; You will get the number of objects (tables, indices, views, triggers) in the database or 0 if the file is empty.
SQLite Null ValuesIf table has Null value, then it will display as blank. The Null value represents represent the absence of a value or empty or no value. By using the NULL keyword, we can represent NULL or empty string values.
How do I search for NULL as, in the above example, manufacturer may be NULL in the database
Use MANUFACTURER + " IS NULL".
Can I include an OR statement in the WHERE statement?
Yes. Let's search for null or empty manufacturers:
MANUFACTURER + " IS NULL OR " + MANUFACTURER + " = ''"
So at the moment I am seraching for all records that are "Amber Risk", but I need to also include in this list entries that are "Red Risk".
INSPECTION_LINK + " = " + inspectionId + " AND " + RACKING_SYSTEM + " = ? AND " +
MANUFACTURER + " IS NULL AND (" + RISK + " = ? OR " + RISK + " = ?)",
new String[] {rackingSystem, manufacturer, "Amber Risk", "Red Risk"},
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
