SQL Query for Search Page

I am working on a small project for an online databases course and i was wondering if you could help me out with a problem I am having.

I have a web page that is searching a movie database and retrieving specific columns using a movie initial input field, a number input field, and a code field. These will all be converted to strings and used as user input for the query.

Below is what i tried before:

select A.CD, A.INIT, A.NBR, A.STN, A.ST, A.CRET_ID, A.CMNT, A.DT
from MOVIE_ONE A
where A.INIT = :init 
AND A.CD = :cd
AND A.NBR = :num

The way the page must search is in three different cases:

1. (initial and number)
2. (code)
3. (initial and number and code)

The cases have to be independent so if certain field are empty, but fulfill a certain case, the search goes through. It also must be in one query. I am stuck on how to implement the cases.

The parameters in the query are taken from the Java parameters in the method found in an SQLJ file.

If you could possibly provide some aid on how i can go about this problem, I’d greatly appreciate it!

Answer

Consider wrapping the equality expressions in NVL (synonymous to COALESCE) so if parameter inputs are blank, corresponding column is checked against itself. Also, be sure to kick the a-b-c table aliasing habit.

SELECT m.CD, m.INIT, m.NBR, m.STN, m.ST, m.CRET_ID, m.CMNT, m.DT
FROM MOVIE_ONE m
WHERE m.INIT = NVL(:init, m.INIT)
AND m.CD = NVL(:cd, m.CD)
AND m.NBR = COALESCE(:num, m.NBR)

---

To demonstrate, consider below DB2 fiddles where each case can be checked by adjusting value CTE parameters all running on same exact data.

• Case 1

  WITH 
   i(init) AS (VALUES('db2')),
   c(cd) AS (VALUES(NULL)),
   n(num) AS (VALUES(53)),
   cte AS
    ...
  

• Case 2

  WITH 
   i(init) AS (VALUES(NULL)),
   c(cd) AS (VALUES(2018)),
   n(num) AS (VALUES(NULL)),
   cte AS
    ...
  

• Case 3

  WITH 
   i(init) AS (VALUES('db2')),
   c(cd) AS (VALUES(2018)),
   n(num) AS (VALUES(53)),
   cte AS
    ...
  

However, do be aware the fiddle runs a different SQL due to nature of data (i.e., double and dates). But query does reflect same concept with NVL matching expressions on both sides.

SELECT *
FROM cte, i, c, n
WHERE cte.mytype = NVL(i.init, cte.mytype)
AND YEAR(CAST(cte.mydate AS date)) = NVL(c.cd, YEAR(CAST(cte.mydate AS date)))
AND ROUND(cte.mynum, 0) = NVL(n.num, ROUND(cte.mynum, 0));