SQL & PL/SQL :: Mark Each Group In Sequence
Dec 16, 2011
I want to group the following records and mark them in a sequence order.
drop table test;
CREATE TABLE TEST
(
key0 NUMBER,
key1 VARCHAR2(1),
key2 NUMBER)
;
INSERT INTO TEST VALUES (4,'A',1);
INSERT INTO TEST VALUES (4,'A',2);
[code]......
key0 key1 key2
4A1
4A2
4A3
4A4
3A1
3A2
3A3
1A1
1A1
Now I want the records in the following way, where group_no would be in sequence when key2 starts with 1 again.
key0 key1 key2 group_no
4A1 1
4A2 1
4A3 1
4A4 1
3A1 2
3A2 2
3A3 2
1A1 3
1A1 4
Can I achieve this using SQL only and not by PL/SQL.
View 5 Replies
ADVERTISEMENT
Dec 7, 2010
I have Table Data in format :-
--------------------------
ABC
ABC
ABC
XYZ
XYZ
and i have a requirement where I need the output in this format.
-------------
ABC 1
ABC 2
ABC 3
XYZ 1
XYZ 2
what query to be used for this
View 2 Replies
View Related
Oct 3, 2010
I ve a data in table as follows
code status seqnce Length
B1 AVL 1 10
B2 AVL 2 10
B3 ASG 3 10
B4 AVL 4 10
B5 AVL 5 10
B6 AVL 6 10
B4 ASG 7 10
B4 ASG 8 10
I need to group the AVL status codes with the sum of lengths Ex) B1 to B2 - 20 B4 to B6 - 30 like that.
View 5 Replies
View Related
Oct 1, 2011
I found nothing in SQL (all in PL/SQL).I have a table:
create table Parent (pk_id number primary key); --which is filled using sequence seq_Parent.
And I have a child table:
create table Child (rRef number, fk_parent number primary key (rRef, fk_parent);
that I need to insert into Child using seq_parent but I want to insert the same sequence for each group of rRef. I dont know how to do that using SQL not PL/SQL.
View 7 Replies
View Related
Jun 1, 2010
I have a result set with the following structure
Column 1 Column 2 Column 3 Column 4
--------- --------- --------- ---------
A1 B1 10 50
A1 B1 20 50
A1 B2 30 40
But i want to restructure this result like below,
Column 1 Column 2 Column 3 Column 4
--------- --------- --------- ---------
A1 B1 10 50
20
B2 30 40
I am just trying to change the repeating values on a certain combination as blank.
View 5 Replies
View Related
Nov 11, 2011
I have deleted lot of records in a table.Would oracle be able to insert in the empty blocks generated from deletion of records without bringing the high water mark down.
View 7 Replies
View Related
Nov 4, 2013
I am trying to discern the difference between Shrink and Move and their impact on the High Water Mark of a table.
My understanding is that MOVE in effect rewrites every row of a table ( hence why it can deal with row chaining ) whereas SHRINK basically moves existing rows in a table 'down' the table into any available free space. This is why MOVE takes a table lock whereas SHRINK takes a row lock. What I am trying to discern is - does MOVE and SHRINK effect the high water mark and does both reallocate space and give it back to free space for the tablespace ? I believe MOVE does reduce the HWM and give freed space back to the tablespace. I am not so sure about SHRINK.
View 2 Replies
View Related
Sep 30, 2011
how to do water mark in oracle report 2.1
View 8 Replies
View Related
Jun 10, 2013
I have one tablespace called U01. This tablepspace contains 31 data files. Due to high water mark I was unable to most datafiles. Since my database running onair application they will not provide me downtime to move the tables. Is there anyway to fix the high water mark without getting downtime window? almost 700+g space unused. I need to reuse them asap because running out of space with in asm diskgroup.
SQL> SELECT A.TABLESPACE_NAME,round(SUM(A.TOTS)/1024/1024) "Tot size MB",
2 round(SUM(A.SUMB)/1024/1024) "Tot Free MB",
3 round(SUM(A.SUMB)*100/SUM(A.TOTS)) "%FREE",
100-round(SUM(A.SUMB)*100/SUM(A.TOTS)) "%USED",
round(SUM(A.LARGEST)/1024/1024) MAX_FREE,SUM(A.CHUNKS) CHUNKS_FREE
4 5 6 FROM (
7 SELECT TABLESPACE_NAME,0 TOTS,SUM(BYTES) SUMB,
[code]...
View 5 Replies
View Related
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
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
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
May 9, 2011
Is there any way to add one disk group space to another disk group. Because One of my disk group is full i want to add space in to that group.
View 1 Replies
View Related
Mar 23, 2013
I'm using this code, and it performs fine, but I'm wondering if there is a more elegant way to do it--maybe with "ROLLBACK". Basically (as you can see) I need to get a normal count for each group but also for each group take a percentage of the total count (so all groups pct adds up to 100 (oh yeah, don't test for zero below, but just a test... )
select
c.Event,
c.code,
count(1) as calls,
total.total_count,
count(1) / total.total_count * 100 as pct_of_total
from
table1 c
[Code]....
[Edit MC: add code tags, do it yourself next time]
View 4 Replies
View Related
Jul 24, 2010
How do I get a query of each sequence and who has the permissions to it?
View 1 Replies
View Related
Jun 1, 2009
I have created a trigger for after insert which updates a table when there is a row inserted in that table. The update is on a column which stores the application description along with the sequence number. Now my requirement is that sequence number should be unique only with in an application but not with in the table.Say the row entry can be as follows:
App_Desc Request_ID
-----------------------
DEV 100 1
DEV 101 2
STG 100 3
STG 101 4
Here Request_ID is unique But the sequqnce thats created for DEV (100,101) should take an entry of 102 for the next entry for DEV and same applies for STG. So I have to use the same sequqnce for all the application.
View 1 Replies
View Related
Apr 16, 2007
I have 3 tables whose structure is as follows:
1) Invoice
Invoice_no number(8),
Debtor varchar2(35),
Invoice Date Date,
Print Date Date,
Currency varchar2(3),
Total Amount Ex Number(11,2),
Total Amount Vat Number(11,2),
Total Amount Number(11,2),
Status Varchar2(15),
2) Invoice Line
Invoice_no number(8),
Invoice_Line_No number(8),
Description_1 varchar2(35),
Description_2 varchar2(35),
Line_Quantity Number(11,2),
Currency varchar2(3),
Line_Amount Number(11,2),
Vat Code number(1),
Status Varchar2(15),
3) vat code
vat code number(1),
Description varchar2(35),
Vat Percentage number(11,2)
Here invoice no and invoice line no are automatically generated fields?
How to generate invoice line no corresponding to invoice no and how to satisfy the following conditions:
1) total vat amount in invoice table should be automatically filled . total vat amount=(line_quantity* line_amount*vat percentage)/100
2) invoice cannot be printed, changed when status is 'closed'.
3) invoice lines cannot be added,changed or removed when status of invoice is closed
4) when any of invoice line no is changed,added, removed or invoice is changed the status of invoice should be changed to 'new'.
5) invoice date of a new invoice cannot be of the past
View 1 Replies
View Related
Oct 10, 2012
Are there any recommendations or good practices to set sequence CACHE parameter (for example one caching per hour, day etc)?
View 4 Replies
View Related
Jul 23, 2010
For some reason Oracle Sequence is skipping the first value. Here is an example of script.
drop sequence tseq;
drop table xyz;
create table xyz
(sno integer);
create sequence tseq
start with 1;
insert into xyz values (tseq.nextval);
select * from xyz;
Now the runtime of the script in SQL Developer:
drop sequence tseq succeeded.
drop table xyz succeeded.
create table succeeded.
create sequence succeeded.
1 rows inserted
SNO
----------------------
2
Why is it skipping the starting value 1. Is there something new in 11g that is causing it to skip the first value. I have many scripts and they all are having errors due to this issue.
View 39 Replies
View Related
Sep 11, 2010
Create a sequence E_SQ which start with the (current max empno + 1) and increment by 1.
View 5 Replies
View Related
May 4, 2010
is there any way to alter only max value in sequence without specifying the max value.
i know we can alter it like :
-- Alter sequence
alter sequence TEST_SEQ
maxvalue 99999999;
can we alter it without providing the max value and let oracle choose default value for the same, same as we can do it when creating a new sequence.
View 5 Replies
View Related
Mar 19, 2012
As we can give grant on sequence and can use in different schema of same database, can same sequence be used across databases?
View 2 Replies
View Related
Nov 1, 2007
After you've created a sequence in sql plus is there anyway of viewing the contents of the sequence and what it does?
View 1 Replies
View Related
Jul 25, 2011
We Require to Change the Sequence Next Value.
If it possible to Update the Sequence Next Value in Oracle.
View 3 Replies
View Related
Dec 27, 2010
The below trigger is running fine without any error ,but i wasn't able to perform the exact logic by the below trigger.IF, inserting multiple row then the SIVNUM field gets the same sequence number for each row , without incrementing the value for the next row.
Only the sequence value increases on the next set of row ow's inserted
Example (inserting 2 rows ) based on the below trigger:
item , refwo , sivnum
1234 , 55555 , 00001
6352 , 77777 , 00002
i want to achieve :
item , refwo , sivnum
1234 , 55555 , 00001
6352 , 77777 , 00001
CREATE OR REPLACE TRIGGER siv_mat_insert
BEFORE INSERT
ON MATUSETRANS
REFERENCING OLD AS old NEW AS new
for each row
[code]....
View 6 Replies
View Related
May 29, 2012
I have round about 100 sequences in a schema. How can i identify that which sequences are not used for a long time. Or How can i identify the last date when that sequence is used.
View 5 Replies
View Related
Sep 17, 2012
In our production, we have two nodes in the cluster. We use the sequence for one of the main table for primary key. Our application is expecting sequence number increments along with created date time stamp. Right now sequences are cached for each node and it creates problem for the application. We would not like to use NOCACHE option because it causes performance issue.
This is the current scenario -
Transaction #1 on Node 1 - Seq ID 1 - Time Stamp 12:01
Transaction #2 on Node 2 - Seq ID 51 - Time Stamp 12:02
Transaction #3 on Node 1 - Seq ID 2 - Time Stamp 12:03
When I try to query based on the time stamp, primary should also go up. To be very clear on what I would like to have, please consider the following example.Without using NOCACHE option, I need to have the data in the following order.
Transaction #1 on Node 1 - Seq ID 1 - Time Stamp 12:01
Transaction #2 on Node 2 - Seq ID 2 - Time Stamp 12:02
Transaction #3 on Node 1 - Seq ID 3 - Time Stamp 12:03
In other words, sequence number should always increment along with the time.
View 2 Replies
View Related
May 13, 2011
i have create one sequece using this syntax
CREATE SEQUENCE seq_emp_id START WITH 100;
after that when i create a table using this syntax
CREATE TABLE employee
(
emp_id integer default NEXTVAL ('seq_emp_id'),
name text
);
i got an error like this
*
ERROR at line 2:
ORA-04044: procedure, function, package, or type is not allowed here
so how can i resolve my this error
View 3 Replies
View Related
Oct 22, 2012
I have Created sequence in oracle 10G and its working fine. its showing THE NUMBER IS 2
SQL> DECLARE NUM NUMBER;
2 BEGIN
3 select SERIAL_NO.NEXTVAL into num from dual;
4 DBMS_OUTPUT.PUT_LINE('THE NUMBER IS ' ||NUM);
5 END;
6 /
THE NUMBER IS 2
PL/SQL procedure successfully completed.When i execute this code in oracle 11G then it does not show output.
SQL> DECLARE NUM NUMBER;
2 BEGIN
3 NUM :=SERIAL_NO.NEXTVAL;
4 DBMS_OUTPUT.PUT_LINE('THE NUMBER IS ' ||NUM);
5 END;
6 /
PL/SQL procedure successfully completed. SQL> Why this is not showing output as THE NUMBER IS .
View 1 Replies
View Related
Jun 10, 2011
I created a Table with a single column varchar2.. in which I wanted to insert value like 'BBBBAB1'... till 'BBBBAB100'
Created a sequence starting with 1...
and inserted single row, and multiple rows using loop also; by using below code -
insert into Trans SELECT CONCAT('BBBBAB', Trans1.NEXTVAL) from dual;
but whenever I see the values they are not as required ... 'BBBBAB1' but one character 'B' is missing, and the values populating are 'BBBAB1'.. 'BBBAB100'
View 1 Replies
View Related
