NCHASHIM Posted November 18, 2019 Share Posted November 18, 2019 My report SQL includes a clauseWHERE ($P!{UICFILTER2} )where UCIFILTER2is mapped to a querySELECT domain.GENERATE_UIC_FILTER($P{LoggedInUsername}, 'ADHOC_HPIP_PERSONNEL,ADHOC_N31_ROLE', 'RESERVE_REPORTING_UNIT_CODE') as RSVUIC_QUERY2 FROM DUALWhen I execute the report, I am receiving an error. I noticed that the where clause readsWHERE ([RESERVE_REPORTING_UNIT_CODE IN ('84251','8806N')])What did these brackets [] come from? Does Jaspersoft require the use of the $X{} format in the SQL statement? Note:I actually want that second parameter to be LoggedInUserRoles and a hard-coded list of roles. However, I am afraid that I would still get these brackets crash the sql statement. Link to comment Share on other sites More sharing options...
pawankumar.singh Posted November 19, 2019 Share Posted November 19, 2019 can you please post your query.As you $P,$X is the different format paramter used for different purpose.ThanksPawan Link to comment Share on other sites More sharing options...
NCHASHIM Posted November 20, 2019 Author Share Posted November 20, 2019 My first report queryselect (pi.last_name || ', ' || pi.first_name || ' ' || pi.mid_initials) name, trim(pi.STREET_ADDRESS || NVL2(pi.STREET_ADDRESS_2, ' ' || pi.STREET_ADDRESS_2,'')) Address, trim(pi.CITY_ADDRESS || NVL2(pi.STATE_ABBREVIATION,', ' || pi.STATE_ABBREVIATION,'') || nvl2(pi.zip_code, ' ' || substr(zip_code,1,5),'')) City_State_Zip, trim(ud.EMAIL_ADDRESS) Email_Address, pi.reserve_reporting_unit_code ruicFROM ows.personnel_info pi left join ows.user_data ud on ud.ssn = pi.ssnWHERE $X{IN, reserve_reporting_unit_code, RUIC_LIST} ORDER BY nameHowever, RUIC_LIST must generated dynamically. It will be different for every operator based on their role. This code SHOULD be the query that populates RUIC_LIST, but Jasperserver choked on the regexp_substr statementselect distinct uicf.uic FROM OWSADHOC.ADHOC_UIC_FILTER UICF INNER JOIN OWSADHOC.ADHOC_USER_ROLE UROLE ON UICF.ROLE_NAME = UROLE.ROLE_NAME inner join OWSADHOC.ADHOC_USER_DATA uname on uname.USER_ID = UROLE.USER_ID WHERE uname.JASPER_ACCOUNT = P{LoggedInUserName} and UROLE.ROLE_NAME in (SELECT NAME FROM (SELECT regexp_substr(LoggedInUserRoles, '(.*?)(,|$)', 1, LEVEL, null, 1) NAME, LEVEL LVL FROM DUAL CONNECT BY LEVEL <= regexp_count(LoggedInUserRoles, ',') + 1) ) Therefore, I created a function that would generate the entire WHERE clause in standard SQL format.I changed the report query toselect (pi.last_name || ', ' || pi.first_name || ' ' || pi.mid_initials) name, trim(pi.STREET_ADDRESS || NVL2(pi.STREET_ADDRESS_2, ' ' || pi.STREET_ADDRESS_2,'')) Address, trim(pi.CITY_ADDRESS || NVL2(pi.STATE_ABBREVIATION,', ' || pi.STATE_ABBREVIATION,'') || nvl2(pi.zip_code, ' ' || substr(zip_code,1,5),'')) City_State_Zip, trim(ud.EMAIL_ADDRESS) Email_Address, pi.reserve_reporting_unit_code ruicFROM ows.personnel_info pi left join ows.user_data ud on ud.ssn = pi.ssnWHERE $P!{UICFILTER) ORDER BY name The function definition isCREATE OR REPLACE FUNCTION OWSADHOC.GENERATE_UIC_FILTER (P_LoggedInUserName IN owsadhoc.adhoc_user_data.jasper_account%TYPE, P_LoggedInUserRoles IN VARCHAR2, P_FIELDNAME IN VARCHAR2) RETURN VARCHAR2 AS P_FILTER VARCHAR2(255);/*****************************************************************************************This procedures is used to generate the filter used on various adhoc jasper reports.Version 0.00Created by: Marian MooreDate: 21 Oct 2019Description: Initial Creation*****************************************************************************************/BEGINSELECT DISTINCT P_FIELDNAME ||' IN (' || listagg ( chr(39) || uic || chr(39) , ',') within group (order by uic) || ')' into P_FILTER from ( select distinct uicf.uic FROM OWSADHOC.ADHOC_UIC_FILTER UICF INNER JOIN OWSADHOC.ADHOC_USER_ROLE UROLE ON UICF.ROLE_NAME = UROLE.ROLE_NAME inner join OWSADHOC.ADHOC_USER_DATA uname on uname.USER_ID = UROLE.USER_ID WHERE uname.JASPER_ACCOUNT = P_LoggedInUserName and UROLE.ROLE_NAME in (SELECT NAME FROM (SELECT regexp_substr(P_LoggedInUserRoles, '(.*?)(,|$)', 1, LEVEL, null, 1) NAME, LEVEL LVL FROM DUAL CONNECT BY LEVEL <= regexp_count(P_LoggedInUserRoles, ',') + 1) ) ) ;RETURN P_FILTER; end GENERATE_UIC_FILTER;/If I test this (using hard-coded values)SELECT OWSADHOC.GENERATE_UIC_FILTER('MOORE_MARIAN', 'ADHOC_HPIP_PERSONNEL,ADHOC_N31_ROLE', 'RESERVE_REPORTING_UNIT_CODE') AS uicfilter FROM DUAL;/The function returns RESERVE_REPORTING_UNIT_CODE IN ('84251','8806N')Great! However, when I execute the new function via Jasper, the Jasper report is still crashing. When I look at the error, the clause is surrounded by brackets for some inexplicable reason. It reads[RESERVE_REPORTING_UNIT_CODE IN ('84251','8806N')] Link to comment Share on other sites More sharing options...
NCHASHIM Posted January 6, 2020 Author Share Posted January 6, 2020 Just to answer my own question. This is how the query ended up.SELECT OWSADHOC.GENERATE_UIC_FILTER($P{LoggedInUsername},$P{ROLE_LIST}, 'RESERVE_REPORTING_UNIT_CODE') as UICFILTER2 FROM DUAL The SQL in the report readsAND $P!{UICFILTER2} AND $P{ROLE_LIST} IS NOT NULLI had to define ROLE_LIST ASString.join(",", $P{LoggedInUserRoles}) 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