SQL Command To Prompt User To Select From A Pre-Defined Drop-Down List

Ben E.

Is there a way to prompt the user running your SQL query to pick from a drop-down list of pre-defined values? For example, I want the user to select one of 3 possible values for the Business Unit: BOCC-BU, CLRK-BU, or HNBC-BU. I know how to use a Bind Variable with a colon :, but am not sure how to tell it to give a drop-down list of only 3 choices. This is the query which I’m running in SQLConnect on the Oracle Fusion tables:

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 '2020-01-01' AND '2023-03-24' AND

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

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

Raghavan p-Oracle

Hi Ben,

Have you tried 'rownum' in the where clause?

ex: select * from emp where rownum<4;

Regards,

Raghavan P