SQL & PL/SQL :: Retrieving Related Records As Groups
Mar 12, 2008
I have a table of N records with: Name SeqNo ID Col4 ... ColX
where Name and ID are non-unique, and SeqNo is a monotonic non-consecutive sequence 0 .. N that is unique within ID..I'd like to generate the following 'groups': For each record where SeqNo = 0, sorted by Name, create the 'group where ID is the same, ordered by SeqNo irrespective of the
values of any of the other columns. For instance, if the table contained:
NameSeqNoIDCol4 ... ColX
RPL2975...
TLM0444...
AAB2801...
AZZ0801...
ABA3444...
KTT4975...
ABA1801...
YHG0975...
DEF1444...
I'd like to generate:
NameSeqNoIDCol4 ... ColX
AZZ0801...
ABA1801...
AAB2801...
TLM0444...
DEF1444...
ABA3444...
YHG0975...
RPL2975...
KTT4975...
I got my desired results by brute-forcing via four sub-queries:
Sub-query 1 - Generate the sorted Names with SeqNo = 0
Sub-query 2 - Expand above with the additional columns,
maintaining original order
Sub-query 3 - For each of the records from sub-query 2,
generate the 'dependents' having the same ID
and SeqNo != 0
Sub-query 4 - Expand above with the additional columns,
maintaining original order of sub-query 1
Main query - Create UNION of 2 and 4, sorting by original
order and SeqNo
if there were not a simpler approach - after all, this must be a fairly common issue when generating BOMs.
View 4 Replies
ADVERTISEMENT
Apr 22, 2010
Data at a table with single column unique indexed is like
1,3,5,6,8,10,98,45,23,56,34,12...... there are about 100 million records.
Out of these values we need to create fixed(5 in each) groups, from - to.
example
1-8
10-56
98-..
View 11 Replies
View Related
Jun 11, 2012
I am using 10.2.4.0 of oracle. I am having one requirement, in which i have to divide the set of records into certain groups , so that they can be executed partly but not in one run.
So in the 'SELECT' clause itself i want to assigns particular value (may be 1 )to first 50000 records then another value(may be 2) to next 10000, like wise. And again the total count of records will also varry time to time , if the total count of record set is less than 10000 , then it should only assign '1' to all the records. i will set the group values (1,2,3...) as another column itself.
View 4 Replies
View Related
Jun 27, 2011
I'm trying to get a list of values from a table, and I am just not seeing the answer -I have a table with 4 records (say A, B, C, and D), each record has a GUID to uniquely identify it.I have a 2nd table that holds the relationships (parent-child).
At this point, I have 2 rows:
A>B
A>C
So, A is a parent to B, and A is a parent to C (D is not related to anything at this point)
I would like to be able to go into the D record and see that it is NOT related to A (A would be the parent in this instance) - the trick however is I only want 1 instance of A to show up.attempted to use some inner/outer join qualifications and I just cannot seem to get the data to display properly.
View 1 Replies
View Related
Dec 2, 2010
My table XXX has following records with data
ID AMOUNT ID_TYPE APPROVE_FLAG
A1 2 A N
B1 100 B N
A2 3 A N
A3 100 A Y
The Select Query should be..All the records should be considered and if the (amount is greater than 50 and approve_flag is N )then except that records all records should be considered.The output should be 3 rows; 2nd row (B1) should not come. writing a select statement for the above conditions.
View 4 Replies
View Related
Mar 27, 2010
I want to retrieve the data from ex.tables. How to get this.
table1:
Account_No, Account_sub_No
1234 1
1234 2
1234 3
2345 4
2345 5
2345 6
2345 7
2345 8
................
Account_no is the primary key
table2:
Account_sub_No, Description
1 Hello
2 Hi
3 No.1
4 great
5 people
.................
8 world
..........................
Account_sub_No is primary key.
Out put:
I want the data like Account_no who is having more than 3 Account_sub_no values.
But in my case need to join these two tables with other tables. join field is Account_no from table1. there are no other fields to join.
View 5 Replies
View Related
Nov 29, 2012
below requirement..
We have certain records like SQL, PL/SQL, Reports, Forms, OAF etc in a table. We wanted to capture rating for each of these criteria. So we want a form to be displayed dynamically..
SQL 1 2 3 4 (1,2,3,4 represent radio buttons)
PL/SQL 1 2 3 4
Reports 1 2 3 4
Forms 1 2 3 4
OAF 1 2 3 4
If we add another row, XMLP in that table additional radio button should be displayed automatically..
View 4 Replies
View Related
Apr 12, 2010
I have to write a query which will get records only if all the rows satisfies the condition.
Output should retrieve only 'A' because all the values are '1' Where as for 'B' only two rows having value '1'.
create table table1(field1 varchar2(100), field2 varchar2(10));
insert into table1(field1,field2)values('A','1');
insert into table1(field1,field2)values('A','1');
insert into table1(field1,field2)values('A','1');
insert into table1(field1,field2)values('B','2');
insert into table1(field1,field2)values('B','2');
insert into table1(field1,field2)values('B','1');
insert into table1(field1,field2)values('B','1');
SELECT field1
FROM table1
WHERE field2=all(select '1' from dual)
FIELD1
A
A
A
B
B
View 2 Replies
View Related
Feb 25, 2011
We have a new implementation which will be using ASM with RAID. The data area needs to be 3TB, and the recovery area, to be used for archive logs and RMAN backups, needs to be 1TB.
The configuration i'm thinking about now is:
+DATA diskgroup: 5*600G disks, using RAID 1+0 (this will include control files)
+FRA diskgroup: 2*500G disks, using RAID 1
+LOG diskgroup: 2*1G disks, using RAID 1 (this is only for redo logs)
So here are my questions:
1. Am I right in supposing that we would get the best performance on the +FRA and +LOG diskgroups by not using RAID 1+0?
2. It has already been agreed to use RAID 1+0 for the +DATA diskgroup, but I can't see the added benefit of this. ASM will already stripe the data, so surely RAID 0 will just stripe it again (i.e double striping the data). Would it not be the better just to mirror the data at a hardware level?
View 4 Replies
View Related
Aug 1, 2013
I am able to assign a user to a user group using the User Admin in Apex.I don't know how I would be able to assign a role (that I know how to define that for an individual user).The only thing I can see is a name for User Group and a Description! My requirement is to define a group of people to be assigned to one group/role, so that every change to that role can be automatically be applied to each user in that group
View 1 Replies
View Related
Sep 11, 2013
I have 5 MViews that I want to refresh in two occasions, every Sundays and at the 1st of the month. I created a Refresh Group for the weekly and that work fine. But when I tried to created the second Refresh Group for the monthly I get a "materialized view is already in a refresh group".
You can only have a materialized view in one refresh group? What options to I have to refresh it in different intervals?
View 1 Replies
View Related
Sep 29, 2010
We recently migrated a database from 9i to 10g (overdue we know!!) and discovered that dbms_mview.refresh default behavior was turned upside down - meaning that 10g didn't first truncate the MV to refresh it. We're trying to unwind a lot of legacy issues, but it also turns out that we also have 100 REFRESH GROUPs and 100 MATERIALIZED VIEWs. That means a 1 to 1 relationship between RGs vs MVs. There is one MV defined to each RG.
These are my questions:
1) Does a 1 to 1 relationship between RGs and MVs make sense to anybody? The original implementors are gone and we can't fathom the reason for this.
2) Is there any reason why I shouldn't convert these 100 groups to plain and simple 100 MVs? I don't want the delete/insert refresh behavior of dbms_refresh.refresh and I do want the truncate behavior of dbms_mview.refresh ATOMIC=FALSE for refreshing a standard MVIEW
View 1 Replies
View Related
Mar 14, 2012
i want to create a group xyz and add some users to xyz group and want to grant/revoke permissions to xyz. So that all the users present in that group will have the same permissions as of the xyz group. so that instead of giving the permissions to users individually i can give it at a time.
View 20 Replies
View Related
Nov 29, 2010
the thread title was a bit confusing, couldn't come up with anything short to describe the question. What I am looking for is a query which will put records into groups based on matching values in one of two columns. So if two records have a matching value in column 1 or column 2 they are in the same group. See the example bellow and expected output for a "better" explanation:
--setup
CREATE TABLE foo
(foo_id NUMBER NOT NULL PRIMARY KEY,
record_number NUMBER,
record_value VARCHAR2(1));
[Code]...
--expected output
group# foo_id record_number record_value
1 1 1 A
1 2 1 B
1 3 2 B
1 4 2 C
2 5 3 D
3 6 4 E
3 7 5 E
My initial thought is that is feels a little bit like the sequential seat problem but not quite close enough. I know it could be done iteratively with PL/SQL but I am thinking there must be a way to do it in SQL I am not seeing yet.
View 6 Replies
View Related
Feb 23, 2013
I have a table as follows:
customers
------------------------------------------------------------------------------------------
custid credit amt month
--------------------------------------------------------------------------------------------
001 C 2000 Jan-2012
001 D 5000 Feb-2012
001 C 3000 Mar-2012
001 C 3000 Apr-2012
001 D 7000 May-2012
I Have to write a single query to calculate the sum of credit and sum of debit value separately.
View 8 Replies
View Related
Aug 14, 2011
I am new to PL/SQL and how to create a trigger to compute the population of the school from the groups of students and store back in population. It also needs to check that there is a min of 10 students to a school.
CREATE OR REPLACE TYPE group_type AS OBJECT
(
group_nameVARCHAR2(20),
tutor_idNUMBER(5),
[code]...
View 11 Replies
View Related
May 21, 2013
I've a situation where I've very less redo logs generated. Let us say 10MB. Which solution will be better ?
1. Create one redo log group about 12 MB in size.
2. Create two redo log groups about 5 MB each in size as recommended by Oracle.
Even though solution 1 is also appropriate for me because I've less redo generated than the redo log group size. My whole redo will fit in this and I can raise checkpoint forcefully after certain period of time let us say every 3 seconds.
In one of our DB I found scenario one is implemented. So I want to know pros and cons of both of these practices.
View 7 Replies
View Related
Jun 21, 2012
We have a big problem in the underlying devices that ASM disk groups depends on. We have SAN disks (EMC DMX) presented to us as /dev/sda, /dev/sdb, etc.These disks have actually multipath setup. For example, - /dev/emcpowera has two different paths as:
1./dev/sda
2./dev/sdp
We were using the direct path /dev/sda to format the disk (as fdisk /dev/sda), and then used oracleasm to create disks (oracleasm createdisk ASMDISKDATA0 /dev/sda1). Then, ASM disk groups were created with those lables (volumes), and then database was created using the ASM disk groups.
Now our platform folks are telling us that we should use the multipath /dev/emcpowera instead of direct path /dev/sda as the direct path is not guaranteed across reboot.
So the questions are:
1.Is there a way to re-link the disk group to asm disk to the multipath devices (/dev/emcpowera1 instead of /dev/sda1)?
2.Is this even an issue for ASM? If the /dev/sda fails after reboot, can Oracle ASM automatically discover the other path /dev/sdp to the physical EMC disk?
View 3 Replies
View Related
Jul 22, 2013
Is it possible to use TimesTen to create cache groups that have different table structures from original Oracle tables based on relational conditions?
View 3 Replies
View Related
Feb 20, 2012
i have a view in a schema of my database 'vw_get_noti_calendar_data' and i want to select some field but it shows an error 'invalid number' and i have observed it but no effect,so where is the error.
my code is---------
SELECT *
FROM aepuser.vw_get_noti_calendar_data
WHERE TO_DATE (TO_CHAR (event_start_date, 'mm/dd/yyyy'), 'dd/mm/yyyy')
BETWEEN TO_DATE (TO_CHAR ('2/28/2012', 'mm/dd/yyyy'), 'dd/mm/yyyy')
AND TO_DATE (TO_CHAR ('2/28/2012', 'mm/dd/yyyy'), 'dd/mm/yyyy')
AND srnum BETWEEN 5 * 1 + 1 AND 5 * 1 + 5;
View 11 Replies
View Related
Jul 12, 2012
What is the default key created when a primary key is created in a table ?
View 1 Replies
View Related
Jan 20, 2012
is there possible to get those column names on which we have applied dml operations like insert, update,delete if yes,then how .
View 5 Replies
View Related
Aug 29, 2008
Im having a problem with writing an appropriate query for a report in my web application. I need it to extract data from three related tables:
CAR(
PK CAR_ID INT NOT NULL,
TYPE VARCHAR NOT NULL)
REPAIR_CENTER(
PK REPAIR_CENTER_ID INT NOT NULL,
NAME VARCHAR NOT NULL)
[code]...
I need the report to display only available cars. Available cars must have these characteristics:
1. if the CAR_REPAIR table is empty, displays all entries from CAR table...
2. if car has multiple entries in the CAR_REPAIR table display only the latest DATE_RETURN if its lower than todays date (SYSDATE), otherwise don't display that car...
3. don't display cars that are in the CAR_REPAIR table and have DATE_RETURN value of NULL
View 3 Replies
View Related
May 3, 2013
we want to truncate a oracle Table in the Oracle DB. After the truncate the fact table will be loaded again. After the new load in the fact table we want to tell the times ten db to refresh the cache table. The cache Table is a user owned read-only cache group with no autorefresh. We want to tell times ten in a PL/SQL Block from Oracle DB that starts the refresh from the cache group in times ten. The refresh should not be a autorefresh because the refresh should only start if the fact table will new loaded after the truncate.
View 1 Replies
View Related
May 15, 2009
I have to reorganize one table that related to several other tables. The reorg is too slow when it runs on this table. I would like to create one image of the table and synch it with the original one in real time. So when I run the reorg, I will use the image table that does not constrained by indexes and other objects. Once the reorg is done, I would like to rename the table. how could I do the replication in real time?
View 2 Replies
View Related
Apr 13, 2012
how to delete a data which is related to many no of child table and according to setnull and casecad by using subprogram?
View 6 Replies
View Related
Sep 17, 2012
I studied the documentation and many websites about DBMS_JOB but I am still confused.How do I schedule a procedure to run i.e. every sunday 10:00 AM ?This is what I tried:
DECLARE
X NUMBER;
BEGIN
SYS.DBMS_JOB.SUBMIT
(
job => X
[code]...
Is the Interval related to next_date ?
View 1 Replies
View Related
Oct 31, 2011
How to find out the tablespaces specific to application (i.e. not related to oracle like system, sysaux,temp,undo etc..)
View 1 Replies
View Related
May 9, 2012
OS: AIX 6.1 64bit
DB: Oracle 10gR2 (10.2.0.5.0)
What we did is..
1. Install patch 6613550 (./rootpre.sh)
2. Install 10.2.0.1 for AIX 64-bit
3. Install 10.2.0.5 Patch Set
4. Create Database
5. Created/Setup LISTENER and TNSNAMES (test connection successful)
After installation of database, everything went well until we tried to restart the server, what happened is that we cannot start the listener, the error was:
TNS-12555: TNS:permission denied
TNS-12560: TNS:protocol adapter error
TNS-00525: Insufficient privilege for operation
IBM/AIX RISC System/6000 Error: 1: Not owner
What i did to resolve this problem is to change the owner of /tmp to oracle, after that i was able to start the listener. ($lsnrctl start) The problem is that our sys admin said that the /tmp directory is being used by the OS and it's owner must be root. But if i return it to root, the listener will not start again. (After server restart)
BTW, our /tmp has enough space so we did not perform the following steps from the installation guidelines:
$TEMP=/directory
$TMPDIR=/directory
$export TEMP TMPDIR
I was thinking if that we must make a temp directory for oracle and set it in the profile, or change the owner to root again and perform this:
$chown -R oracle:oinstall /tmp
$chmod -R 777 /tmp
View 2 Replies
View Related
Jan 26, 2012
I wanted to know how to find the links between cursors that have been created in v$sql after compiling a PLSQL procedure.
For example, if i compile the following procedure :
create or replace
PROCEDURE PROCEDURE1
AS
BEGIN
insert into t values(1,1);
END PROCEDURE1;
, the 2 following cursors are created in v$sql :
select SQL_ID, SQL_TEXT from v$sql
where sql_id in ('71pj8t5nz1d80','2s567zb6684sh');
"SQL_ID""SQL_TEXT"
"2s567zb6684sh""BEGIN PROCEDURE1; END;"
"71pj8t5nz1d80""INSERT INTO T VALUES(1,1)"
Thus, i would like to know how to find that the cursor 71pj8t5nz1d80 is called/linked by the cursor 2s567zb6684sh.
This would be useful for interpreting some sql statistics in v$sqlstats in where such cursors appear and related sql statistics are accounted twice.
View 1 Replies
View Related
