Value set basics in Oracle Apps

Value set basics in Oracle Apps

| Print |

E-mail

An article on Value Sets, for beginners that follow http://getappstraining.blogspot.com Question: What is value set? Answer: It is a set of values Question: Why do we need value sets? Answer: You do not always want a user to enter junk free text into all the fields. Hence, Oracle Apps uses value set to validate that correct data is being entered in the fields in screen. Question: Is value set attached to all the fields that require validations? Answer : A big NO Question: Then where lies the usage of value sets? Answer: Broadly speaking, value sets are attached to segments in Flexfields. You can argue that value sets are also attached to parameters of concurrent program(but in reality oracle treats parameters as Descriptive Flexfields) Question: This is insane, flexfields haven’t been covered in http://getappstraining.blogspot.com as yet? Answer: Agreed, hence let’s restrict value set explanation to their usage in concurrent program parameters. Question: Any examples? Answer: For the namesake, lets add a Parameter to the concurrent program that we defined in “Concurrent Program Training Lesson link”. Lets add a parameter named “cost centre”, the values to this parameter must be restricted to one of the three values, i.e. HR, SEC, IT. At the time of submission of the concurrent program the user should be able to pick a cost centre from a list. This is where value set gets used. Lets now define a simple value set as described above. Step 1. Go to Application Developer, and select menu /Validation/Set Step 2. Now define a value set of type Independent. We will cover the other most widely used Type “Table” latter. Step 3. Now, lets add three independent values to the value set for this Cost Centre list. Hence click on menu Values within Validation Step 4. Here we add the values for IT, HR, SEC to this independent value set. “CONTROL-S” to save the data Step 5. Now let us go back to Concurrent Program that we created in earlier training lesson and Click on Parameters Step 6. Now lets create a parameter, and attach the value set that we created to this parameter. Step 7. Now to test this, lets go to receivables manager and click on Requests. Click on Request, Step 8. Submit New Request, and then click on OK. Step 9 Now, we can see the values defined in the value set here.

ORACLE SUBQUERY/CORRELATED QUERY EXAMPLES

A subquery is a SELECT statement which is used in another SELECT statement. Subqueries are very useful when you need to select rows from a table with a condition that depends on the data of the table itself. You can use the subquery in the SQL clauses including WHERE clause, HAVING clause, FROM clause etc.

The subquery can also be referred as nested SELECT, sub SELECT or inner SELECT. In general, the subquery executes first and its output is used in the main query or outer query.

Types of Sub queries:

There are two types of subqueries in oracle:

• Single Row Subqueries: The subquery returns only one row. Use single row comparison operators like =, > etc while doing comparisions.
• Multiple Row Subqueries: The subquery returns more than one row. Use multiple row comparison operators like IN, ANY, ALL in the comparisons.

SINGLE ROW SUBQUERY EXAMPLES

1. Write a query to find the salary of employees whose salary is greater than the salary of employee whose id is 100?

SELECT EMPLOYEE_ID,
 SALARY
FROM EMPLOYEES
WHERE SALARY >
    (
  SELECT SALARY
  FROM EMPLOYEES
  WHERE EMPLOYEED_ID = 100
  )

2. Write a query to find the employees who all are earning the highest salary?

SELECT EMPLOYEE_ID,
 SALARY
FROM EMPLOYEES
WHERE SALARY =
  (
  SELECT  MAX(SALARY)
  FROM EMPLOYEES
  )

3. Write a query to find the departments in which the least salary is greater than the highest salary in the department of id 200?

SELECT DEPARTMENT_ID,
 MIN(SALARY)
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID
HAVING MIN(SALARY) > 
  (
  SELECT MAX(SALARY)
  FROM EMPLOYEES
  WHERE DEPARTMENT_ID = 200
  )

MULTIPLE ROW SUBQUERY EXAMPLES

1. Write a query to find the employees whose salary is equal to the salary of at least one employee in department of id 300?

SELECT EMPLOYEE_ID,
 SALARY
FROM EMPLOYEES
WHERE SALARY IN
  (
  SELECT  SALARY
  FROM EMPLOYEES
  WHERE DEPARTMENT_ID = 300
  )

2. Write a query to find the employees whose salary is greater than at least on employee in department of id 500?

SELECT EMPLOYEE_ID,
 SALARY
FROM EMPLOYEES
WHERE SALARY > ANY
  (
  SELECT  SALARY
  FROM EMPLOYEES
  WHERE DEPARTMENT_ID = 500
  )

3. Write a query to find the employees whose salary is less than the salary of all employees in department of id 100?

SELECT EMPLOYEE_ID,
 SALARY
FROM EMPLOYEES
WHERE SALARY < ALL
  (
  SELECT  SALARY
  FROM EMPLOYEES
  WHERE DEPARTMENT_ID = 100
  )

4. Write a query to find the employees whose manager and department should match with the employee of id 20 or 30? 

SELECT EMPLOYEE_ID,
 MANAGER_ID,
 DEPARTMENT_ID
FROM EMPLOYEES
WHERE (MANAGER_ID,DEPARTMENT_ID) IN
  (
  SELECT MANAGER_ID,
   DEPARTMENT_ID
  FROM EMPLOYEES
  WHERE EMPLOYEE_ID IN (20,30)
  )

5. Write a query to get the department name of an employee?

SELECT EMPLOYEE_ID,
 DEPARTMENT_ID,
 (SELECT DEPARTMENT_NAME
 FROM DEPARTMENTS D
 WHERE D.DEPARTMENT_ID = E.DEPARTMENT_ID
 )
FROM EMPLOYEES E

CORRELATED SUBQUERIES EXAMPLES

Correlated sub query is used for row by row processing. The sub query is executed for each row of the main query.

1. Write a query to find the highest earning employee in each department? 

SELECT DEPARTMENT_ID,
 EMPLOYEE_ID,
 SALARY
FROM EMPLOYEES E_0
WHERE 1 = 
  (
  SELECT  COUNT(DISTINCT SALARY)
  FROM EMPLOYEES E_I
  WHERE E_O.DEPARTMENT_ID = E_I.DEPARTMENT_ID
  AND E_O.SALARY <=  E_I.SALARY
  )

2. Write a query to list the department names which have at lease one employee? 

SELECT DEPARTMENT_ID,
 DEPARTMENT_NAME
FROM DEPARTMENTS D
WHERE EXISTS
 (
 SELECT 1
 FROM EMPLOYEES E
 WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID)

3. Write a query to find the departments which do not have employees at all? 

SELECT DEPARTMENT_ID,
 DEPARTMENT_NAME
FROM DEPARTMENTS D
WHERE NOT EXISTS
 (
 SELECT  1
 FROM EMPLOYEES E
 WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID)