SQL & PL/SQL :: Trying To Get Employee Hire Anniversary

Oct 18, 2012

I'm trying to get employee hire anniversary between 2 days ago and within 7 days from today..It's working fine until I get to January system month (then mm=01, which is less then mm=12)

select employee_id, first_name, last_name, job_id, hire_date,
case
when (to_number(to_char(hire_date,'mmdd'),'9999')) = (to_number(to_char((sysdate-2), 'mmdd'), '9999')) then 'Day before yesterday'
when (to_number(to_char(hire_date,'mmdd'),'9999')) = (to_number(to_char((sysdate-1), 'mmdd'), '9999')) then 'Yesterday'
when (to_number(to_char(hire_date,'mmdd'),'9999')) = (to_number(to_char((sysdate), 'mmdd'), '9999')) then 'Today'
when (to_number(to_char(hire_date,'mmdd'),'9999')) = (to_number(to_char((sysdate+1), 'mmdd'), '9999')) then 'Tomorrow'
[code]....

View 13 Replies


ADVERTISEMENT

SQL & PL/SQL :: Print Ename And Salary Of Employee Using Employee Number As Input

Aug 11, 2013

/* Formatted on 2013/08/11 18:46 (Formatter Plus v4.8.8) */
CREATE PROCEDURE p_get_name (
p_empno IN OUT NUMBER,
p_name OUT VARCHAR2,
p_err OUT NUMBER
[code].......

Note:- I want to print ename and salary of emp using empno as a input but i dont want to declare extra variable for salary , i want to print salary using empno but when i execute this procedure. It gives value of empno in salary. Don't Know Why , how can i print salary of emp using empno as input without declaring extra variable for salary.

View 25 Replies View Related

SQL & PL/SQL :: Employee Table To Assign Grade For Each Employee Based On His Salary

Feb 9, 2012

based on the following information

grade lowsal highsal
------ ----- ------
1 700 1200
2 1201 1400
3 1401 2000
4 2001 3000
5 3001 9999

for the employee table to assign grade for each employee based on his salary the following plsql procedure is giving error:

-----------------------------------------------------------
CREATE OR REPLACE PROCEDURE GRADE(EID IN NUMBER,BONUS OUT NUMBER) IS

vGRADE NUMBER(8,2);
vSAL NUMBER(8,2);

BEGIN
vGRADE=1
SELECT SAL INTO vSAL FROM EMP WHERE EMPNO=EMPID;
IF vSAL<= 700 THEN
vGRADE:=1;
ELSEIF vSAL<= 1201 THEN
[code]....

View 4 Replies View Related

SQL & PL/SQL :: How To Retrieve Each Employee Age From Employee DB

Nov 25, 2011

I have a table like below,I want retrieve each employee age ,from db column

SQL> select * from dob;

ENAME DB
---------- ---------
vishal 12-MAR-90
jeya 30-MAR-73
vasanthi 17-APR-80
mangai 25-NOV-81
poorna 18-AUG-80
vinod 20-AUG-81
nanthini 01-JUN-86
karthick 20-MAR-88

View 4 Replies View Related

SQL & PL/SQL :: Count Of Employee Names

Oct 5, 2011

this is my code

DECLARE
V1 VARCHAR2(100);
V2 VARCHAR2(100);
V3 VARCHAR2(200);
BEGIN
[code]......

if i want to dynamically enter the three to four names then i want the count of those names .

View 5 Replies View Related

SQL & PL/SQL :: Give New ID To Existing Employee?

Mar 13, 2012

want to create a PL/SQL procedure, update_id(id_emp in number), that gives a new id_emp (id_emp=y) to an existing employee (id_emp=x).So before updating the EMP table, we have to :

1- create a new row on EMP(with id_emp=y) that has the same informations of the employee (id_emp=x),
2- update all tables that contains the id_emp column (update <TAB> set id_emp=y where id_emp=x),
3- delete employee (id_emp=x).

The problem is in step 2 : it creates a lot of locks and makes the DB unusable.To deal with this problem, I thought for many solutions, but the problem is how to implement them correctly and efficiently.

Before executing step 2, we have to ensure (through a marker I guess) that all the tables that have the id_emp column, are managed by our session, and any other acces (through SELECT, UPDATE, DELETE, INSERT statment) from another session will be rejected since we have a marker on that table.

When step 2 ends, we release all the tables from the markers.

My simple question : how to achieve this ?

View 12 Replies View Related

SQL & PL/SQL :: How To Find Name Of Oldest Employee

Dec 21, 2010

I have done it through SUBQUERY AND MIN FUNCTION

SQL> SELECT LAST_NAME ,HIRE_DATE FROM EMPLOYEES
WHERE HIRE_DATE = (SELECT MIN(HIRE_DATE) FROM EMPLOYEES) ;

but i want a smaller, simple code .

View 7 Replies View Related

SQL & PL/SQL :: Select Employee According To Days

Jun 8, 2010

i have an employee table where there is a column called join date. Now i have to select the employee according to the days. Means how many employee joined on Monday/Tuesday etc.

View 17 Replies View Related

SQL & PL/SQL :: Update Of Employee Table

May 28, 2011

I have to implement a functionality in my application. I have an employee table and each employee does a transaction which is stored in a transaction table.

The functionality that I have to implement is that if an employee does not perform any transaction for a period of 2 years then updated the employee and set him inactive.

View 3 Replies View Related

SQL & PL/SQL :: Calculate Sanctions To Employee

Aug 1, 2012

i have two tables

first table
employees
emp_no pk
emp_name
emp_salary
emp_hiredate
emp_birthdate
-------------------
second table
sanctions
sanc_id
sanc_name
emp_no fk

i need the following

if employees take sanctions and his salary 1000 and sanctions value 50 then the net salary is 950 . I need if the same employee take another sanctions values 50
the result will be
the old net salary is 950
and the new old salary is 900

View 17 Replies View Related

SQL & PL/SQL :: Update In Employee Table?

Apr 4, 2010

Employee Table
==============
create table empoyee (
empno number,
sal number)
insert into employee(empno,sal) values (1, 200);

[code]...

If we make any update in Employee table for his/her salary, before update, that record should be inserted into EmployeeHist table and history will continue to build. Employee Table should have only current salary.If we change sal for emplyee # 1 from 200 to 800 then original current record in employee table will be inserted into employeehist table like

empsno = 1
empno =1
sal=200
last_update=sysdate

View 10 Replies View Related

Forms :: Populate Only Particular Employee LOV

Apr 1, 2013

I want to populate only particular employee LOV ,when i am press "Populate LOV"..Button and i attached Form for testing and It is a 10g version..

View 5 Replies View Related

Business Rules For Employee Table

Aug 19, 2013

We need some transformation rules on Source employee table which comes from SAP and want to load in Target table.Basic idea is we need to calculate time when position was not held by sub owner so . . . Etc

Source looks like:

POSITION_TCD START_DT END_DT SUBTYPE POSI_HOLDER

Example 1:

10005822 10/16/2003 11/20/2008 A008 105404
10005822 10/16/2003 3/31/2009 A999 105404
10005822 6/23/2008 7/5/2009 A008 124530
10005822 11/21/2008 8/31/2009 A008 105404
10005822 8/31/2009 4/16/2010 A008 105323
10005822 4/19/2010 12/31/9999 A999 131995
10005822 7/5/2010 12/31/9999 A008 131995

Example 2:

10084408 5/3/2010 12/31/9999 A008 130591
10084408 3/21/2011 5/17/2011 A008 132725
10084408 5/3/2010 1/2/2011 A999 130591

After business rules data should look like:

Target

Example 1:

POSITION_TCD POSI_HOLDER SUBS_OWNER EMP_ASSI_TYPE START_DT END_DT

10005822 105404 105404 Hold=Subs 10/16/2003 11/20/2008
10005822 124530 105404 Hold<>Subs 6/23/2008 3/31/2009
10005822 124530 No Substantive No Substan 4/1/2009 7/5/2009
10005822 105404 No Substantive No Substan 11/21/2008 8/31/2009
10005822 105323 No Substantive No Substan 8/31/2009 4/16/2010
10005822 No Holder 131995 No Holder 4/19/2010 7/4/2010
10005822 131995 131995 Hold=Subs 7/5/2010 12/31/9999

Example 2:

10084408 130591 130591 Hold=Subs 5/3/2010 1/2/2011
10084408 130591 No Substantive No Substan 1/3/2011 12/31/9999
10084408 132725 No Substantive No Substan 3/21/2011 5/17/2011

View 1 Replies View Related

All Employee Types In Single Query

Jul 7, 2011

I have four employee types such as 'C' for consultants, 'S' for staffs, 'W' for workers ,, ('E','S','W') for all types of employees. i have write four queries for showing four types of employees. can it is possible in a single query.

I have written this in oracle forms . I have taken a list item for workers i have taken 'W' , for staffs i have taken 'S' , for consultants i have taken 'C' and for all i have taken 'A'. my column name is emp_type in( :block.list_item ) but it is not taking the value from the list item when the value is emp_type in('C') etc etc..

like this for workers ,staffs . when it is 'A' it will take emp_type in ('E','S','W')

View 3 Replies View Related

Forms :: Display Employee Details?

Feb 25, 2013

i want to display employees details when i am passing Deptno and that department employees only display in Oracle Forms

View 16 Replies View Related

SQL & PL/SQL :: Query - Get Details Of Each Employee In Single Row?

Jun 7, 2012

I have three tables as shown in the image. Need to get the details of each employee in a single row..

EMployee_id BaseSalary Bonus Hike shares

View 4 Replies View Related

SQL & PL/SQL :: How To Get 2nd Highest Salary From Employee Table

Apr 29, 2011

i was given a task to find the second highest employee sal from emp table.

View 5 Replies View Related

Forms :: Update Particular Employee Name By Using Cursor

Jul 14, 2013

I would like to update the particular employee name by using the cursor ..For that I would like to show the steps which I did . as in the below screen it updated John but it removed the other ename ...I like to update John only and remaining ename should be as it ..

The code which I have written on KEY-COMMIT is below.

View 1 Replies View Related

PL/SQL :: Fetch Records From Employee Table

Oct 28, 2012

I am having one table Employee. Employee table having 50 records. I want to fetch 5 records every timeone the query is executed. But it should be like below.

1-5 records
6-10 records
11-15 records
16-20 records
46-50 records

Any one can give the query.

View 5 Replies View Related

SQL & PL/SQL :: Query To Find Is Employee A Manager

Feb 26, 2013

Refer the table below and provide me the query to find given employee is manager or not.

Refer the table below, emp#100 is manager, emp#300 is not a manager.

<emp> <emp_name> < manager >
100Smith null
200Ram 500
300Shankar 500
400Madhur 200
500Suman 100
600Sundar 100

View 2 Replies View Related

PL/SQL :: Employee Table - Find Characters

Sep 24, 2013

In the Employee table i want to display having 4 characters of Emplo_name ,that name's 3rd character must be 'R' How to get it .

View 9 Replies View Related

SQL & PL/SQL :: Identify Department VP In Employee Interface Table

Mar 12, 2010

I have employee interface table something like this.

emp_idemp_name Job_title supervisor_name
1AJ Engineer BJ
2CK Analyst ND
3BJ Manager TR
5TR VP IT JD
6ND S Manager MD
7MD VP Telecom SK
8SK VP Eng JR

I want to idenitfy the VP for each employee. The logic I have to apply is check for hte supervisor of each employee to see if the supervisor has designation starting with 'VP'. If no, I have check the supervisor of the supervisor and so on. I tried using a recursive query using connect_by_root but in the above example for employee ND it lists the VP as both MD and SK. I need it to show on MD who is the lower in the hierarchy.

I am a Java person but since my app uses the Oracle DB I am to do this task.

View 13 Replies View Related

Forms :: Create A Report For Absent Employee?

Oct 18, 2012

I have a requirement that I need to create a report for absent employee, we have "Hand Punch" scanning system for attendance, when a employee scan his hand it put a record with the employee id, date, shift, & time.

Now I need to create a report for absent employees in date range

View 2 Replies View Related

PL/SQL :: Procedure To Search Employee By Passing First / Middle Or Last Name

Jun 5, 2013

sort out the error of this procedure to search an employee by passing employee fname or middle or last to get his name .

CREATE OR REPLACE PROCEDURE "WEB_MASTER"."SEARCH_EMPLOYEE"
(empname IN VARCHAR2) is
cur number;
v_emp_name VARCHAR2 (100);
r number;
begin
[code]....

View 6 Replies View Related

PL/SQL :: How To Query A Hierarchy Structure (Manager-Employee)

Apr 29, 2013

I am using Oracle database 10g R2.

suppose I have this structure

EMP_ID               EMP_NAME          MANAGER_ID
1                    KING               
2                    STEVE               1
3                    FRANK               2

What I want is when I query for any EMP_ID, I want to get all his manager first, and the last record is the employee in leaf of the hierarchy in the query.

For example, when I query for EMP_ID=2 ,I want to get this result

EMP_ID               EMP_NAME          MANAGER_ID
1                    KING               
2                    STEVE               1

and when I query for EMP_ID=3 ,I want to get this result:

EMP_ID               EMP_NAME          MANAGER_ID
1                    KING               
2                    STEVE               1
3                    FRANK               2

How to write this query?

View 3 Replies View Related

Return Results Where Employee ID That Has 2 Different Timezone IDs On Same Date

Jun 21, 2012

I have a table with the following columns

EMPLOYEEIDNUMBER(12,0)
PUNCHDTM DATE
TIMEZONEIDNUMBER(12,0)

I want to return any results where any employee id that has 2 different timezoneid's on the same date. I would actually like, if its possible, to select these entries to display on one row per employee per day. So for example

EMPLOYEEID - PUNCHDTM - TIMEZONEID - PUNCHDTM - TIMEZONEID
12345 - 6/20/2012 5:00 am - 123 - 6/20/2012 10:00am - 456

To me who is newer with SQL this sounds like i would be 'joining' the table to itself so i've searched for that but not found what i need.

View 3 Replies View Related

Forms :: Sending Employee PayRoll Slip

Jan 21, 2012

I am looking to send all employees in the company their payroll slip directly to their email IDs, so I need to built a producer can :

1- Generate their slips and save it into PDF files with each employee number .
2- send an email message to each employee with his slip.

View 2 Replies View Related

Create Trigger So That Whenever Record In Employee Table Deleted?

Dec 7, 2009

I'm trying to create a trigger so that whenever a record in the Employee table is deleted, a trigger will automatically delete corresponding records in the Job History table, then the Employee record is archived to EmployeeArchive before it is deleted. It compiles but with warnings. Here's what I've got.

CREATE TABLE EmployeeArchive
(EmployeeID Int, FirstName Char, LastName Char,
EMail Char, PhoneNumber Int, HireDate Date, JobID Char, Salary Int,
Commission Int, ManagerID Int, DepartmentID Char);

[Code]....

View 11 Replies View Related

Forms :: Show Manager / Employee And Department On The Tree

Nov 1, 2012

i have 3 tables

table employee
employee_no
employee_name

table manager
manager_id
manager_name

table department
dept_id
dept_name

how can show on the tree all manager with employee with department

example
manager ali
employee Mohamed
department marketing

View 6 Replies View Related

SQL & PL/SQL :: Employee Table In Scott Schema Created By Default

Oct 3, 2011

I am using Oracle 11g Release 11.2.0.1.0

OS: Windows

I am using the Employee table in Scott schema which created by default.

10,20,30,40,50 are the department ids.

I would like to have the output like below. I am having lots of values in dep id and lots of employees in each dept ids

Emp name Dep ID
John 20
Mike 40
Ram 10
Guru 50
Kumar 30

View 7 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved