This is what I've been trying to do.
I have the following table:
FOOTBALL PLAYERS < ID, NAME, ATTRIBUTE>
100-JIM-TALL
101-BOB-STRONG
102-MARK-SMART
...etc
I want to form a query that regardless of the total returned records, I will be able to specify how many of each "kind" of players I want returned. There are several good reasons that it has to be one query and not many.
In sql plus How do I get the number of distinct rows of a certain value?
for example
select group_number from records group by group_number
How would I query for the total number of group_numbers in this query?
I have a column COL1 in table TAB1 which is varchar2. I want select only rows which has number and not alphanumeric value? I don't want to use regexp for this since
View 11 Replies View RelatedThe query below returns 101 rows. If I replace the column list with an asterik the query returns 892 rows. I do not understand why.
--select *
select Ref_Consultant_CD
,Resident_CD
,ID
,Ref_Facility_CD
[code]......
I am facing a problem in bulk insert using SELECT statement.My sql statement is like below.
strQuery :='INSERT INTO TAB3
(SELECT t1.c1,t2.c2
FROM TAB1 t1, TAB2 t2
WHERE t1.c1 = t2.c1
AND t1.c3 between 10 and 15 AND)' ....... some other conditions.
EXECUTE IMMEDIATE strQuery...These SQL statements are inside a procedure. And this procedure is called from C#.The number of rows returned by the "SELECT" query is 70.
On the very first time call of this procedure, the number rows inserted using strQuery is 70. But in the next time call (in the same transaction) of the procedure, the number rows inserted is only 50.And further if we are repeating calling this procedure, it will insert sometimes 70 or 50 etc. It is showing some inconsistency.On my initial analysis it is found that, the default optimizer is "ALL_ROWS". When i changed the optimizer mode to "rule", this issue is not coming.I am using Oracle 10g R2 version.
I am importing some data from Oracle into another database on a regular basis. It works fine for most of the queries but couple of queries don't work sometimes (random). I don't get any errors or any data.
We switched on the Oracle auditing to find out the queries being sent to oracle db. We can see all the queries in the Audit log. Is it possible to configure Auditing to get the "Number of Rows" returned by Select statements so that we can be sure that some data was returned.
I'd like to know if it is possible to track DML actions issued on a specific table by a specific user, for example , i tried :
AUDIT SELECT on SCOTT.DEPT by HR by ACCESS;
I get an error, where is my syntax error ?
i want to know if it's possible to do it without trigger ?
I need to get the number of rows based on one condition for certain time period. For example, writing a query for 3(any) hrs , i need to get the output as count of rows as hourly basis. Like these:
Date Time Count
12/08/2010 16:00 234
12/08/2010 17:00 345
12/08/2010 18:00 645
I want my query to return the rows of the table where a column contains a specific values first in a certain order, and then return the rest of the rows alphabetized.
For Example:
Country
ALBANIA
ARGENTINA
AUSTRALIA
....
CANADA
....
USA
....
Now i want USA and CANADA on top in that order and then other in alphabetized order.
We are interested to find the number of connections from specific client. Is tracing on sqlnet.ora in the client machine the answer? If yes, which trace has the information?
View 1 Replies View RelatedI want to convert a number to this format using below query but i'm getting error. how to correct the below query.
SELECT TO_CHAR(12345678, '99G9999D99') FROM dual;
error:-###########
There are four columns as follows, but I need to delete those rows from the third column only where the second letter of the word appears as vowel. For example, I want to delete the rows having the words, 'Ramu' and 'Ravi' only.
A B C D
xx y Ramu xx
yu ut Ameer uui
rtt iw Ravi iwoow
fgsg isd Intel jjuiw
If i have a CSV file that is in the following format
"fd!","sdf","dsfds","dsfd",
"fd!","asdf","dsfds","dsfd",
"fd","sdf","rdsfds","dsfd",
"fdd!","sdf","dsfds","fdsfd",
"fd!","sdf","dsfds","dsfd",
"fd","sdf","tdsfds","dsfd",
"fd!","sdf","dsfds","dsfd",
Is it possible to exclude any row where the first column has an exclamation mark at the end of the string.
i.e. it should only load the following rows
"fd","sdf","rdsfds","dsfd",
"fd","sdf","tdsfds","dsfd",
I am using Oracle 10.2
I have a table that has hierarchical data within it. I need to select this data in a specific way, showing the hierarchy.
E.g. Data in table (Key is unique)
Lvl KeyParKey HasChild
1k101
1k200
1k301
1k401
2k34k10
2k22k11
2k24k10
2k13k30
2k52k30
2k35k30
2k13k30
2k11k40
3k56k220
3k109k221
3k67k220
4k61 k1090
Etc etc....
That�s generally the format the values would appear in the table if I just did a standard select. I want it displayed in a more hierarchical Parent � child way.
The format I need to get out is as follows:
Lvl KeyParKey hasCh
1k101
2k34k10
2k22k11
3k56k220
3k109k221
4k61 k1090
3k67k220
2k24k10
1k200
1k301
2k13k30
2k52k30
2k35k30
2k13k30
1k401
2k11k40
How to select a substring in oracle up to a more than one specific characterfor ex : 121.051^NP: FAMILY PRACTICE ( trim the values before ^ ) 121.051^*NP: FAMILY PRACTICE (trim the value before *). with below function I can only get rid of ^ , I want both the specific characters ^ and ^* to be removed at the same time.
SUBSTR(p.phys_sub_grp_2_desc,INSTR(p.phys_sub_grp_2_desc, '^') +1)
I'm using Oracle 9i Enterprise edition, Is there a select statement to view transaction log for specific date?
View 1 Replies View Relatedwhen we ran SELECT statement against CUBE, we got below wait event: resmgr: cpu quantum.Further, we checked below 2 parameters :
NAME VALUE
------------------------- --------------------------------------------------
resource_limit TRUE
resource_manager_plan SCHEDULER[0x12B943C]:DEFAULT_MAINTENANCE_PLAN
[code]....
It has been found that these sessions did not get enough CPU to process the request. how to find out how many CPU has been allocated to this database ?
> uname -a
Linux dukedmts03db02.corp.cox.com 2.6.18-128.1.16.0.1.el5 #1 SMP Tue Jun 30 16:48:30 EDT 2009 x86_64 x86_64 x86_64 GNU/Linux
how to analyze how much % of CPU being utilized for a specific session ?
I have a database which consists of various orders and various field.
I have a variable called createddatetime . I want that whenever i should run the database it should display records from
Yesterday 06:00:00 am to Current Date 05:59:59 am
Now to implement this i tried to put this syntax
and to_char(Createddatetime,'dd/mm/yyyy HH24:mi:ss') between 'sysdate-1 06:00:00' and 'sysdate 05:59:59'
But nothing comes up
where as definitely there are records between times because when i do and Createddatetime between sysdate-1 and sysdate I see valid records coming up.
in my sql we can use limit to select first 4 rows in the table then next 4 rows ,can oracle do that ?
I have fifty rows inside the table
select * from student where rownum between 0 and 4 order by stuid ->it work
select * from student where rownum between 5 and 9 order by stuid -> did not work
what is the correct way to do it ?
I am trying to find records in table A that don't have any corresponding records in table B
select a.ID,a.templatenames from cbe a where not exists (select 1 from cbe_child b where a.ID=b.cbe_id)
Both tables have about 100 mil rows. And there is no Parent/Child relationship here, A.ID is a PK column.how can i write this select to perform better.
I have one string 'SWAP_20120224_MEGAMART_MAR_Sales.csv'
I want to get the string between second underscore and third underscore i.e. 'MEGAMART'
How to achieve this in sql statement?
how can I select whole table in parts of 100 rows?
If I have primary key I can:
CODEstart=0;
end=100;
select * from table where ID>=start_point and ID<end;
start=end;
end=end+100;
and repeat:
CODEselect * from table where ID>=start_point and ID<end;
How can I do it without primary key? Is there another posibility to getting 100 number of rows? Maybe using rowid?
i have a table displaying location, cost, inner_num and rownum ordered by the descending order of the cost.
the question is: display locations of the highest 10 costs. if the cost of 11th row is the same as 11th, then display 11 rows.
Consider the following tables
MANAGERID
101
102
103
104
MANAGERID EMPID
101 ----------------24
101-----------------25
101 ----------------26
104 --------------- 27
write sql query to get the following output without using minus,union,intersect.
MANAGERID EMPID
101 ---------------------------------- 24
101------------------------------------25
101 -----------------------------------26
102 -----------------------------------N/A
103 -----------------------------------N/A
104 -----------------------------------27
My Table structure is
SL# Start_ts
1 7/31/2012 3:53:42.864070 AM
2 7/26/2012 2:13:58.280928 PM
3 7/25/2012 2:13:41.692569 PM
4 7/21/2012 2:13:26.821070 PM
5 7/18/2012 2:13:07.827278 PM
6 7/13/2012 2:05:35.023520 PM
Question
How to select last 10 days rows only (from sysdate)
Error
1) SQL> select * from Test where to_date(start_ts, 'DD-MON-YYYY HH24:MI:SS') > to_date(sysdate, 'DD-MON-YYYY HH24:MI:SS')-10 ; (or)
2) SQL> select * from Test where to_date(start_ts, 'MM/DD/YYYY HH24:MI:SS') > to_date(sysdate, 'MM/DD/YYYY HH24:MI:SS')-10 ;
ORA-01858: a non-numeric character was found where a numeric was expected
[Code]...
I have been trying to find out how to write the following query:
Suppose the following table:
ID seq
24 1
24 2
24 3
67 1
67 2
67 3
67 4
67 5
13 1
13 2
I would like to retrieve the rows for every different ID with its max value. For one ID that would be:
select * from TABLE where seq in(
select max(seq) from TABLE where id=24)
How can i do this for all the rows??
is there a way to find out how many rows a View has? Something similar to NUM_ROWS with regular tables perhaps? Or do I have to use Count(*)?
View 4 Replies View RelatedI need to load 2 trillion data from an external table to Oracle Heap table. I am using Direct Path insert for that. how to commit after inserting n number of rows.
View 8 Replies View RelatedCan we select table's rows of 5 to 15, like
select * from emp
where rowid <=5;
It is through an error when i used the below method.
select * from emp
where rowid >=5 and rowid <=15;
I have a table ABC with two columns NAME and SEQUENCE. The valid values of sequence are from 1..50. The table may not have all the Sequence number. For example following is the data in my table..
Quote:NAME SEQUENCE
------------------------
Jonh| 5
Amy| 1
Suresh| 3
I need a SQL to get the data like
Quote:NAME SEQUENCE
------------------------
Amy| 1
| 2
Suresh| 3
| 4
Jonh | 5
|6
|7
..
..
..
|50
Basically I want to have rows for the sequence numbers which are not present i my table. I thought of using the following query to generate a sequence and then join, but does not work.
select rownum from dual connect by rownum <=50
I tried something like
with temp as (select name, sequence from abc where supp_ref_order is not null order by sequence )
select rownum as num, temp.name from dual, temp connect by rownum <=50 where temp.sequence =num
