CODEWITHSUNDEEP
sqlsqlite
SU3
SU3

Reputation: 5409

Set default value for WHERE clause for specific columns

Suppose I've created a table like this:

CREATE TABLE stuff(
  a1 TEXT,
  a2 TEXT,
  . . .
  an TEXT,
  data TEXT
);

I need to make SELECT queries like this:

SELECT data FROM stuff WHERE
a1="..." and a2="..." and . . . and an="..."

selecting a specific value of data, with all a values specified.

There are a lot of as that have some value that I consider default.

Is there a way to add some kind of statement to the table or the query that will use some default value for as which are not explicitly constrained in the where clause? For example, so that if I don't write a1="b" after where I get only rows where a1 is equal to "b", rather than any value, but if I write a1="c" I get those.

The default is the same for all as.

The best solution for me would be to bake the default into the table or the database file.

Upvotes: 1

Views: 2642

Answers (1)

Joshua Schlichting
Joshua Schlichting

Reputation: 3450

Is there a way to add some kind of statement to the table or the query that will use some default value for as which are not explicitly constrained in the where clause?

Short answer: No.

Based off of what you said here:

For example, so that if I don't write a1="b" after where

I'm thinking you might be running this query often, maybe even manually, and you want to pass in some different values. If this is indeed the case, you can pass parameters in and use some variables to handle this.

What you can do, is have a CASE statement in that WHERE to help. Consider the following:

SELECT *
FROM
    table
WHERE
    CASE
        WHEN %s IS NOT NULL
            THEN a1 = %s
        ELSE a1 = 'b'
        END
    [AND/OR] <other constraints here>

Now, the syntax of how you actually do this will vary, based on what you're actually using to execute your queries. Are they being ran from a python program? something in .net? etc etc

Also, you wouldn't have to strictly stick to the IS NOT NULL I used there, you could have some other values to do other things with. Up to you.

EDIT:

To Shawn's point in the comments, if %s, the argument being passed in, is either NOT NULL or a legitmate value, then ifnull() would be a cleaner alternative for this case statement. Example below:

WHERE
    a1 = ifnull(%s, 'b')

Upvotes: 1

Related Questions