Oracle SQL Bind Variables BETWEEN Dates In BI Publisher

Ben E.

Hello, I have an SQL query to return a list of terminated employees that I’m trying to use in BI Publisher. The goal is to prepare a report where the user can enter a date range. In other words, give me a list of all employees with termination dates ranging from ABC to XYZ. Here is the script I’m entering in the Data Model:

SELECT DISTINCT

ORG2.NAME "Busn Unit",

'CNTY'||LPAD(PER.PERSON_NUMBER, 5, 0) "Emp ID",

EMP.FIRST_NAME "First Name",

EMP.MIDDLE_NAMES "Middle",

EMP.LAST_NAME "Last Name",

NAME.KNOWN_AS "Nick Name",

ASSIGN.ASSIGNMENT_NAME "Job Title",

ORG1.NAME "Department",

TO_CHAR(PERIOD.DATE_START,'fmMM/DD/YYYY') "Start Date",

TO_CHAR(EMP.ACTUAL_TERMINATION_DATE,'fmMM/DD/YYYY') "Termination Date",

TYPE.USER_PERSON_TYPE "Person Type",

ASSIGN.ACTION_CODE "Action Code",

LOC.LOCATION_NAME "Work Location",

LOC.ADDRESS_LINE_1 "Work Address Line 1",

LOC.ADDRESS_LINE_2 "Work Address Line 2",

LOC.TOWN_OR_CITY "City",

LOC.REGION_2 "State",

LOC.POSTAL_CODE "Zip Code",

LOC.REGION_1 "County",

LOC.COUNTRY "Country",

PERIOD.COMMENTS "Separation Notes"

FROM

PER_EMPLOYEES_X EMP,

PER_ALL_ASSIGNMENTS_M ASSIGN,

HR_ALL_ORGANIZATION_UNITS ORG1,

HR_ALL_ORGANIZATION_UNITS ORG2,

PER_PERIODS_OF_SERVICE PERIOD,

HR_LOCATIONS_ALL LOC,

PER_ALL_PEOPLE_F PER,

PER_PERSON_NAMES_F NAME,

PER_PERSON_TYPES_TL TYPE

WHERE

EMP.ASSIGNMENT_ID=ASSIGN.ASSIGNMENT_ID AND

TRUNC(SYSDATE) BETWEEN ASSIGN.EFFECTIVE_START_DATE AND ASSIGN.EFFECTIVE_END_DATE AND

TRUNC(SYSDATE) BETWEEN PER.EFFECTIVE_START_DATE AND PER.EFFECTIVE_END_DATE AND

TRUNC(SYSDATE) BETWEEN NAME.EFFECTIVE_START_DATE AND NAME.EFFECTIVE_END_DATE AND

ASSIGN.ORGANIZATION_ID=ORG1.ORGANIZATION_ID AND

ASSIGN.BUSINESS_UNIT_ID=ORG2.ORGANIZATION_ID AND

ASSIGN.PERIOD_OF_SERVICE_ID=PERIOD.PERIOD_OF_SERVICE_ID AND

ASSIGN.LOCATION_ID=LOC.LOCATION_ID AND

EMP.PERSON_ID=PER.PERSON_ID AND

EMP.PERSON_ID=NAME.PERSON_ID AND

NAME.NAME_TYPE='GLOBAL' AND

EMP.ACTUAL_TERMINATION_DATE IS NOT NULL AND

TYPE.PERSON_TYPE_ID=ASSIGN.PERSON_TYPE_ID AND

EMP.ACTUAL_TERMINATION_DATE BETWEEN TO_DATE(:"From") AND TO_DATE(:"To") AND

ORG2.NAME='BOCC-BU' -- BOCC-BU, CLRK-BU, HNBC-BU

ORDER BY ORG2.NAME, EMP.LAST_NAME, EMP.FIRST_NAME, "Emp ID", "Start Date"

I’m using bind variables with the colon : to prompt for the date range. When I try to save this in the Data Model, I get an error that says “Failed to load SQL.”. Does anyone know of a solution? Thanks.

MandeepGupta

Hi @User_WZDVD,

Please change

EMP.ACTUAL_TERMINATION_DATE BETWEEN TO_DATE(:"From") AND TO_DATE(:"To")

To

EMP.ACTUAL_TERMINATION_DATE BETWEEN TO_DATE(:FromDt) AND TO_DATE(:ToDt)

Thanks.

Ben E.

Now getting different error messages with that, but found another solution:

EMP.ACTUAL_TERMINATION_DATE BETWEEN :pFrom AND :pTo

Apparently, you must type a letter p (of all things) after the colon : for some reason to make it work in BI Publisher.

Raghavan p-Oracle

Hi Ben,

Check if you have created params with the name pFrom and pTo in Datamodel ->Parameters section.

Regards,

Raghavan P