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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
Apr 29, 2011
i was given a task to find the second highest employee sal from emp table.
View 5 Replies
View Related
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
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
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
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
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
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
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
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
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
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
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
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
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
