The forum will experience an outage sometime between February 10 at 7:00pm EST and February 12 at 11:59 EST for installation of security updates. The actual time and duration of the outage are unknown but attempts will be made to minimize the downtime. We apologize for any inconvenience.

Hi

I am wanting to write a query that shows accounts related to a selected user createdby but only if a user has been entered, if not I want to show all users.

An example of my code so far can be seen below. I just need to know what goes where the '%' is.

SELECT forename, surname, jobtitle, divname
FROM dba.contact
WHERE createdby = case when @user is null then '%' else @user

asked 22 Jul '16, 07:15

Jongee's gravatar image

Jongee
1183416
accept rate: 0%


In order to use wildcards like '%' in a comparison, you have to use the LIKE operator (or SIMILAR/REGEXP for v11 and above), so the following should work:

WHERE createdby like case when @user is null then '%' else @user

In contrast, in your sample, "createdby = '%'" would only evaluate to true for the value '%' itself.

I'm assuming that createdby is declared as NOT NULL, otherwise you would need to handle NULL values, as well.


Note, a more efficient (and comprehensible IMHO) condition, also treating possible NULLs for createdby correctly, would be to use

WHERE createdby = @user or @user is null
permanent link

answered 22 Jul '16, 07:50

Volker%20Barth's gravatar image

Volker Barth
29.3k287438644
accept rate: 32%

edited 22 Jul '16, 07:56

Note that using either of Volker's suggestions will require a table scan since the predicates are not sargable and therefore a table scan will be required.

An even more efficient way of handling this case would be to test if @user is null outside of the query and then query all users or just the one appropriately. I.e.

IF @user is NULL THEN
  SELECT forename, surname, jobtitle, divname
  FROM dba.contact;
ELSE
  SELECT forename, surname, jobtitle, divname
  FROM dba.contact
  WHERE createdby = @user;
END IF;

Provided there is an index on createdby then the ELSE clause query can do an easy index lookup.

(22 Jul '16, 08:29) Mark Culp
Replies hidden
1

Mark, could you please tell why "WHERE createdby = @user or @user is null" is not sargable? - I would think the optimizer will decide whether @user (treated as a constant for that query) is null or not and would then use the sargable predicate "x = someValue" (in case x is indexed, of course) or would return all rows (thereby necessarily doing a table scan) otherwise.

Or do you relate to ASA9 which might work different than current versions here?

(22 Jul '16, 08:49) Volker Barth
2

Volker: You are correct. I tested v17 and it showed that the latest 17.0.4 optimizer is smart enough to do some work with the constants to eliminate predicates and therefore your suggested solution is good. I did the same test with v9 and the query plan each time (1: @user = NULL, 2: @user not NULL) involves a sequential scan. Without more testing (or looking the code or docs) I do not know at what point the smarts were added to the optimizer.

(22 Jul '16, 09:48) Mark Culp

Thanks for all the additional comments.

(27 Jul '16, 09:16) Jongee
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×95
×18

question asked: 22 Jul '16, 07:15

question was seen: 188 times

last updated: 27 Jul '16, 09:16