SQL & PL/SQL :: How To Show Sequential Numbers For Each Group

Oct 4, 2010

MyTable has missing records:

id_group id_column
-------------------------
1 1
1 2
1 3
1 5
1 9
2 1
2 2
2 4
2 6

As you see, it could be missed record 4,6,7 and 8 of group 1 and record 3 and 5 of group 2. That's is an example.

I need to show all sequential records, incluing missing ones, like this:

Id_group Id_column
-------------------------------------------------
1 1
1 2
1 3
1 4
1 5
1 6
1 7
1 8
1 9
2 1
2 2
2 3
2 4
2 5
2 6

I try this query:

/*---*/
Select G.id_group, C.rownum id_column
from (select distinct id_group from MyTable) G
cross join (SELECT rownum FROM dual CONNECT BY LEVEL <= (select
max(id_column) from MyTable)) C
/*---*/

but it shows record 7, 8 and 9 as members of group 2.

View 9 Replies


ADVERTISEMENT

SQL & PL/SQL :: Extracting Numbers And Few Portion Of Text From A String Containing Dates / Characters And Numbers

Jul 13, 2011

I have a table test with column containing dates, characters and numbers. I have to extract the number part and the three characters before the number . My data looks like :

TEST
ID DATA
1 3/12/2007
2 0
3 3/8/2010 ABC 217
4 NONE
5 COLM XYZ 469 6/8/2011
6 LMN 209

My expected results should look like :

ID DATA
1
2
3 ABC 217
4
5 XYZ 469
6 LMN 209

View 7 Replies View Related

PL/SQL :: Count Sequential Events

Jul 5, 2012

I have a table with around half a million rows. I would like to determine how many sequential events occur per device per unique group of events. The requirement is that the events continue to occur within 1 second of each other, every second, with no gaps. For example, in the sample below there is a sequence of 2 events for device 2737, and another sequence of 5 events for device 2737. If there was a 1 second interval with no event (Signal_time) then that would be the end of one group of events, this is also shown below.

Using Oracle 11.2.0.2.0

DEVICE_ID     SIGNAL_TIME
4401     17-JUN-12 12.59.59 PM
2332     02-MAY-12 12.59.59 PM
1086     30-MAY-12 12.59.58 PM
2737     17-JUN-12 12.59.58 PM -- start of one group / sequence of events
2737     17-JUN-12 12.59.58 PM -- end of one group / sequence of events

[Code]...

-- start of one group / sequence of events

2737     16-JUN-12 11.59.59 PM
2737     16-JUN-12 11.59.59 PM
2737     17-JUN-12 12.00.00 AM
2737     17-JUN-12 12.00.01 AM

-- end of one group / sequence of events

2737     17-JUN-12 12.00.03 AM

-- this is excluded from the above group of 5 events, but this may be the start of another group

Required output

Device--Count(*)--Start_Signal_Time-----------End_Signal_Time------------Duration (seconds)
2737----5-------------16-JUN-12 11.59.58 PM---17-JUN-12 12.00.01 AM--4
2737----2-------------17-JUN-12 12.59.58 PM---17-JUN-12 12.59.58 PM--1

View 3 Replies View Related

Performance Tuning :: Analyzing Non-sequential Snapshots In AWR

Mar 5, 2013

How can check database performance in a non-sequential parts of a day using AWR tool. For example, suppose we divide a day to 2 parts: low-traffic time and high-traffic time with the following time interval:

low-traffic time : 7:00am - 10:00am and 7:00pm - 11:00pm
high-traffic time : 10:00am - 01:00pm and 4:00pm - 06:00pm

I want to examine performance using snapshots gathered by AWR. If I get 2 AWR reports for low-traffic time of day (one for 7:00am - 10:00am, another for 7:00pm - 11:00pm ) and compute average of values in 2 reports, could I called it database performance in low-traffic part of day or not?

View 9 Replies View Related

Application Express :: To Change The Default Behavior Of Hide / Show Region To Show

Jun 26, 2012

I am trying to change the default behavior of Hide/Show Region to show, after some attempts i got it partially working but now clicking the icon to toggle hide/show doesn't work also changed the icons and added type="" but its not working.

View 16 Replies View Related

Database Scattered / Sequential Read And Async IO Waits

Nov 23, 2010

We have a table (call it PROBLEM table) that varies between 60 and 100 million records from one data archival process to the next.The table has data from all 50 states and is range partitioned by each states unique code and has a primary key based on the following:

STATE the data is from
BMRK YEAR the year the data was last benchmarked
AREA location the data is from
SERIES industry the data is located in
YEAR the data was reported
MONTH the data was reported
DATA TYPE the type of data that was reported
ESTIMATE TYPE the type of statistical estimate that was produced from the data
REPORT ID report id unique to each state
REPT SFC state the reporter resided in

We have developed a job that will process data from other tables and create some statistics resulting in approx 2.2 million inserts, .5 million updates and .5 million deletes on PROBLEM table during each job run. Once this table is loaded another process (call this PROBLEM_PROCESS)takes off that reads this updated information to produce some statistical data that is stored in another table. This data is produced by summing anywhere between 2 and 5000 records per query from the PROBLEM table.Here is the query (call it PROBLEM_QUERY)

select round(sum(cm_value*sample_weight),3),round(sum(pm_value*sample_weight),3)
from matched_sample
where state_fips_code = :1
and area_fips_code = :2
and series_type_code = 'B'
and year = :3
and month = :4
[code]...

When we run this job it is very fast (20 minutes) until it gets to the PROBLEM_QUERY, at this point we get DB file scattered read waits, DB file sequential read waits and async IO waits and the job has run for as long as 2 days. When I look at grid, the PROBLEM query is doing a full table scan and ignoring the index.

Our systems people advised us to re-organize and re-index that data in the PROBLEM table after all of the DML and before the PROBLEM_PROCESS takes off. If we do this, it works and cuts our time down alot. However, the re-organize and re-index process adds eight hours to this process. Note: When we re-roganized the data we create a copy table on another set of table spaces and insert the data into the copy sorting the data on the primary key columns. After this we re-index the new table and drop the old table renaming the new table. When we do this later, we move the data back to another set of table spaces. So, we move the data back and for between table spaces A and B we can say.

We have Oracle installed on a M5000 server with Solaris as the OS the binaries and data files are stored on a NetApp Storage array(model 3160) of 500 1TB SATA 7200 RPM drives. However, there are 128 other databases on the NetApp filer as well.

IMO the array and the slow disks are the problem. I believe this because we are catering to the slow disks by re-organizing and re-indexing the PROBLEM table during each run. I don't believe this should be neccessary. We normally re-organize and re-index our data each week in our production system after many more transactions than this.

Our systems people state it is our application. Oracle Support tells us the statistics are out of date but have not answered us on why the statistics are out of date and the index is abandoned after 1 run.

View 7 Replies View Related

Performance Tuning :: High CPU Utilization In RAC And Log File Sequential Read Event

Apr 13, 2011

In a 3-node RAC setup; one node is showing high CPU utilization around 40~50%. The CPU utilization was less than 20% 10 days back but from 9th oldest day it jumped and consistently shows the double figure. I ran AWR reports on all three nodes and found one node with high CPU utilization and shows below tops events-

EVENT WAITS TIME(S) AVG WAIT(MS) %TOTAL CALL TIME WAIT CLASS
CPU
time 5,802 34.9

RFS
ping 15 5,118 33,671 30.8 Other

Log file sequential
read 234,831 5,036 21 30.3 System I/O

Sql*Net
more data from
client 24,1711,08745 6.5 Network

Db file sequential
read130,939 4533 2.7 User I/O

Findings:-
On AWR report(file attached) for node= sipd207; we can see that "RFS PING" wait event takes 30% of the waits and "log file sequential read" wait event takes 30% of the waits that occurs in database.

1)Are these symptoms of undersized log buffer?
2)I feel Network wait can be reduced by tweaking SDU & TDU values based on MDU.

View 2 Replies View Related

Export/Import/SQL Loader :: Sequential Data File Record Processing?

Oct 1, 2013

If I use the conventional path will SQL*Loader process a data file sequentially from top to bottom?  I have a file comprised of header and detail records with no value found in the detail records that can be used to relate to the header records.  The only option is to derive a header value via a sequence (nextval) and then populate the detail records with the same value pulled from the same sequence (currval).  But for this to work SQL*Loader must process the file in the exact same sequence that the data has been written to the data file.  I've read through the 11g Oracle® Database Utilities SQL*Loader sections looking for proof that this is what will happen but haven't found this information and I don't want to assume that SQL*Loader will always process the data file records sequentially. 

View 9 Replies View Related

Performance Tuning :: Query Hanging With Sequential Read Or Latch Free Waits?

Apr 7, 2011

Following query is hanging either with 'Sequential access read' or 'Latch Free' wait event Important thing is the table which is self joined in subquery here does not have any index at all While it was hanged I tried to get trace of it and terminated twice. As such haven't got 'row source generataion' The table has only 120000 records and it shall update 34000 records

UPDATE invoice_header inv
SET inv.modified_due_date =
(SELECT inv1.btn_due_date
FROM invoice_header inv1
WHERE inv.dct_code = inv1.dct_code AND inv1.release = 'A5')

[code]...

During 'sequential read' using p1,p2 values tried to get what the session is reading and found that it is using the table itself.

During lath free I found following
SELECT name, 'Child '||child#, gets, misses, sleeps
FROM v$latch_children
WHERE addr= (select p1raw from v$session_wait where sid=18)
UNION

[code]...

However instead of self join when I creaed global temporary table as

create global temporary table t as select * from invoice_header where release='A5'

And used it in the update as

UPDATE invoice_header inv
SET inv.modified_due_date =
(SELECT t.btn_due_date
FROM t
WHERE inv.dct_code = t.dct_code AND t.release = 'A5')
WHERE inv.release = 'A5' AND inv.btn_due_date >= TRUNC (SYSDATE)

It updated the records in a second!!

Questions are
1) why it is producing 'sequential read' wait event when there is no index access or else why it is doing single block access when FTS is required?
2) Why is the 'latch free' wait event here and what it indicates here with 'cache buffer handles'?
Is it because we are reading and updating the same segment?

know in case DDL of table is required. It has all nullable columns and no index at all. Since it is 9i I am unable to use MERGE effectively in this case

View 14 Replies View Related

Group Records With Less Than One Hour Separation And Count How Many Per Group

Nov 1, 2013

I'm trying to group sets of data based on time separations between records and then count how many records are in each group.

In the example below, I want to return the count for each group of data, so Group 1=5, Group 2=5 and Group 3=5

SELECT AREA_ID AS "AREA ID",
LOC_ID AS "LOCATION ID",
TEST_DATE AS "DATE",
TEST_TIME AS "TIME"
FROM MON_TEST_MASTER
WHERE AREA_ID =89
AND LOC_ID ='3015'
AND TEST_DATE ='10/19/1994';

[code]....

Group 1 = 8:00:22 to 8:41:22

Group 2 = 11:35:47 to 11:35:47

Group 3 = 15:13:46 to 15:13:46

Keep in mind the times will always change, and sometime go over the one hour mark, but no group will have more then a one hour separation between records.

View 4 Replies View Related

SQL & PL/SQL :: Combining Rownum And Group By Gives - Not A Group By Expression

Jun 23, 2011

I read that rownum is applied after the selection is made and before "order by". So, in order to get the sum of salaries for all employees in all departments with a row number starting from 1, i wrote :

select ROWNUM,department_id,sum(salary) from employees group by department_id

If i remove rownum, it gives the correct output. Why can't rownum be used here ?

View 16 Replies View Related

SQL & PL/SQL :: Group Through Expression Inside Group By?

May 17, 2011

Refer to the txt file to create table and insert data.

I executed the following query-

SELECT priority, detail, COUNT(1) FROM TEST GROUP BY priority, detail

and got the following result-

PRIORITYDETAIL COUNT(1)

StandardPatch 27
StandardInitial TSS 1
StandardInitial development 10
StandardProduction deployment5
High PriorPatch 1

Now I want that Initial TSS and Initial development should be combined as Initial together and I should get the result as follows:

PRIORITYDETAIL COUNT(1)

StandardPatch 27
StandardInitial 11
StandardProduction deployment5
High PriorPatch 1

View 3 Replies View Related

String Of Numbers

Oct 12, 2007

I have the following set of numbers that i am passing in as one input into a stored procedure.

234,456,234,456,567

Now i want to take this list of numbers and use it in an IN statement:

select * from table where column_a in (P_INPUT);

however, when i try this, it give me an invalid error. I have tried inserting single quote around each value and get the same invalid error. I tried a To_char around my column, which solved the error, but it never finds a match!

View 6 Replies View Related

SQL & PL/SQL :: How To Get Random Numbers

Jul 1, 2010

I want to get 10 random numbers from existing 100 numbers. How can we get/generate random numbers ?

for example I have a table with customer ID, customer Name, having 100 record. We want 10 customers ID randomly from that 100 record not repeated any number. Have any command or procedure for that ?

View 3 Replies View Related

SQL & PL/SQL :: How To Get Column Containing Numbers Only

Jul 25, 2010

I have a column of datatype varchar2

this column may contain char or may contain number or may contain both

some values in this column are only number is there any function or any other way to get those column that are only number

View 3 Replies View Related

SQL & PL/SQL :: Combinations Of Numbers

Nov 1, 2010

Earlier, we had a discussion how to generate the permutations. generating the code for combinations

For eg: if I have input as [1,2,3,4,5] then 5c2 = 20 combinations should be generated as pairs

1,2
1,3
1,4
1,5
2,3
2,4
2,5
3,4
3,5
4,5

Total of 10 combinations

View 8 Replies View Related

SQL & PL/SQL :: Function To Sum Five Numbers

Apr 13, 2013

I am creating a function to sum five numbers (less 1). Is it possible to have an array of numbers in an SQL function, and how would this be implemented?

Here is the screenshot of my output (I cannot embed links until 5 posts!): flic.kr/p/eaSHBP

CREATE OR REPLACE FUNCTION sumfivenumbers ( n1 NUMBER, n2 NUMBER, n3 NUMBER, n4 NUMBER, n5 NUMBER)
RETURN NUMBER
IS
Sumnums NUMBER;
BEGIN
SELECT SUM(n1+n2+n3+n4+n5-1) INTO Sumnums FROM DUAL;
DBMS_OUTPUT.PUT_LINE(Sumnums);
RETURN 1;
END sumfivenumbers;
/
SELECT sumfivenumbers(5,5,5,5,5) AS "Five Numbers less 1" FROM DUAL;

View 7 Replies View Related

SQL & PL/SQL :: Selecting 1st And 2nd TOP Numbers (from Same Row)

Jul 6, 2012

I need to find the top two values value for each ID Number:

CREATE TABLE TABLE_1
(ID number (8),
NUMBER_1 number (2),
NUMBER_2 number (2),
NUMBER_3 number (2),
NUMBER_4 number (2));

INSERT INTO TABLE_1
VALUES
('12345679','30','25','30','05');
INSERT INTO TABLE_1
VALUES
('99999999','30','25','15','05');

Desired Result:

ID Number 1st 2nd
12345679 30 30
99999999 30 25

View 7 Replies View Related

SQL & PL/SQL :: Numbers To Alphabets

Jul 6, 2013

I have a requirement to display numbers as equivalent alphabets , like if the stored value is 1 then it should display as 'A' ,2 means 'B' ,is there a way to find out.

CREATE TABLE APS ( ITEM_NO NUMBER, ITEM_NAME VARCHAR2(12))
INSERT INTO APS (ITEM_NO,ITEM_NAME) VALUES (1,'TEST1');
INSERT INTO APS (ITEM_NO,ITEM_NAME) VALUES (2,'TEST2');
INSERT INTO APS (ITEM_NO,ITEM_NAME) VALUES (3,'TEST3');
INSERT INTO APS (ITEM_NO,ITEM_NAME) VALUES (4,'TEST4');

-- The output to be is.

item_no , item_name
A TEST1
B TEST2
C TEST3
D TEST4

View 23 Replies View Related

SQL & PL/SQL :: Numbers To Words

Jul 6, 2012

I tried to convert numbers to words, it shows the below error.

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Jul 6 11:00:29 2012

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> SELECT TO_CHAR (TO_DATE (2447834, 'j'), 'jsp') FROM DUAL;

TO_CHAR(TO_DATE(2447834,'J'),'JSP')
---------------------------------------------------------------------------
two million four hundred forty-seven thousand eight hundred thirty-four

SQL> SELECT TO_CHAR (TO_DATE (244783400, 'j'), 'jsp') FROM DUAL;
SELECT TO_CHAR (TO_DATE (244783400, 'j'), 'jsp') FROM DUAL
*
ERROR at line 1: ORA-01830: date format picture ends before converting entire input string

SQL>

View 4 Replies View Related

PL/SQL :: Extract Numbers Only

Sep 18, 2012

Here i face probelm that he numbers must be follw by DOT "." , this is not correct if the statment only conatines numbers without DOT that not extract. As the

SELECT REGEXP_SUBSTR ( 'hello to 8898989898989 jkjk nnnm mnj'
, '([0-9]+.[0-9]*)' || -- Starts with digit(s) (may or may not have digits after .)
'|' || -- or
'(.[0-9]+)' -- starts with decimal point
) AS result
FROM dual
;

but mean i have to add . after numbers . as follow

SELECT REGEXP_SUBSTR ( 'hello to 8898989898989 jkjk nnnm mnj'
, '([0-9]+.[0-9]*)' || -- Starts with digit(s) (may or may not have digits after .)
'|' || -- or
'(.[0-9]+)' -- starts with decimal point
) AS result
FROM dual
;

but this is not right

i want to extract numbers without DOT also.

View 6 Replies View Related

SQL & PL/SQL :: How To Print Even Numbers Without Using MOD Function

Mar 1, 2012

How to print even numbers 1 to 10.without using the MOD Function

View 17 Replies View Related

Insert All Client Numbers

May 12, 2010

I am trying to run this script:

Update ACT.CUSTOMER_CLIENT_REFERENCE
Set ORIGINAL_SOURCE_FG = 'N',
CANADIAN_ULTIMATE_REFERENCE_FG = 'N',
LUDT = SYSDATE,
CLT_NO = (Select clt_no from client.client where clt_no between 701885 and 705287)
Where Exists (Select 1 from client.client where clt_ofc_no = 19 and clt_no between 701885 and 705287)

But the CLT_NO = .... line is giving me this error: ORA-01427: single-row subquery returns more than one row

what i am trying to do is insert all the client numbers between 701885 and 705287 into ACT.CUSTOMER_CLIENT_REFERENCE

View 6 Replies View Related

SQL & PL/SQL :: Checking Consecutive Numbers

May 11, 2012

I have a table with three columns: terminal, place and batch. How can I check for missing batches?

select * from transactions;

terminal place batch
84812
84813
84814
84816
84821
84823
84824
84825

View 5 Replies View Related

SQL & PL/SQL :: Spell Out Numbers To Words

Mar 14, 2002

I found a message which explain how to spell out numbers to words. I'm french and the purpose of my question is how to convert numbers to word (to print cheque) but in french the function found in the newsgroup was :

select decode( sign( &num ), -1, 'Negative ', 0, 'Zero', NULL ) ||
decode( sign( abs(&num) ), +1, to_char( to_date( abs(&num),'J'),'Jsp') )
from dual
/

I'm afraid but i don't undaunted how pass my number to this function (&num ???)

View 12 Replies View Related

SQL & PL/SQL :: Missing Numbers In A Sequence

Feb 16, 2010

create table t (
number_from number,
number_to number);
insert into t values(1,3);
insert into t values(5,9);
insert into t values(10,15);
commit;

I need to create a stored function that could find/return missing numbers between number_from and number_to for each record in table t.

For example:

Number_fromNumber_to missing

13
594 from previous record, this record is supposed to start from 4

View 24 Replies View Related

Forms :: Calculation When Add Numbers

Feb 5, 2011

I am developing a form where I need to add Numbers.In fact we have a bag of Cones that contain 24 cones.In normal calculation when I add numbers for example

5.24 Plus 5.24 it will give the result 10.48

I Need the appropriate method to calculate if I add these two numbers it should give the result 5.24 Plus 5.24 the result should be 12

View 7 Replies View Related

SQL & PL/SQL :: Find Numbers Between More Than 1 Condition

Sep 21, 2011

I have a table A with 10000 records with values as 1 to 10000. Table B has columns as range,number1,number2. Totally there are three rows as

ROW1=range1,1,100 & ROW2=range2,300,500 & ROW3=range3,800,900

I need to select all records from table A when it is between any one of the three rows from table B.

View 13 Replies View Related

SQL & PL/SQL :: Assigning Numbers To The List?

Apr 10, 2012

acheive the below result.

Select * from result;

res
===
3
3
3
3
3
0
3
3
3
0
3
0

output:

I would like to get res1 using SQL query.

resres1

31
32
33
34
35
00
31
32
00
31
32
33
34
00
.. ....

View 9 Replies View Related

SQL & PL/SQL :: No Rounded Decimal Numbers

Aug 5, 2011

how can show this query in this picture

select round(15.555,2) from dual appear final result 15.55 only

and this query don't be this result 15.56

because i have filed and always Decimal numbers be (15.555,2)

i want only result appear after two numbers and no rounded the numbers

View 2 Replies View Related







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