go through both the given queries...
SELECT * FROM EMP A WHERE 1=(SELECT COUNT(DISTINCT(SAL)) FROM EMP B WHERE B.SAL>A.SAL);
SELECT * FROM EMP WHERE SAL=(SELECT MIN(SAL) FROM (SELECT DISTINCT(SAL) FROM EMP ORDER BY SAL DESC) WHERE ROWNUM<=2);
both queries will fetch second highest sal from emp table.which sorting is used by oracle in order by and group by clause.
i was given a task to find the second highest employee sal from emp table.
View 5 Replies View Relatedour system has always been running on mysql database and recently we have switched to oracle. As the current system is coded using mysql query syntax, when i run this program using oracle database, i got a error. The language that I'm using is JSP.
this is the error message:
The following query could not run on oracle. To convert these mysql queries to oracle compatible queries.
SELECT productID,productName FROM products order by productName;
select newsID,newsDate,newsHeadLine1 from news order by newsDate Desc limit 3
SELECT fuji_products.productID, productName_Display FROM products,products_availability where products_availability.productID=products.productID and (product_status='enabled' or product_status='all') AND category='12'
SELECT catID, catSub1 from category where catSub = '"+ prodCat +"' AND catSub1 is not null group by catSub1 order by catSub1
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 have an table named as rawdata.I need output comes
F_DATE MAGNITUDE
--------- ----------
11-JAN-10 9
create table rawdata(f_date date,magnitude number);
select * from rawdata;
F_DATE MAGNITUDE
--------- ----------
10-JUN-09 6
10-DEC-09 6
11-JAN-10 7
11-JAN-10 8
11-JAN-10 9
I wonder how I can limit the queries in a table? This limitation would be that from a certain time, it allows queries. Before the opening hours, permitting no select.
Example: Allow SELECTs in table only after 16 p.m.
I would like to know the following.
If multiple queries are run in parallel(at the same time) against a table or a set of tables (query referencing multiple tables), does it reduce the performance.
In other words is Oracle capable of reading (selecting from) the same table multiple times in parllel.
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.
I have a column.
EMPNO
----------
E123
E256
E420
E1420
I need to get the highest value among these. how to get it?
What is the fastest way to fetch DISTINCT values from partitioned table?
1) DISTINCT/UNIQUE
2) GROUP BY
3) PARTITION BY OVER()
4) MAX(ROWID)
Table Definition
CREATE TABLE STG_SOS_SALES_FACT_STUDY
(
CNTRY_KEY NUMBER,
STUDY_ID NUMBER,
PRD_KEY_YEAR NUMBER,
PRD_KEY_WEEK NUMBER,
DATE_FROM DATE,
[Code]...
-> PARTITION BY RANGE (PRD_KEY_YEAR, PRD_KEY_WEEK)
-> SUBPARTITION BY LIST (CNTRY_KEY)
** Local Partition Indexes
1) CN_SD_CTG_PRD_PRDC_IDX = STG_SOS_SALES_FACT_STUDY (PRD_KEY_YEAR, PRD_KEY_WEEK, CNTRY_KEY, STUDY_ID, CTG_ID, PRDC_KEY)
2) CN_SD_PRD_STR_CTG_IDX = STG_SOS_SALES_FACT_STUDY (PRD_KEY_YEAR, PRD_KEY_WEEK, CNTRY_KEY, STUDY_ID, STR_KEY)#Query:
SELECT DISTINCT PRD_KEY_WEEK, PRD_KEY_YEAR
[Code]...
** Explain Plan:
Plan
SELECT STATEMENT ALL_ROWSCost: 6,235 Bytes: 629 Cardinality: 37
8 HASH UNIQUE Cost: 6,235 Bytes: 629 Cardinality: 37
7 CONCATENATION
3 PARTITION RANGE ITERATOR Cost: 1,985 Bytes: 1,031,900 Cardinality: 60,700 Partition #: 3 Partitions accessed #194 - #207
[Code]...
Partition #: 7 Partitions determined by Key ValuesThe above query is taking around 6-7 minutes to fetch the data.
How does the following underlined query works to find the nth highest salary
SELECT DISTINCT(A.SALARY) FROM EMPLOYEE A
WHERE &SALARY = (SELECT COUNT(DISTINCT(B.SALARY)) FROM EMPLOYEE B
WHERE A.SALARY<=B.SALARY);
My table holds info on documents:
|doc_id|auth|version|date_created|
so what I'm trying to do is select the latest version of each document record.
so say I have there records:
doc_uid|doc_id|auth|version|date_created|
|1 | 100 |al |0.1 |11/4/08 |
|1 | 101 |al |0.2 |11/4/08 |
|2 | 102 |al |0.4 |11/4/08 |
So i want to select only one copy of every doc_uid(unique id) with the highest version number, which would make my output:
doc_uid|doc_id|auth|version|date_created|
|1 | 101 |al |0.2 |11/4/08 |
|2 | 102 |al |0.4 |11/4/08 |
doc_id 100 was excluded because it has a higher version which was shown instead.
So how would i express this is oracle sql.
edit: Just thought i should say that I have been tring to crack this for 2 days now :P I have been messing around with MAX() but I cant make it do what i want.
I've a large table on which I applying aggregate and group by functions to the the results.
Here are two of the columns in my table:
Name ==== Score
John ==== 200*
Zohaib ==== 299
Ali ==== 0*
John ==== 200
Maria ==== 150*
Ali ==== 0
Maria ==== absent
John ==== absent
Here astrick (*) means with distinction....
The "score" column is a varchar column I want to run a query on this table to show the highest score for each student and the output should be like this:
Name ==== Score
Zohaib ==== 299
John ==== 200*
Maria ==== 150*
Ali ==== 0*
Important note:
1. if there is a tie between two highest scores like for a student, incase of john 200 was made twice, but the score with asterick (*) will be the "maximum" becuase it is with distinction so the output should also show the the highest score with asterick.
2. the output should show the the 2nd column (score) in desc order of highest score by each student...again incase of a tie, the one with astrick will come first in the result..
I know with just mere numbers, that is pretty easy but in this case it is a varchar column and also i need the astrick along with the highest score.
I want the simplest and shortest query if possible to achieve this result
I hope I've been able to clearly explain my requirment. I am using 10G.
how to find the nth highest salary
View 11 Replies View RelatedI was trying to update the AGENTS table with the data based on queries. But all the records updates with the same data. I can't understand why.
Write a PL script to populate these columns as follows:
TRAVEL_STATUS is one of three values:
GLOBETROTTER: agent has visited five or more different locations in the course of his missions
ROVER: agent has visited between one and four locations
SLOB: agent has been on no missions.
CONTACTS is the number of targets that share the agent's home location.
some of the tables and columns are
agents table: agent_id, location_id
targets table: target_id, location_id
missions_agents table: agent_id, mission_id
missions_targets: target_id, mission_id
missions table: mission_id, location_id
My code is
DECLARE
status AGENTS.TRAVEL_STATUS%TYPE;
contact AGENTS.CONTACTS%TYPE;
CURSOR loc_cur
IS
SELECT
a.AGENT_ID id,
[code].....
Following are 2 queries, which return same results as far as the input parameter is NOT NULL
select /*+ gather_plan_statistics */ * from PX_CJQ where decode(:x,null,1,object_id) = NVL(:x,object_id);
select /*+ gather_plan_statistics */ * from PX_CJQ where object_id = NVL(:x,object_id);
However the execution plan differs a lot The FTS cost or rows accessed count also varies what could be the reason? The PX_CJQ is simply select * from dba_objects and has index on object_id which anyway is not being used in this case
variable x number
exec :x:= 28
Query - 1
***************
***************
SQL> >select /*+ gather_plan_statistics */ * from PX_CJQ where decode(:x,null,1,object_id) = NVL(:x,object_id);
SQL> >select * from table(dbms_xplan.display_cursor(NULL,NULL,'ALLSTATS LAST'));
[code]...
Query - 2
***************
***************
SQL> >select /*+ gather_plan_statistics */ * from PX_CJQ where object_id = NVL(:x,object_id);
SQL> >select * from table(dbms_xplan.display_cursor(NULL,NULL,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
[code]...
29 rows selected.
SQL> >
I have three select queries. Each of them returns a single column. I want the result of these queries into a single table..
I tried this way..
select * from
(first select),(second select),(third select);
this gives duplicate rows...
i want to fetch the data in my form from table, by using item_code.
eg when i write the item_code its value get matched in same table and fetch the another
columns by item_code.
Lets say I have a table in ORACLE database like:
ACC_ID | ACC_AMT
111 | 10000
111 | 12000
111 | 14000
222 | 25000
222 | 30000
333 | 18000
333 | 27000
333 | 13000
333 | 15000
I want to get the output as:
ACC_ID_1 | ACC_AMT_1 | ACC_ID_2 | ACC_AMT_2 | ACC_ID_3 | ACC_AMT_3
111 | 10000 | 222 | 25000 | 333 | 18000
111 | 12000 | 222 | 30000 | 333 | 27000
111 | 14000 | null | null | 333 | 13000
null | null | null | null | 333 | 15000
I need each different ACC_ID with ACC_AMT in different columns. The table may have other different ACC_ID also, but I will fetch only what I need. What is the best way to do this?
So far I have tried this:
SELECT
(CASE WHEN ACC_ID=111 THEN ACC_ID END) AS ACC_ID_1,
(CASE WHEN ACC_ID=111 THEN ACC_AMT END) AS ACC_AMT_1,
(CASE WHEN ACC_ID=222 THEN ACC_ID END) AS ACC_ID_2,
(CASE WHEN ACC_ID=222 THEN ACC_AMT END) AS ACC_AMT_2,
(CASE WHEN ACC_ID=333 THEN ACC_ID END) AS ACC_ID_3,
(CASE WHEN ACC_ID=333 THEN ACC_AMT END) AS ACC_AMT_3
FROM <TABLE_NAME>
But I am not getting the desired result.
Is there any function avaialble in SQL that can return the highest common factor among a bunch of numbers. For example
10,20,25 have a highest common factor of 5.
join two oracle tables for fast fetch of data from table.
View 1 Replies View Related 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)
I am trying to update the security_level of a mission to the highest security level of missions of the same type.
Attributes of the missions table:
mission_id, code_name, mission_type_id, mission_date, security_level
The following is an intermediate output.
MISSION_ID MISSION_TYPE_ID SECURITY_LEVEL
318 3 6
329 3 2
286 5 6
521 5 3
281 6 4
396 7 3
331 8 4
14 9 4
230 9 0
486 10 2
The maximum output for each mission_type_id
MAX_LEVEL TYPE
6 3
6 5
4 6
3 7
4 8
4 9
2 10
According to this 3 records (329,521 and 230) should update.
But my code returns an error.
ERROR at line 4:
cannot update (......"SECURITY_LEVEL") to NULL
this is my code
UPDATE
AM_X_442_2 amx
SET
Amx.SECURITY_LEVEL =
(
select
max_level
[code].........
I have intended the query to make it legible but it removes all spaces.
I want to Bold the value with highest value in Numeric field. how can i do this.
View 12 Replies View RelatedHere is my problem, i need to create some files with my own format(let say 5000 records each) from a huge data table (May contain 5 Million records). And i want this creation to be multi threaded.
so how can i form queries efficiently to fetch records like 1..5000 and 5001..10000 and so on. I can form some thing like select * from table where rownum<5000 and not exists ( already fetched records) . but it is not the efficient one.