1

This question is almost exactly like mine but none of the answers work with my case.

If it was my question I'd slightly edit it to make it a different question. This question is thus different from the linked one.

Here's the problem: I want a way to match any non-null value ('%') AND null values.

The thing is:

  • I'm using oracle so I can't use IsNull

  • Some columns are NUMBERs, which means I can't use COALESCE(column, ' '). (ORA-00932: inconsistent datatypes: expected NUMBER got CHAR). However, like '%' and like '2118' do work on NUMBER columns.

None of the answers apply to this problem because you can't make a null into an empty string when the column is a NUMBER.

How could I do to achieve this?

Some context:

My procedure takes a lot of parameters, and does a select with all of them. They can all have a value or be null, so if they're null they're replaced with '%'.

That way, the procedure does :

where t.col1 like param1
and t.col2 like param2
...

Most of the times, only one or two parameters is not null. For the others parameters, the procedure needs to match on every row.

But when the value is null, like '%' doesn't match the row. I'm looking for a way to match anything when param x is empty (so paramx = '%')

Teleporting Goat
  • 417
  • 1
  • 6
  • 20
  • Please provide sample data and desired results. Because all values are either valid or `NULL`, it sounds like you don't want any conditions at all. – Gordon Linoff Sep 11 '19 at 14:04
  • @GordonLinoff I added some context. I thought it was way clearer to explain my procedure than to paste a wall of code, I hope that makes it clear enough. – Teleporting Goat Sep 11 '19 at 14:19
  • 1
    `LIKE` is for character values. It's **not** for numbers –  Sep 11 '19 at 14:32

5 Answers5

3

My procedure takes a lot of parameters, and does a select with all of them. They can all have a value or be null, so if they're null they're replaced with '%'.

That seems like you're making life hard for yourself. Leave them null, then do:

where (param1 is null or t.col1 like param1)
and (param2 is null or t.col2 like param2)

If param1 (the procedure argument; life is simpler when your parameter/variable names and column names are different... so I've changed the column names to make it a bit clearer) is null it is basically ignored* and all rows pass that part of the filter, whether the column value is null or not null. If param2 is not null then the is null check for that fails and only rows with (not-null) column values that match param2 value meet that part of the filter.

* Conditions in an or can be evaluated in any order; putting the is null check first doesn't necessarily mean the like won't be evaluated - but the optimiser is pretty smart about that sort of thing

Alex Poole
  • 183,384
  • 11
  • 179
  • 318
  • Smart and elegant solution, thank you! Names are indeed different in my procedure but they're prefixed with something not universal at all, I thought it would be clearer to leave them out. I edited that part of the question. – Teleporting Goat Sep 11 '19 at 15:00
  • I would have done it similarly, but without the "OR": where t.col1 like nvl(param1, t.col1). Not sure how that would affect performance, though. – AndyDan Sep 11 '19 at 18:55
  • My test on 12.1 shows, that even if there are indces on `col1`, `col2`and `col1,col2` I end with `TABLE ACCESS FULL` with `filter((:PARAM1 IS NULL OR "T"."COL1" LIKE :PARAM1) AND (:PARAM2 IS NULL OR "T"."COL2" LIKE :PARAM2))` – Marmite Bomber Sep 11 '19 at 19:04
1

If you want to match a specific value and NULL, you can use OR:

where col = <specific value> or col is null
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
1

In Oracle NVL can be used instead of ISNULL

IF NVL(aNumberColumn,-1) = -1 THEN
    ---whatever
END IF;
HereGoes
  • 1,302
  • 1
  • 9
  • 14
0

Oracle automatically converts NUMBER to VARCHAR2 for like-conditions. So what you have todo is do that yourself so you can use coalesce:

COALESCE(TO_CHAR(column), ' ') like '%'
Radagast81
  • 2,921
  • 1
  • 7
  • 21
  • That's what I did but I got an error from Oracle when the column is a `NUMBER`. – Teleporting Goat Sep 11 '19 at 14:20
  • I added explicit conversion to_char, so the error will not accure any more. Wanted to be as close to your attempt as possible. Wasn't clear if you needed the `like` functionality along with `null`- checks. If not better use `(column is null or column = :1)`... – Radagast81 Sep 12 '19 at 07:05
0

The proposals from other answers based on OR or NVL/ COALESCE are "elegant" and simple, but as a rule they inhibit the index access, which is the most important thing.

You may step down to use dynamic SQL to address the optional parameter problem - which is your case. If a parameter is not passed (it is NULL) - simple ignore it.

So for example with two parameters, if both parameters are passed, generate following SQL

select * from tab t
where t.col1 like :param1
and t.col2 like :param2

If only parameter 1 is given, generate this SQL:

select * from tab t
where t.col1 like :param1

With no parameter you will end with

select * from tab t

Technically it is preferable to have in all SQL statements the same number of bind variables, which is not the case in the above proposal. See this answer for detailed explanation of the trick popularized by Tom Kyte to preserve the number of bind variable with optional parameters.

For example the second statement with only parameter 1 would yield following SQL

select * from tab t
where t.col1 like :param1
and (1=1 or t.col2 like :param2)

The shortcut logik of 1=1(which is TRUE) eliminates the second part of the predicate, but the bind variable is still used, so the number of the bind variables remains constant.

The big advantage is a fine index range access of this query

Marmite Bomber
  • 19,886
  • 4
  • 26
  • 53