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.
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]....
the following proble.The emp table is having 14 records.
SELECT * FROM emp ORDER BY empno;
EMPNOENAMESALDEPTNO
7369SMITH80020
7499ALLEN160030
7521WARD125030
[code]...
The emp table is having 10 records.
SELECT * FROM emp_10 ORDER BY empno;
EMPNOENAMESALDEPTNO
7369SMITH80020
7499ALLEN160030
7521WARD125030
7566JONES200020
[code]...
I have written the following PL/SQL block logic tofetch the records from the emp table and compare the records with emp_10 table to perform insert if the records are newelse to perform update the existed records in the emp_10 table.
DECLARE
CURSOR tranche_balance_cur
IS
SELECT empno,
ename,
sal,
[code]...
Execution scenario 1:
I have commented insert and update statements in that case I got the following out put.
Inserted Records4
Updated Records10
As per the logic it's giving the correct output because the cursor is fetching 14 records in that already 10 records are existed in emp_10 tableand 4 are new records.so that it's showing the count for inserted records as 10 and updated records as 4.
Execution scenario 2:
I have uncommented insert and update statements in that case I got the following out put.
Inserted Records13
Updated Records1
As per the logic it's not giving the correct output.
I tried with using TRIM function in the comparision logic to avoid spaces.
TRIM(emp_10.empno) = TRIM(tranche_balance_rec.empno)
AND TRIM(emp_10.ename) = TRIM(tranche_balance_rec.ename)
AND TRIM(emp_10.sal) = TRIM(tranche_balance_rec.sal)
AND TRIM(emp_10.deptno) = TRIM(tranche_balance_rec.deptno)
/* 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.
I have created two tab pages.when i change tab then it should be display automatically all the records in that tab without pressing any.
View 1 Replies View Relatedcreate table test_exp (oid number, ioid number, status varchar2(20));
Below are the insert statements.
Insert into TEST_EXP (OID, IOID, STATUS) Values (1, 100, 'NEW');
Insert into TEST_EXP (OID, IOID, STATUS) Values (1, 101, 'DISCO');
Insert into TEST_EXP (OID, IOID, STATUS) Values (1, 102, 'CANCELLED');
Insert into TEST_EXP (OID, IOID, STATUS) Values (2, 103, 'NEW');
Insert into TEST_EXP (OID, IOID, STATUS) Values (3, 104, 'DISCO');
Insert into TEST_EXP (OID, IOID, STATUS) Values (4, 105, 'DISCO');
Insert into TEST_EXP (OID, IOID, STATUS) Values (4, 106, 'NEW');
Insert into TEST_EXP (OID, IOID, STATUS) Values (5, 107, 'NEW');
Insert into TEST_EXP (OID, IOID, STATUS) Values (5, 108, 'CANCELLED');
[code]...
Now my problem is we should fetch the data based on the below rules
If an OID contains 2 IOIDs for which there is a NEW and DISCO status attached, then fetch the 2 records
If an OID has only 1 of these status, then ignore the same
If an OID has none of the 2 status, then ignore the same.
The expected output will be like below:
OID|IOID|STATUS
1 |100 |NEW
1 |101 |DISCO
4 |105 |DISCO
4 |106 |NEW
I am using Oracle 11g
The procedure is compiled without any errors.
The select statement retrieves records well when executed at SQL promt.
But the same records are not displayed in this procedure by fetch from cursor.
The last message in the procedure 'completed' is also displayed.
create or replace procedure disp_rec(vid IN varchar2, vfeat IN number)
is
cursor c1 is select gid, listagg(vindex, ',') within group (order by tid) idx from ridrecords where idarpt=vid and feattype=vfeat group by gid;
type vg is table of number;
type vi is table of varchar2(3500);
[Code]....
I have a table with below syntax and insert statements.
-- Table creation
-----------------------
create table order_test (oid number,
orderno number,
ordername varchar2(100),
orderstatus varchar2(50),
orderseq number);
----------------------------------
-- Insert statements
[Code]....
The Output looks like below
OID|ORDERNO|ORDERNAME|ORDERSTATUS|ORDERSEQ
1 |100 |ORD1 |INPROGRESS |1
7 |100 |ORD1 |START |4
3 |100 |ORD1 |START |2
4 |100 |ORD1 |INPROGRESS |3
6 |101 |ORD2 |CANCELLED |3
2 |101 |ORD2 |INPROGRESS |2
5 |101 |ORD2 |COMPLETE |1
8 |102 |ORD3 |COMPLETE |1
9 |103 |ORD4 |CANCELLED |1
10 |103 |ORD4 |CANCELLED |2
Now, the below rules should be applied to get the output..
1) If status is same, then fetch max(seq) record
2) If status is different, then apply below priority to fetch the records
Start - Priority 1 (High)
In Progress - Priority 2
Cancelled - Priority 3
Complete - Priority 4 (Lowest)
Means, the output for each order should look like below.
OID|ORDERNO|ORDERNAME|ORDERSTATUS|ORDERSEQ
3 |100 |ORD1 |START |2
2 |101 |ORD2 |INPROGRESS |2
8 |102 |ORD3 |COMPLETE |1
10 |103 |ORD4 |CANCELLED |2
how to do it.
I have 3 tables, Emp(Emp_id,emp_name),dept(dept_no,dept_name),emp_dept(emp_id,dept_no). Emp tabl ehas some 20 employes id who belongs to different departments.There are few employee who belongs to multiple departments as well. I want to fetch records of emp_id, emp_name, dept_no in the following format.
Name id dept_no
Ram 101 10
20
30
Ani 201 10
20
I have a big database, I want to retrieve its records in several tries; first I use "select * from dbname where rownum <1000" but for 2'th try how can I get next 1000 records but not previous records?
View 1 Replies View RelatedI got a issue with a query to fetch records between two dates for fixed timings
Date
From 29-09-2011 to 04-10-2011
Time
From 00:00:00hrs to 08:00:00hrs
I tried the below queries, it doesnt work
select a.detectorid,sum(b.totalvolume),a.updatetime,a.averagespeed from traffic_data a left outer join volume_data b on a.traffic_id=b.traffic_data_id
where pollinterval=1 and detectorid=�AIDC_0154� and updatetime between to_date(�29-aug-2011:00:00:00�,�DD-MON-YYYY:HH24:MI:SS�)
[code]...
I need to fech parent records only when no child record with status 'N' exists. There are only two possible values for status column of child table 'Y' / 'N'.
Below are table structures and insert statements for data.
CREATE TABLE MASTER
(
COL1 NUMBER,
[Code]....
COMMIT;Query I framed is below
select * from master where exists (select null from child where child.col2 = master.col1
group by child.col2 having count(distinct col3) =1 )
Output in above case would be 3 as for 1 there's one record with status as 'N' and for 2 there's no child record. I am on 10g.
I have made a travel booking system which comprises of 3 forms
1)Travel Booking form
2)Reservation Form
3)Cancellation Form
Under one booking number i can add multiple users in which they can have there multiple travels.
Users can cancel there individual travels under a prescribe booking number which on doing the Cancel flag turns to 'Y'.
What i want is, If a user is cancelling his/her travel under any booking number then while retriving the records in Travel Booking form, the travels which are cancelled should not be in enable mode.
For one user there can be 4 travels out of which 2 are cancelled, how can i track only those records whoes cancel flag is set to Y. some logic to find it out. Else can i use :system.cursor_record. If yes, How to use it for this system.
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.
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
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
i was given a task to find the second highest employee sal from emp table.
View 5 Replies View RelatedIn 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 RelatedI 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.
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]....
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
display the total number of employee working under president in emp table
View 5 Replies View RelatedI 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
I am having two tables
Table 1 having 16 cror rows .
Table 2 having 1000 rows
I joined both the tables and fetch all inforamtion from big table for those key present in small table.Join query taking more time to fetch the rows .
FRM-40501: ORACLE error: unable to reserve record for update or delete.
ORA-24374: define not done before fetch or execute and fetch
My master-detail form has single canvas. For both blocks, master and detail, two tables joined together in each. One table to be updated, second table has some info for reference (query only).
I am getting these errors when in detail block the item from LOV is selected for existing record. This does not happen for new record inserted in detail block.
create or replace PROCEDURE newprocedur(outname OUT VARCHAR2,outroll OUT NUMBER) AS
CURSOR c1 IS
select Name,Rollno,Section from emp;
BEGIN
Open c1;
fetch c1 into outname,outroll;
Here out of 3 columns in cursor is it possible to fetch only two columns using FETCH like i did above?
I want to write a SELECT query on the data which are collected in a PLSQL table which is having 4 columns.
Looping through all the records in the PLSQL table will not get my requirement. Because I need to group the data based on two columns and need to fetch the count of groups.
Is there any way to query that PLSQL table?
how to get name of coloumn and table name which is being modified in trigger
View 8 Replies View RelatedOS : widows 2003
Oracle 9i release 2
I need to Fetch 1 lakh rows from table (all rows) in fastest way.
How to do it?
I am trying to update records in the target table based on the records coming in from source. For instance, if the incoming record is present in the target table I would update them in the target else I would simply insert. I have over one million records in my source while my target has 46 million records. The target table is partitioned based on calendar key. I implement this whole logic using Informatica. Looking at the informatica session log I find that the informatica code is perfectly fine but its in the update part it takes long time (more than 5 days to update one million records). find the TARGET TABLE query and the UPDATE query as below.
TARGET TABLE:
CREATE TABLE OPERATIONS.DENIAL_REGRET_FACT
(
CALENDAR_KEY INTEGER NOT NULL,
DAY_TIME_KEY INTEGER NOT NULL,
SITE_KEY NUMBER NOT NULL,
RESERVATION_AGENT_KEY INTEGER NOT NULL,
LOSS_CODE VARCHAR2(30) NOT NULL,
PROP_ID VARCHAR2(5) NOT NULL,
[code].....