How can I use a wildcard as a default parameter value?

0

I am trying to assemble a report drawing information from a table using four columns: NAME, DOB, CREATE_DEPT, and CURRENT_DEPT. The report selects any records which have exact matches for a given NAME and DOB combination. I want to be able to add parameters, $P{CR_DEPT} and $P{CU_DEPT}, which will allow the user to select only duplicates with specific values for CREATE_DEPT or CURRENT_DEPT or for the entire table. I have tried making the default value of each parameter = % but the query does not run. Essentially the department-related part of the query looks like this:

SELECT *
FROM TABLE
WHERE
CREATE_DEPT = $P{CR_DEPT}
AND CURRENT_DEPT = $P{CU_DEPT}

In one case CR_DEPT and/or CU_DEPT will have a specific value, with the other possibly the wildcard default. In another case both will have the wildcard default value.

I've only found a couple similar questions here - they were both fairly old and the answers were work-arounds, not real solutions. I'm hoping there is a better, more current solution to this problem.

Thanks!

HistoryGeek's picture
Joined: Oct 26 2016 - 8:29am
Last seen: 2 years 4 months ago

1 Answer:

0

I hope i understood question.

Why using wild card at all?

Instead of wildcard, why not NULL as default for parametars $P{CR_DEPT} and $P{CR_DEPT} and in query write something like  (or NVL for oracle):

CREATE_DEPT = coalesce($P{CR_DEPT}, CREATE_DEPT)

AND  CURRENT_DEPT = coalesce($P{CR_DEPT}, CURRENT_DEPTT)

 

Alternative  would be something like ....($P{CR_DEPT}=wildcard or CREATE_DEPT=$P{CR_DEPT})....  but it looks wrong (and wildcard must not be something that can be found in CREATE_DEPT or CURRENT_DEPT values in db table)...

todorovic.t.zoran's picture
Joined: Nov 9 2016 - 9:03am
Last seen: 2 years 2 months ago
Feedback
randomness