StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POAdd a filter parameter to ssrs report
text
Body
copied!<p>I have a query that I need to update to allow user to filter out pending applications. I have created the parameter and tried to implement using case but it is not working or giving any error messages on how to correct it. The code is:</p> <pre><code>select distinct pers.person_fname, pers.person_mname, pers.person_lname, le.nationalprovidernumber NPN, lic.licensenumber LICENSE_NUMBER, adr.address_line1 ADDRESS1, adr.address_line2 ADDRESS2, adr.address_line3 ADDRESS3, adr.city CITY, sp.state_province_name STATE, adr.postal_code ZIP_CODE, eml.email, rtp.residencetype_name RESIDENCY, ltp.licensetype_name LICENSE_TYPE, lic.expirationdate DATE_OF_EXPIRATION from odilic_admin.license lic inner join odilic_admin.licenseststimeline lst on lic.license_id = lst.license_id inner join odilic_admin.licenseststype lstp on lst.licenseststype_id = lstp.licenseststype_id inner join odilic_admin.licensedef ldef on lic.licensedef_id = ldef.licensedef_id inner join odilic_admin.licensetype ltp on ldef.licensetype_id = ltp.licensetype_id inner join odilic_admin.residencetype rtp on ldef.residencetype_id = rtp.residencetype_id inner join odilic_admin.licensingentity le on lic.licensingentity_id = le.licensingentity_id inner join odilic_admin.individual ind on le.licensingentity_id = ind.licensingentity_id inner join odidir_admin.person pers on ind.person_id = pers.person_id left outer join odidir_admin.person_address_rel par on pers.person_id = par.person_id left outer join odidir_admin.address adr on par.address_id = adr.address_id left outer join odidir_admin.address_type atp on adr.address_type_id = atp.address_type_id left outer join odidir_admin.state_province sp on adr.state_province_id = sp.state_province_id left outer join (select pr.person_id, em.email_id, em.email from odidir_admin.person pr, odidir_admin.person_email_rel pe, odidir_admin.email em where pr.person_id = pe.person_id and pe.email_id = em.email_id and email_type_id = 2) eml on pers.person_id = eml.person_id where ltp.licensetype_id in (:License_type) and lstp.licenseststype_name = 'Active' and atp.address_type_name = 'Mailing Licensing' and (lic.expirationdate >= current_date and trunc(lic.expirationdate) = :Expiration_Date) and sysdate between lst.periodbegindate and lst.periodenddate order by lic.licensenumber </code></pre> <p>In order to get applications that are pending I need to access the table odilic_admin.licenseappl and filter out all licenses with appststype = 2 (pending). To do this I added a join to the query before the last left outer join andt hen a case at bottom for when this parameter is selected.</p> <pre><code>select distinct pers.person_fname, pers.person_mname, pers.person_lname, le.nationalprovidernumber NPN, lic.licensenumber LICENSE_NUMBER, adr.address_line1 ADDRESS1, adr.address_line2 ADDRESS2, adr.address_line3 ADDRESS3, adr.city CITY, sp.state_province_name STATE, adr.postal_code ZIP_CODE, eml.email, rtp.residencetype_name RESIDENCY, ltp.licensetype_name LICENSE_TYPE, lic.expirationdate DATE_OF_EXPIRATION from odilic_admin.license lic inner join odilic_admin.licenseststimeline lst on lic.license_id = lst.license_id inner join odilic_admin.licenseststype lstp on lst.licenseststype_id = lstp.licenseststype_id inner join odilic_admin.licensedef ldef on lic.licensedef_id = ldef.licensedef_id inner join odilic_admin.licensetype ltp on ldef.licensetype_id = ltp.licensetype_id inner join odilic_admin.residencetype rtp on ldef.residencetype_id = rtp.residencetype_id inner join odilic_admin.licensingentity le on lic.licensingentity_id = le.licensingentity_id inner join odilic_admin.individual ind on le.licensingentity_id = ind.licensingentity_id inner join odidir_admin.person pers on ind.person_id = pers.person_id left outer join odidir_admin.person_address_rel par on pers.person_id = par.person_id left outer join odidir_admin.address adr on par.address_id = adr.address_id left outer join odidir_admin.address_type atp on adr.address_type_id = atp.address_type_id left outer join odidir_admin.state_province sp on adr.state_province_id = sp.state_province_id **left outer join odilic_admin.licenseappl appl on lic.licensingentity_id = appl.licenseappl_id** left outer join (select pr.person_id, em.email_id, em.email from odidir_admin.person pr, odidir_admin.person_email_rel pe, odidir_admin.email em where pr.person_id = pe.person_id and pe.email_id = em.email_id and email_type_id = 2) eml on pers.person_id = eml.person_id where ltp.licensetype_id in (:License_type) and lstp.licenseststype_name = 'Active' and atp.address_type_name = 'Mailing Licensing' and (lic.expirationdate >= current_date and trunc(lic.expirationdate) = :Expiration_Date) and sysdate between lst.periodbegindate and lst.periodenddate **case :pending when = yes then appl.applststype_id !=2 end** order by lic.licensenumber </code></pre> <p>Instead of the case I have also tried using an IF with the same result. This looks like: </p> <pre><code>if :Pending = 1 then and appl.applststype_id != 2; end if; </code></pre> <p>Any help to get me past this is greatly appreciated and I will be sure to vote and select most correct answer to help me solve this.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload