0

I have a variable v_brand in a sql procedure. If that variable is not null then

select * from table where column1 = someValue and brandColumn = v_brand;

If the variable is null

select * from table where column1 = someValue;

Best I could come up with is

IF v_brand IS NOT NULL THEN

 select * from table where column1 = someValue and brandColumn = v_brand;

ELSE

select * from table where column1 = someValue;

END IF:

The queries I will be using are hundreds of lines. Is there a way to combine both queries, as it seems more maintainable.

Gnqz
  • 3,292
  • 3
  • 25
  • 35
user3728557
  • 3
  • 4
  • You could try `brandColunn = coalesce(v_brand, brandColumn)` – Schwern May 08 '23 at 04:51
  • Looks like this will work. Please add this as answer, will accept as solution after trying it out – user3728557 May 08 '23 at 04:58
  • Does this answer your question? [Oracle SQL - How to build where clause with optional search parameters](https://stackoverflow.com/questions/30454886/oracle-sql-how-to-build-where-clause-with-optional-search-parameters) – astentx May 08 '23 at 21:27

1 Answers1

1

you can combine the 2 queries using some conditional logic

SELECT *
FROM table
WHERE column1 = someValue
AND (brandColumn = v_brand OR v_brand IS NULL);

check whether v_brand either =brandColumn or NULL

  • v_brand is NULL + OR returns true (query returns all rows where column1 equals someValue -- regardless of brandColumn)

  • v_brand not NULL -- (brandColumn = v_brand) will be evaluated(true--the row will be returned -- if false -- AND will be false + row will be excluded from result set)

Lemonina
  • 712
  • 2
  • 14