HistoryGeek Posted October 26, 2016 Share Posted October 26, 2016 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 tableWHERECREATE_DEPT = $P{CR_DEPT}AND CURRENT_DEPT = $P{CU_DEPT}[/code]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! Link to comment Share on other sites More sharing options...
todorovic.t.zoran Posted November 10, 2016 Share Posted November 10, 2016 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)... Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now