How to search for NULL in Android database query & OR statement in selection

Question

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?

sql `where X is null` or `isnull(x)`? – Marc B Jan 05 '13 at 19:02 — Marc B, Jan 05 '13 at 19:02

Sam · Accepted Answer · 2013-01-05T19:09:39.080

9

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"},

edited Jan 05 '13 at 19:09

answered Jan 05 '13 at 19:03

Sam

86,580
20
181
179

Ah, but I search MANUFACTURER using a variable (manufacturer) - this may or may not be NULL. If it is NULL, it currently causes my app to crash (any idea why this might be?). Thanks for confirmation of OR statement though, will give this a shot. – Scamparelli Jan 05 '13 at 19:09
"but I search MANUFACTURER using a variable (manufacturer)" Ok, so you want to _ignore_ the manufacturer portion entirely when it is `null`? – Sam Jan 05 '13 at 19:13
I'm afraid not, the MANUFACTURER may be NULL, but there may be other data attached to those records that I need to show (i.e. the manufacturer would just be blank when I output the data).. – Scamparelli Jan 05 '13 at 19:15
You should just have two separate queries and use an if statement to choose which version to run: `if(manufacturer == null)`. – Sam Jan 05 '13 at 19:18
Ok, blimey that'll work. I'll give it a shot, thanks so much. If you fancy another similar question to answer: http://stackoverflow.com/questions/14173359/get-a-count-of-the-number-of-times-distinct-entries-occur-in-database-query-and/14173545#comment19642734_14173545 :) – Scamparelli Jan 05 '13 at 19:23

How to search for NULL in Android database query & OR statement in selection

1 Answers1

Linked